question

shivan avatar image
shivan asked

How to find consecutive records in sql server

i need some help in writing a sql server, this is what i am trying to achieve. this is the sample of the data Employee WDate Hours -------- ----------------------- ----------- 1004 2014-01-01 00:00:00.000 8 1004 2014-01-02 00:00:00.000 8 1004 2014-01-08 00:00:00.000 8 1004 2014-01-09 00:00:00.000 8 1004 2014-01-11 00:00:00.000 8 1004 2014-01-12 00:00:00.000 3 1004 2014-01-15 00:00:00.000 3 the result i am expecting are: Employee WDate Hours -------- ----------------------- ----------- 1004 2014-01-01 00:00:00.000 8 1004 2014-01-02 00:00:00.000 8 1004 2014-01-08 00:00:00.000 8 1004 2014-01-09 00:00:00.000 8 Thanks in advance
sql-server-2008t-sql
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why does the data from 11th and 12th Jan not come out in the results?
0 Likes 0 ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer. And, you can mark your own answer if that really solved everything.
0 Likes 0 ·
Venkataraman avatar image
Venkataraman answered
In SQL server, it is Set based operation and order is not guaranteed. You should put ORDER BY Clause on WDate to get data ordered by date. SELECT Employee, WDate, Hours FROM ORDER BY WDate
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Not withstanding the question about the consecutive dates of 11/12 Jan, this is a version of the 'islands' problem. declare @YourTable table ( Employee int, WDate datetime, [Hours] int ) insert into @YourTable select 1004,' 1 jan 2014',8 insert into @YourTable select 1004,' 2 jan 2014',8 insert into @YourTable select 1004,' 8 jan 2014',8 insert into @YourTable select 1004,' 9 jan 2014',8 insert into @YourTable select 1004,' 11 jan 2014',8 insert into @YourTable select 1004,' 12 jan 2014',3 insert into @YourTable select 1004,' 15 jan 2014',3 ;with cte as ( select Employee, WDate, [Hours], WDate-row_number() over(order by WDate) as r2 from @yourtable ) ,cte2 as ( select Employee, WDate, [Hours], count(*)over(partition by r2) as [count] from cte ) select Employee, WDate, [Hours] from cte2 where [count]>1 You haven't stated what version of SQL Server this is, and if it is SQL2012, then there are further solutions using LAG/LEAD For more details about gaps and islands look at these: http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hi, Thanks for your response! i am using sql server 2008 i used your code as it is but getting a syntax error on this line. i looked at the parenthesis but couldnt find any issue. count(*)over(partition by r2 order by (select null)) as [count] from cte Msg 156, Level 15, State 1, Line 25 Incorrect syntax near the keyword 'order'. Msg 102, Level 15, State 1, Line 25 Incorrect syntax near ')'.
0 Likes 0 ·
I've amended the code to work for SQL2008
0 Likes 0 ·
shivan avatar image
shivan answered
i want to clarify what i am trying to accomplish, which is little bit more than what i have asked initially. Sorry about the confusion Hi, i am trying to create a logic to find employees who has taken PTO for consecutive days (max upto 7 days) and total hours should be 40. Example: Employee WDate Hours -------- ----------------------- ----------- 1001 2014-01-01 00:00:00.000 4 1001 2014-01-02 00:00:00.000 6 1001 2014-01-03 00:00:00.000 6 1001 2014-01-04 00:00:00.000 6 1001 2014-01-05 00:00:00.000 7 1001 2014-01-06 00:00:00.000 5 1001 2014-01-07 00:00:00.000 6 1001 2014-05-12 00:00:00.000 3 1004 2014-01-01 00:00:00.000 8 1004 2014-01-02 00:00:00.000 8 1004 2014-01-08 00:00:00.000 8 1004 2014-01-09 00:00:00.000 8 1004 2014-01-11 00:00:00.000 8 1004 2014-01-12 00:00:00.000 3 1004 2014-01-15 00:00:00.000 3 in the above scenario: employee # 1001 has taken consecutive PTO for 7 days and total is 40. I want to get this information employee # 1004, has taken consecutive pto four different times: (Jan 1 and Jan 2 ) -- i want to check if the total hours is 40 or not, in this case it is only 16, so ignore (Jan 8, Jan 9) -- i want to check if the total hours is 40 or not, in this case it is only 16, so ignore (Jan 11, Jan 12) -- i want to check if the total hours is 40 or not, in this case it is only 11, so ignore Jan 15 -- i want to check if the total hours is 40 or not, in this case it is only 16, so ignore hope this helps
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shivan avatar image
shivan answered
i found the answer from another post, so thought of sharing: declare @tab table(Employee int,WDate datetime,[Hours] int); insert into @tab values (1001 ,'2014-01-01 00:00:00.000', 4), (1001 ,'2014-01-02 00:00:00.000', 6), (1001 ,'2014-01-03 00:00:00.000', 6), (1001 ,'2014-01-04 00:00:00.000', 6), (1001 ,'2014-01-05 00:00:00.000', 7), (1001 ,'2014-01-06 00:00:00.000', 5), (1001 ,'2014-01-07 00:00:00.000', 6), (1001 ,'2014-05-12 00:00:00.000', 3), (1004 ,'2014-01-01 00:00:00.000', 8), (1004 ,'2014-01-02 00:00:00.000', 8), (1004 ,'2014-01-08 00:00:00.000', 8), (1004 ,'2014-01-09 00:00:00.000', 8), (1004 ,'2014-01-11 00:00:00.000', 8), (1004 ,'2014-01-12 00:00:00.000', 3), (1004 ,'2014-01-15 00:00:00.000', 3); with cte_1 as (SELECT Employee,MIN(WDate) AS Island_start , MAX(WDate) AS Island_end FROM (SELECT Employee,WDate, [Hours],WDate - ROW_NUMBER() OVER(partition by employee ORDER BY WDate) AS grp FROM @tab t) AS D GROUP BY grp,Employee), CTE_2 as ( select *, datediff(d,c.Island_start,c.Island_end) Diff from cte_1 c) select * , (select sum(hours) from @tab t where t.Employee=C.Employee and wdate between c.Island_start and case when c.Diff>5 then dateadd(d,4,c.Island_start) else c.Island_end end )from CTE_2 C ;
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That calculates the last column as '29' for the employee 1001 between 1/1/14 and 7/1/14 - but it should be 40 - why are you limiting the date range to 4 days when the difference is more then 5 days?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.