Identify Back to Back Time sheets

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Identify Back to Back Time sheets

Author

Topic

dnagahawatte
Starting Member

24 Posts

Posted - 2011-10-25 : 18:41:01

Hi;
In a table called "timesheets" we store timesheets of engineers.
Start_Time and End_Time
In the ideal world they could look like below.
Start ----- End
08.00 ----- 10.00
10.00 ----- 12.00
12.00 ----- 14.00
16.00 ----- 18.00
18.00 ----- 20.00
Now what i want is to identify Start_Time and End_Time of those blocks.
in the above ex: Answer would be
Start - 08.00 and End 14.00
AND
Start - 16.00 and End 20.00
(Reason - there is a gap in between 14.00 and 16.00)

Basically query should identify the gap and divide them in to blocks and give start and End time for those Blocks.(There could be many blocks)

How can one achieve this?
Appreciate your help.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-26 : 02:28:54

[code]
with
cte as
(
select rn = row_number() over (order by [Start]),
[Start], [End]
from timesheets
),
rcte as
(
select rn, [Start], [End], grp = 1
from cte
where rn = 1

union all

select c.rn, c.[Start], c.[End],
grp = case when r.[End] = c.[Start] then r.grp else r.grp + 1 end
from rcte r
inner join cte c on r.rn = c.rn - 1
)
select min([Start]), max([End])
from rcte
group by grp
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dnagahawatte
Starting Member

24 Posts

Posted - 2011-10-26 : 08:44:48

I will give this a go. Tx Mate!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources