You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Calculate Count Of Consecutive leaves applied using Query

No replies
ashok_star2004's picture
User offline. Last seen 14 weeks 23 hours ago. Offline
Joined: 02/13/2009
Posts: 45

Hi All,
I Shall be thankful if any one suggest me in this issue.Requirement is to calculate the number of Consecutive leaves applied using query.

In PS_TL_RPTD_TIME,we have the employees applied for leave with TRCs.
We have to calculate the number of consecutive leaves applied as the count between a date range.But, in this table the saturdays and sundays are not listed.This is breaking my query.The sample output i've got is

Emplid Start_date End_date Count
11534 21-APR-10 23-APR-10 3
11534 26-APR-10 30-APR-10 5
11534 03-MAY-10 07-MAY-10 5
11534 10-MAY-10 10-MAY-10 1
11534 12-MAY-10 13-MAY-10 2

Here,24-APR-10,25-APR-10,01-MAY-10,02-MAY-10,08-MAY-10,09-MAY-10 are Saturdays,Sundays and 11-MAY-10 is Tuesdayas per the 2010 Calendar.Now,i want to get the total count including Saturdays,Sundays dates and leave count as

Emplid Start_date End_date Count
11534 21-APR-10 07-MAY-10 13
11534 10-MAY-10 10-MAY-10 1
11534 12-MAY-10 13-MAY-10 2

Can you pls help me out??Im attaching my query.

Thanks in Advance,
Ashok K.

AttachmentSize
Leave_Count.TXT1.05 KB