| Find number of consecutive days from a table [message #614579] |
Fri, 23 May 2014 08:24  |
 |
Amyask
Messages: 1
Registered: May 2014
|
Junior Member |
|
|
I hope someone can help me with this. I put the simple example below:
I need to find out the consecutive holidays in the database Plus consecutive weekend.
for holiday data in the database for example:
EXCP_DATE ID
----------- ---------- -------------------- ----------
3/29/2013 1
4/1/2013 1
12/9/2013 1
12/10/2013 1
12/25/2013 1
12/26/2013 1
12/27/2013 1
12/28/2013 1
I need the number of holidays and weekend under "occurences" column. consecutive holidays/weekend
my CONSECUTIVE_DAY: 1 means the date has consecutive holiday/weekend
ID EXCP_DATE WeekDay CONSECUTIVE_DAY OCCURENCES
1 3/29/2013 FRIDAY 1 3
2 4/1/2013 MONDAY 0 1
3 12/9/2013 MONDAY 1 2
4 12/10/2013 TUESDAY 0 1
5 12/25/2013 WEDNESDAY 1 5
6 12/26/2013 THURSDAY 1 4
7 12/27/2013 FRIDAY 1 3
8 12/28/2013 SATURDAY 1 2
Below is the query which works for most cases. It only fails when there is more than 2 consecutive holidays as it only lead one step.
select A.*,
CASE
WHEN CONSECUTIVE_DAY = 1 THEN
case when upper(datename(weekday,EXCP_DATE)) ='FRIDAY' and LEAD(EXCP_DATE) OVER(ORDER BY EXCP_DATE) = (EXCP_DATE +3) THEN
DATEdiff(dd,EXCP_DATE, LEAD(EXCP_DATE) OVER(ORDER BY EXCP_DATE)) +1
when upper(datename(weekday,EXCP_DATE)) ='FRIDAY' and LEAD(EXCP_DATE) OVER(ORDER BY EXCP_DATE) <> (EXCP_DATE +3) THEN
3
else
DATEdiff(dd,EXCP_DATE, LEAD(EXCP_DATE) OVER(ORDER BY EXCP_DATE))+1
end
ELSE
CONSECUTIVE_DAY
END OCCURENCES
FROM(
SELECT T.*,upper(datename(weekday,EXCP_DATE)) WeekDay,
Lead(EXCP_DATE, 1, EXCP_DATE)
OVER(ORDER BY EXCP_DATE) Lead,EXCP_DATE + 1 [EXCP_DATE + 1], EXCP_DATE +3 [EXCP_DATE +3], datename(weekday,EXCP_DATE +3 ) [DateName_EXCP_DATE +3],
---------------------------
CASE
WHEN Lead(EXCP_DATE, 1, EXCP_DATE)
OVER(ORDER BY EXCP_DATE) = EXCP_DATE + 1 THEN
1
WHEN upper(datename(weekday,EXCP_DATE)) ='FRIDAY' AND LEAD(EXCP_DATE) OVER(ORDER BY EXCP_DATE) = EXCP_DATE +3 THEN
1
when upper(datename(weekday,EXCP_DATE)) ='SATURDAY' AND LEAD(EXCP_DATE) OVER(ORDER BY EXCP_DATE) = EXCP_DATE +2 then
1
ELSE
0
END CONSECUTIVE_DAY
FROM TEST1 T
)A
-----------------------------------The number 5 and 6 are not correct. How can I do recursive on Lead?
The result:
ID EXCP_DATE WeekDay Lead EXCP_DATE + 1 EXCP_DATE +3 DateName_EXCP_DATE +3 CONSECUTIVE_DAY OCCURENCES
1 3/29/2013 FRIDAY 4/1/2013 3/30/2013 4/1/2013 Monday 1 4
2 4/1/2013 MONDAY 12/9/2013 4/2/2013 4/4/2013 Thursday 0 0
3 12/9/2013 MONDAY 12/10/2013 12/10/2013 12/12/2013 Thursday 1 2
4 12/10/2013 TUESDAY 12/25/2013 12/11/2013 12/13/2013 Friday 0 0
5 12/25/2013 WEDNESDAY 12/26/2013 12/26/2013 12/28/2013 Saturday 1 2
6 12/26/2013 THURSDAY 12/27/2013 12/27/2013 12/29/2013 Sunday 1 2
8 12/27/2013 FRIDAY 12/28/2013 12/28/2013 12/30/2013 Monday 1 3
7 12/28/2013 SATURDAY 12/28/2013 12/29/2013 12/31/2013 Tuesday 0 0
Thanks for any help.
|
|
|
|
|
|
|
|
| Re: Find number of consecutive days from a table [message #614608 is a reply to message #614603] |
Fri, 23 May 2014 12:46  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Amyask wrote on Fri, 23 May 2014 02:29Hi Lalit,
I am glad to find the query you have done by Google search.
I need to find out the consecutive holidays in the database Plus consecutive weekend.
I am using SQL server 2012, but it does not matter if it is Oracle code.
Would you please tell me how to do it. I need to have it done.
Thanks in advance.
Amy
Yes, OP used PM and sent me his question. OP mentioned in PM that it's SQLServer, and that it doesn't matter to him if the solution given is for Oracle or SQLServer. I requested OP to post the question in the appropriate forum.
[Updated on: Sat, 24 May 2014 01:51] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
