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
-~----------~----~----~----~------~----~------~--~---
