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 Oracle-PLSQL@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
