Home » SQL & PL/SQL » SQL & PL/SQL » Find number of consecutive days from a table
| Find number of consecutive days from a table [message #602786] |
Mon, 09 December 2013 02:16  |
 |
pratik4891
Messages: 73
Registered: February 2011
|
Member |
|
|
I have an exception table which contains the holiday .
I want to find the consecutive holidays excluding weekedns
create table test1
(
excp_date date ,
id number
)
insert into test1 values ('29-mar-2013',1);
insert into test1 values ('01-apr-2013',1);
insert into test1 values ('09-dec-2013',1);
insert into test1 values ('10-dec-2013',1);
insert into test1 values ('25-dec-2013',1);
insert into test1 values ('28-dec-2013',1);
I want the o/p as
excp_date consc_days
29th Mar 2
09 Dec 2
10 Dec 1
25 Dec 1
28 Dec 1
Could you please help me out ?
I have the limitation of not using lead and lag function
[Updated on: Mon, 09 December 2013 02:17] Report message to a moderator |
|
|
|
|
|
| Re: Find number of consecutive days from a table [message #602793 is a reply to message #602786] |
Mon, 09 December 2013 03:12  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You must learn to use type casting functions when working with dates. I ran your test case code, and these are the dates I get:
orclz> insert into test1 values ('29-mar-2013',1);
1 row created.
orclz> insert into test1 values ('01-apr-2013',1);
1 row created.
orclz> insert into test1 values ('09-dec-2013',1);
1 row created.
orclz> insert into test1 values ('10-dec-2013',1);
1 row created.
orclz> insert into test1 values ('25-dec-2013',1);
1 row created.
orclz> insert into test1 values ('28-dec-2013',1);
1 row created.
orclz> select * from test1;
EXCP_DATE ID
------------------- ----------
0029-03-20 13:00:00 1
0001-04-20 13:00:00 1
0009-12-20 13:00:00 1
0010-12-20 13:00:00 1
0025-12-20 13:00:00 1
0028-12-20 13:00:00 1
6 rows selected.
orclz>orclz> select excp_date,case to_char(excp_Date,'d') when '5' then 3 when '6' then 2 else 1 end from test1;
EXCP_DATE CASETO_CHAR(EXCP_DATE,'D')WHEN'5'THEN3WHEN'6'THEN2ELSE1END
------------------- ----------------------------------------------------------
0029-03-20 13:00:00 1
0001-04-20 13:00:00 1
0009-12-20 13:00:00 3
0010-12-20 13:00:00 2
0025-12-20 13:00:00 1
0028-12-20 13:00:00 1
6 rows selected.
orclz>
|
|
|
|
| Re: Find number of consecutive days from a table [message #602796 is a reply to message #602793] |
Mon, 09 December 2013 03:40 |
|
pratik4891
Messages: 73
Registered: February 2011
|
Member |
|
|
John ,
Thanks for your help
I have modified the query
insert into test1 values (to_date('03/29/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('04/01/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/09/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/10/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/25/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/26/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/28/2013','mm/dd/yyyy'),1);
The requirement is not like a decode function .
These table contains the business holidays
Now if Friday and next Monday are holidays then it will return two but if next Monday is not a business holiday it should return 1
It should check for the next Business days until there is a break and it should return the count
Michel ,
Yes I have to do a subquery however I am not sure how to do the iteration
Could you please suggest ?
|
|
|
|
|
|
| Re: Find number of consecutive days from a table [message #602802 is a reply to message #602796] |
Mon, 09 December 2013 04:23 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
pratik4891 wrote on Mon, 09 December 2013 15:10Now if Friday and next Monday are holidays then it will return two but if next Monday is not a business holiday it should return 1
So is this a consideration that weekend holidays are Saturday and Sunday?
Also, using LAG would be easy to implement the logic. Any specific reason not to use it?
[Updated on: Mon, 09 December 2013 04:25] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Find number of consecutive days from a table [message #602817 is a reply to message #602805] |
Mon, 09 December 2013 05:43 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
pratik4891 wrote on Mon, 09 December 2013 16:08Please suggest me the soultion with the lag or lead function .
I have to consider weekends as well as holidays
SQL> SELECT T.*,
2 CASE
3 WHEN LAG(EXCP_DATE, 1, EXCP_DATE)
4 OVER(ORDER BY EXCP_DATE) = EXCP_DATE - 1 THEN
5 0
6 WHEN TRIM(TO_CHAR(EXCP_DATE, 'day')) = 'monday' AND LAG(EXCP_DATE)
7 OVER(ORDER BY EXCP_DATE) = EXCP_DATE - 3 THEN
8 0
9 ELSE
10 1
11 END CHK_CONSECUTIVE_DAYS
12 FROM TEST1 T;
EXCP_DATE ID CHK_CONSECUTIVE_DAYS
----------- ---------- --------------------
3/29/2013 1 1
4/1/2013 1 0
12/9/2013 1 1
12/10/2013 1 0
12/25/2013 1 1
12/26/2013 1 0
12/28/2013 1 1
7 rows selected
|
|
|
|
| Re: Find number of consecutive days from a table [message #602868 is a reply to message #602817] |
Tue, 10 December 2013 00:21 |
|
pratik4891
Messages: 73
Registered: February 2011
|
Member |
|
|
Thanks Lalit for your help. The last row seems to return incorrect result though
Michel ,
The test cases are like mentioned below
1) I have a hoilday table and I have to find the consecutive number of hoildays if there are any
Scenario 1 : No consecutive hoilday say 4th of July , it will return
Date Occurance
04-July-2013 1
Scenarion 2 : Consecutive holidays say 25th Dec 26th Dec and 27th Dec so it will return
Date Occurance
25th Dec 3
26th Dec 2
27th Dec 1
Scenarion 3 : If consecutive holidays exists excluding saturday and sunday say 13th Dec and 16th Dec it should return
Date occurance
13th Dec 2
16th Dec 1
Is it possible in oracle ?
[Updated on: Tue, 10 December 2013 00:22] Report message to a moderator |
|
|
|
|
|
| Re: Find number of consecutive days from a table [message #602876 is a reply to message #602875] |
Tue, 10 December 2013 01:40 |
|
pratik4891
Messages: 73
Registered: February 2011
|
Member |
|
|
Michel ,
I have put the below insert statements
insert into test1 values (to_date('03/29/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('04/01/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/09/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/10/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/25/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/26/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/27/2013','mm/dd/yyyy'),1);
insert into test1 values (to_date('12/28/2013','mm/dd/yyyy'),1);
For 25th December there are 3 consecutive holidays (25,26,27) so the occurance is 3
For 26th December there are 3 consecutive holidays (26,27) so the occurance is 2
For 27th December there is only 1 holiday (27) so the occurance is 1
Please let me know whether I am missing anything
|
|
|
|
|
|
| Re: Find number of consecutive days from a table [message #602880 is a reply to message #602868] |
Tue, 10 December 2013 02:05 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
pratik4891 wrote on Tue, 10 December 2013 11:51Thanks Lalit for your help. The last row seems to return incorrect result though
You haven't understood the query output. The "0" means that the day is a consecutive holiday. "1" means that it is not consecutive with respect to previous record. If you want the output to have number of occurences, then tweak the same code and get the count of such consecutive days.
The logic for the count/occurences would be whenever you get a "0", then use LEAD to get the number of days between the two records.
|
|
|
|
| Re: Find number of consecutive days from a table [message #602882 is a reply to message #602880] |
Tue, 10 December 2013 02:16 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
This is what I amasking you to explain :
SQL> SELECT A.*,
2 CASE
3 WHEN CHK_CONSECUTIVE_DAYS = 0 THEN
4 EXCP_DATE - LAG(EXCP_DATE, 1, EXCP_DATE) OVER(ORDER BY EXCP_DATE)
5 ELSE
6 CHK_CONSECUTIVE_DAYS
7 END OCCURENCES
8 FROM (SELECT T.*,
9 CASE
10 WHEN LAG(EXCP_DATE, 1, EXCP_DATE)
11 OVER(ORDER BY EXCP_DATE) = EXCP_DATE - 1 THEN
12 0
13 WHEN TRIM(TO_CHAR(EXCP_DATE, 'day')) = 'monday' AND
14 LAG(EXCP_DATE)
15 OVER(ORDER BY EXCP_DATE) = EXCP_DATE - 3 THEN
16 0
17 ELSE
18 1
19 END CHK_CONSECUTIVE_DAYS
20 FROM TEST1 T) A;
EXCP_DATE ID CHK_CONSECUTIVE_DAYS OCCURENCES
----------- ---------- -------------------- ----------
3/29/2013 1 1 1
4/1/2013 1 0 3
12/9/2013 1 1 1
12/10/2013 1 0 1
12/25/2013 1 1 1
12/26/2013 1 0 1
12/27/2013 1 0 1
12/28/2013 1 0 1
8 rows selected
So, in above output, for the consecutive days from 25th to 28th Dec, against which records you want to see the total occurence count? Post your expected output.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 16:07:46 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
