question

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

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/

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

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 ;