CREATE TABLE employee_attend (leave_code number(5),
EMPL_CODE integer,
leave_type integer,
start_date DATE,
end_date DATE);
INSERT INTO employee_attend VALUES (1,2050,2,TO_DATE('28/10/08',
'DD/MM/YY'), TO_DATE('04/11/08', 'DD/MM/YY'));
INSERT INTO employee_attend VALUES (2,2050,3,TO_DATE('09/11/08',
'DD/MM/YY'), TO_DATE('14/11/08', 'DD/MM/YY'));
INSERT INTO employee_attend VALUES (3,2050,2,TO_DATE('27/11/08',
'DD/MM/YY'), TO_DATE('03/12/08', 'DD/MM/YY'));
INSERT INTO employee_attend VALUES (4,2066,2,TO_DATE('28/10/08',
'DD/MM/YY'), TO_DATE('04/11/08', 'DD/MM/YY'));
INSERT INTO employee_attend VALUES (6,2066,2,TO_DATE('27/11/08',
'DD/MM/YY'), TO_DATE('03/12/08', 'DD/MM/YY'));
commit;
SELECT em, MIN (dt), MAX (dt), st
FROM (SELECT em, dt, st, MAX (rn) OVER (ORDER BY em,dt) max_rn
FROM (SELECT dt, st,em,
CASE
WHEN LAG (st, 1) OVER (ORDER BY em,dt) = st
THEN NULL
ELSE ROW_NUMBER () OVER (ORDER BY em,dt)
END rn
FROM (SELECT emp_date.empl_code em, day_date dt,
NVL2 (start_date, leave_type, 1) st
FROM (SELECT TO_DATE ('20081101',
'yyyymmdd')
+ LEVEL
- 1 day_date
FROM DUAL
CONNECT BY LEVEL <=
TO_NUMBER
(TO_CHAR
(LAST_DAY (TO_DATE
('20081101',
'yyyymmdd')),
'DD'))) gen
CROSS JOIN
(SELECT DISTINCT empl_code
FROM employee_attend)
emp_date
LEFT OUTER JOIN employee_attend ea
ON ( day_date BETWEEN start_date AND
end_date
AND emp_date.empl_code = ea.empl_code
)
ORDER BY 1,
2)))
GROUP BY em,st,
max_rn
ORDER BY 1,2;
On Sun, Nov 9, 2008 at 8:00 PM, Binu K S <[EMAIL PROTECTED]> wrote:
> Hai Michale ,
>
> Thanks
>
> Will try to do this .
>
> Regards
> binu
>
>
> On 11/10/08, Michael Moore <[EMAIL PROTECTED]> wrote:
>>
>> yes, you can use an analytic function and take the first and last value in
>> the partition.
>> The partition will be by (working_leave) order by mydate
>> I'll show you tomorrow if I have time or you have not worked it out by
>> then. I encourage you to take a try at it.
>>
>> It will look something like
>>
>> select first_value(mydate) partition by working_leave order by mydate
>> ,last_value(mydate) partition by working_leave order by mydate
>> ,working_leave
>> FROM ( your entire previously written select statement goes here) ;
>>
>>
>> Regards,
>> Mike
>>
>> On Sun, Nov 9, 2008 at 8:09 AM, Binu K S <[EMAIL PROTECTED]> wrote:
>>
>>> Hai Mike ,
>>> Thanks the help . With the help of u r query and some pl/sql statement
>>> I completed my work .
>>>
>>> Using this query , i am getting the following output .
>>>
>>> 01-NOV-08 Leave
>>> 02-NOV-08 Leave
>>> 03-NOV-08 Leave
>>> 04-NOV-08 Leave
>>> 05-NOV-08 working
>>> 06-NOV-08 working
>>> 07-NOV-08 working
>>> 08-NOV-08 working
>>> 09-NOV-08 Leave
>>> 10-NOV-08 Leave
>>> 11-NOV-08 Leave
>>> 12-NOV-08 Leave
>>> 13-NOV-08 Leave
>>> 14-NOV-08 Leave
>>> 15-NOV-08 working
>>> 16-NOV-08 working
>>> 17-NOV-08 working
>>> 18-NOV-08 working
>>> 19-NOV-08 working
>>> 20-NOV-08 working
>>> 21-NOV-08 working
>>> 22-NOV-08 working
>>> 23-NOV-08 working
>>> 24-NOV-08 working
>>> 25-NOV-08 working
>>> 26-NOV-08 working
>>> 27-NOV-08 Leave
>>> 28-NOV-08 Leave
>>> 29-NOV-08 Leave
>>> 30-NOV-08 Leave
>>>
>>>
>>>
>>> How can I format this into the following way .
>>>
>>> 01-NOV-08 04-NOV-08 Leave
>>> 05-NOV-08 08-NOV-08 working
>>> 09-NOV-08 14-NOV-08 Leave
>>> 15-NOV-08 26-NOV-08 working
>>> 27-NOV-08 30-NOV-08 Leave
>>>
>>> Any of the analytic function can be used ?
>>> Thanks
>>> Binu
>>>
>>>
>>> On 11/5/08, Michael Moore <[EMAIL PROTECTED]> wrote:
>>>>
>>>> This should give you enough to complete the job. If not, let me know and
>>>> we'll take it to the next step. Obviously, you can replace the hard coded
>>>> dates with PL/SQL variables.
>>>> -- Mike --
>>>>
>>>> CREATE TABLE date_ranges (start_date DATE,
>>>> end_date DATE);
>>>>
>>>> INSERT INTO date_ranges VALUES (TO_DATE('28/10/08', 'DD/MM/YY'),
>>>> TO_DATE('04/11/08', 'DD/MM/YY'));
>>>> INSERT INTO date_ranges VALUES (TO_DATE('09/11/08', 'DD/MM/YY'),
>>>> TO_DATE('14/11/08', 'DD/MM/YY'));
>>>> INSERT INTO date_ranges VALUES (TO_DATE('27/11/08', 'DD/MM/YY'),
>>>> TO_DATE('03/12/08', 'DD/MM/YY'));
>>>> commit;
>>>>
>>>> SELECT day_date, nvl2(start_date,'Leave','working') wk_or_lv
>>>> FROM (SELECT TO_DATE ('20081101', 'yyyymmdd') + LEVEL - 1 day_date
>>>> FROM DUAL
>>>> CONNECT BY LEVEL <=
>>>> TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE ('20081101',
>>>> 'yyyymmdd')),
>>>> 'DD'))) gen
>>>> LEFT OUTER JOIN
>>>> date_ranges ON (day_date BETWEEN start_date AND end_date)
>>>> ;
>>>>
>>>> On Tue, Nov 4, 2008 at 9:30 AM, Binu K S <[EMAIL PROTECTED]> wrote:
>>>>
>>>>> Hai ,
>>>>> this is not a report. This for salary calculation. For example an
>>>>> employee is taken leave twice in a month , eg first time it is emergency
>>>>> leave and another time it is sick leave . The employee taken Emergency
>>>>> leave
>>>>> from 10-nov-2008 - 15-nov-2008 and sick leave from 20-nov-2008 to
>>>>> 23-nov-2008 . The remaing days he is present for work . So when the salry
>>>>> for the month of November is preparing there will be five records .
>>>>>
>>>>> 1 from 1-nov-2008 to 9-nov-2008
>>>>> 2 from 10-nov-2008 t0 15-nov-2008 (Leave )
>>>>> 3 from 16-nov-2008 to 19-nov-2008
>>>>> 4 from 20-nov-2008 to 23-nov-2008 (Leave )
>>>>> 5 from 24-nov-2008 to 30-Nov-2008
>>>>>
>>>>> Then the salary will be calculated based on these period . The
>>>>> calculation of allowance during the leave period is based on lots
>>>>> parameter . The information available at present is the leave details . If
>>>>> there is no information available in the leave table , then the employee
>>>>> is
>>>>> present in the work .
>>>>>
>>>>> Regards
>>>>> Binu
>>>>>
>>>>>
>>>>> On 11/4/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Nov 3, 11:16 pm, "Binu K S" <[EMAIL PROTECTED]> wrote:
>>>>>> > Hai,
>>>>>> >
>>>>>> > I am working in an HR project . I have one table which store leave
>>>>>> > information . The main fields are
>>>>>> >
>>>>>> > Leave_code , Employee_code , Leave_type , From_date , To_date
>>>>>> >
>>>>>> > Here Leave Code is the primary key of the table .
>>>>>> > Employee Code is the employee Number .
>>>>>> > Leave Type is the type of leave . Numeric field refering to master
>>>>>> table
>>>>>> > which contain different type of leave . Eg 1 - Annual Leave , 2 -
>>>>>> Sick
>>>>>> > Leave
>>>>>> > 3 Emergency leave .
>>>>>> > From Date and To Date are the leave starting date and leave end date
>>>>>> .
>>>>>> >
>>>>>> > For Example I have the following data in the leave table
>>>>>> >
>>>>>> > Leave_Code Employee_Code Leave_Type From_Date
>>>>>> To_Date
>>>>>> >
>>>>>> -----------------------------------------------------------------------------
>>>>>> > 1 2050 2
>>>>>> > 10-Nov-2008 15-Nov-2008
>>>>>> > 2 2050 3
>>>>>> > 20-Nov-2008 25-Nov-2008
>>>>>> >
>>>>>> > When I am preparing salary for the Month Of November i need to split
>>>>>> the
>>>>>> > date as follows
>>>>>> >
>>>>>> > 01-Nov-2008 to 09 -Nov-2008 Working
>>>>>> > 10-Nov-2008 to 15-Nov-2008 Leave ( Sick Leave )
>>>>>> > 16-Nov-2008 to 19-Nov-2008 Working
>>>>>> > 20-Nov-2008 to 25-Nov-2008 Leave(Emergency Leave)
>>>>>> > 26-Nov-2008 to 30-Nov-2008 Working
>>>>>> >
>>>>>> > While spliting the date we need to consider following cases also .
>>>>>> >
>>>>>> > 1 Leave may start in the previous month and end in the current month
>>>>>> . For
>>>>>> > example the leave can start from 28-OCT-2008 and Ends on 5 - Nov -
>>>>>> >
>>>>>> > 2008 . In that case the out put must be
>>>>>> >
>>>>>> > 1-nov-2008 to 5-Nov-2008 Leave
>>>>>> > 6-Nov-2008 to 30-Nov-2008 Office .
>>>>>> >
>>>>>> > Similarly the leave can start in the current month and ends in the
>>>>>> next
>>>>>> > month .For example the leave can start from 25-nov-2008 and Ends on
>>>>>> 10 -
>>>>>> >
>>>>>> > dec - 2008 . In that case the out put must be
>>>>>> >
>>>>>> > 1-nov-2008 to 24-Nov-2008 Office
>>>>>> > 25-Nov-2008 to 30-Nov-2008 Leave
>>>>>> >
>>>>>> > U can use PL/SQL
>>>>>> >
>>>>>> > Hope I will get Help From u people
>>>>>> >
>>>>>> > Thanks
>>>>>> > Regards
>>>>>> > Binu
>>>>>>
>>>>>> This entire report is generated from this one table of data? What
>>>>>> have you tried to solve this?
>>>>>>
>>>>>>
>>>>>> David Fitzjarrell
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>> >>
>>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
