[Solved] split rows by time - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

[Solved] split rows by time

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-06 : 06:22:30

Hi,
I have a table with startTime and EndTime columns. These are usually an hour apart but are sometimes more. I have another table which displays data for each hour. I am trying to merge the data in Reporting Services.

My question: can I split a row if difference between startTime and EndTime is greater than one hour?
Eg.

ID: 'a1', StartTime: '9:00', EndTime: '12:00'

becomes:
ID: 'a1', EventTime: '9:00'
ID: 'a1', EventTime: '10:00'
ID: 'a1', EventTime: '11:00'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 06:36:26

you can

SELECT t.ID,DATEADD(hh,v.number,t.StartDate)
FROm Table t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(hh,v.number) <= t.EndDate

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-06 : 07:23:10

Hi,

when I ran this it gave me an error about wrong number of arguments for dateadd. I was able to get it giving required result using: "AND DATEADD(hh,-12,v.number)" on a small test sample. But could you explain what is happening in the query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 07:36:57

quote:
Originally posted by darkdusky

Hi,

when I ran this it gave me an error about wrong number of arguments for dateadd. I was able to get it giving required result using: "AND DATEADD(hh,-12,v.number)" on a small test sample. But could you explain what is happening in the query?

why are you subtracting 12 from hour? i thought what you were asking for was hour wise time. see belwo

declare @test table
(
ID int,
StartDate datetime,
EndDate datetime
)
insert into @test
select 1, '11-02-2008 10:30:00.000','11-02-2008 16:30:00' union all
select 2, '15-02-2008 08:30:00.000','15-02-2008 11:30:00' union all
select 3, '21-02-2008 05:00:00.000','21-02-2008 22:00:00' union all
select 4, '25-02-2008 22:30:00.000','26-02-2008 02:30:00' union all
select 5, '02-02-2008 10:00:00.000','11-02-2008 17:00:00'

select t.ID,dateadd(hh,v.number,t.StartDate)
from @test t
cross join master..spt_values v
where v.type='p'
and dateadd(hh,v.number,t.StartDate) <=t.EndDate

output
------------------------------------
ID	time
1	2008-02-11 10:30:00.000
1	2008-02-11 11:30:00.000
1	2008-02-11 12:30:00.000
1	2008-02-11 13:30:00.000
1	2008-02-11 14:30:00.000
1	2008-02-11 15:30:00.000
1	2008-02-11 16:30:00.000
2	2008-02-15 08:30:00.000
2	2008-02-15 09:30:00.000
2	2008-02-15 10:30:00.000
2	2008-02-15 11:30:00.000
3	2008-02-21 05:00:00.000
3	2008-02-21 06:00:00.000
3	2008-02-21 07:00:00.000
3	2008-02-21 08:00:00.000
3	2008-02-21 09:00:00.000
3	2008-02-21 10:00:00.000
3	2008-02-21 11:00:00.000
3	2008-02-21 12:00:00.000
3	2008-02-21 13:00:00.000
3	2008-02-21 14:00:00.000
3	2008-02-21 15:00:00.000
3	2008-02-21 16:00:00.000
3	2008-02-21 17:00:00.000
3	2008-02-21 18:00:00.000
3	2008-02-21 19:00:00.000
3	2008-02-21 20:00:00.000
3	2008-02-21 21:00:00.000
3	2008-02-21 22:00:00.000
4	2008-02-25 22:30:00.000
4	2008-02-25 23:30:00.000
4	2008-02-26 00:30:00.000
4	2008-02-26 01:30:00.000
4	2008-02-26 02:30:00.000
5	2008-02-02 10:00:00.000
5	2008-02-02 11:00:00.000
5	2008-02-02 12:00:00.000
5	2008-02-02 13:00:00.000
5	2008-02-02 14:00:00.000
5	2008-02-02 15:00:00.000
5	2008-02-02 16:00:00.000
5	2008-02-02 17:00:00.000
5	2008-02-02 18:00:00.000
5	2008-02-02 19:00:00.000
5	2008-02-02 20:00:00.000
5	2008-02-02 21:00:00.000
5	2008-02-02 22:00:00.000
5	2008-02-02 23:00:00.000
5	2008-02-03 00:00:00.000
5	2008-02-03 01:00:00.000
5	2008-02-03 02:00:00.000
5	2008-02-03 03:00:00.000
5	2008-02-03 04:00:00.000
5	2008-02-03 05:00:00.000
5	2008-02-03 06:00:00.000
5	2008-02-03 07:00:00.000
5	2008-02-03 08:00:00.000
5	2008-02-03 09:00:00.000
5	2008-02-03 10:00:00.000
5	2008-02-03 11:00:00.000
5	2008-02-03 12:00:00.000
5	2008-02-03 13:00:00.000
5	2008-02-03 14:00:00.000
5	2008-02-03 15:00:00.000
5	2008-02-03 16:00:00.000
5	2008-02-03 17:00:00.000
5	2008-02-03 18:00:00.000
5	2008-02-03 19:00:00.000
5	2008-02-03 20:00:00.000
5	2008-02-03 21:00:00.000
5	2008-02-03 22:00:00.000
5	2008-02-03 23:00:00.000
5	2008-02-04 00:00:00.000
5	2008-02-04 01:00:00.000
5	2008-02-04 02:00:00.000
5	2008-02-04 03:00:00.000
5	2008-02-04 04:00:00.000
5	2008-02-04 05:00:00.000
5	2008-02-04 06:00:00.000
5	2008-02-04 07:00:00.000
5	2008-02-04 08:00:00.000
5	2008-02-04 09:00:00.000
5	2008-02-04 10:00:00.000
5	2008-02-04 11:00:00.000
5	2008-02-04 12:00:00.000
5	2008-02-04 13:00:00.000
5	2008-02-04 14:00:00.000
5	2008-02-04 15:00:00.000
5	2008-02-04 16:00:00.000
5	2008-02-04 17:00:00.000
5	2008-02-04 18:00:00.000
5	2008-02-04 19:00:00.000
5	2008-02-04 20:00:00.000
5	2008-02-04 21:00:00.000
5	2008-02-04 22:00:00.000
5	2008-02-04 23:00:00.000
5	2008-02-05 00:00:00.000
5	2008-02-05 01:00:00.000
5	2008-02-05 02:00:00.000
5	2008-02-05 03:00:00.000
5	2008-02-05 04:00:00.000
5	2008-02-05 05:00:00.000
5	2008-02-05 06:00:00.000
5	2008-02-05 07:00:00.000
5	2008-02-05 08:00:00.000
5	2008-02-05 09:00:00.000
5	2008-02-05 10:00:00.000
5	2008-02-05 11:00:00.000
5	2008-02-05 12:00:00.000
5	2008-02-05 13:00:00.000
5	2008-02-05 14:00:00.000
5	2008-02-05 15:00:00.000
5	2008-02-05 16:00:00.000
5	2008-02-05 17:00:00.000
5	2008-02-05 18:00:00.000
5	2008-02-05 19:00:00.000
5	2008-02-05 20:00:00.000
5	2008-02-05 21:00:00.000
5	2008-02-05 22:00:00.000
5	2008-02-05 23:00:00.000
5	2008-02-06 00:00:00.000
5	2008-02-06 01:00:00.000
5	2008-02-06 02:00:00.000
5	2008-02-06 03:00:00.000
5	2008-02-06 04:00:00.000
5	2008-02-06 05:00:00.000
5	2008-02-06 06:00:00.000
5	2008-02-06 07:00:00.000
5	2008-02-06 08:00:00.000
5	2008-02-06 09:00:00.000
5	2008-02-06 10:00:00.000
5	2008-02-06 11:00:00.000
5	2008-02-06 12:00:00.000
5	2008-02-06 13:00:00.000
5	2008-02-06 14:00:00.000
5	2008-02-06 15:00:00.000
5	2008-02-06 16:00:00.000
5	2008-02-06 17:00:00.000
5	2008-02-06 18:00:00.000
5	2008-02-06 19:00:00.000
5	2008-02-06 20:00:00.000
5	2008-02-06 21:00:00.000
5	2008-02-06 22:00:00.000
5	2008-02-06 23:00:00.000
5	2008-02-07 00:00:00.000
5	2008-02-07 01:00:00.000
5	2008-02-07 02:00:00.000
5	2008-02-07 03:00:00.000
5	2008-02-07 04:00:00.000
5	2008-02-07 05:00:00.000
5	2008-02-07 06:00:00.000
5	2008-02-07 07:00:00.000
5	2008-02-07 08:00:00.000
5	2008-02-07 09:00:00.000
5	2008-02-07 10:00:00.000
5	2008-02-07 11:00:00.000
5	2008-02-07 12:00:00.000
5	2008-02-07 13:00:00.000
5	2008-02-07 14:00:00.000
5	2008-02-07 15:00:00.000
5	2008-02-07 16:00:00.000
5	2008-02-07 17:00:00.000
5	2008-02-07 18:00:00.000
5	2008-02-07 19:00:00.000
5	2008-02-07 20:00:00.000
5	2008-02-07 21:00:00.000
5	2008-02-07 22:00:00.000
5	2008-02-07 23:00:00.000
5	2008-02-08 00:00:00.000
5	2008-02-08 01:00:00.000
5	2008-02-08 02:00:00.000
5	2008-02-08 03:00:00.000
5	2008-02-08 04:00:00.000
5	2008-02-08 05:00:00.000
5	2008-02-08 06:00:00.000
5	2008-02-08 07:00:00.000
5	2008-02-08 08:00:00.000
5	2008-02-08 09:00:00.000
5	2008-02-08 10:00:00.000
5	2008-02-08 11:00:00.000
5	2008-02-08 12:00:00.000
5	2008-02-08 13:00:00.000
5	2008-02-08 14:00:00.000
5	2008-02-08 15:00:00.000
5	2008-02-08 16:00:00.000
5	2008-02-08 17:00:00.000
5	2008-02-08 18:00:00.000
5	2008-02-08 19:00:00.000
5	2008-02-08 20:00:00.000
5	2008-02-08 21:00:00.000
5	2008-02-08 22:00:00.000
5	2008-02-08 23:00:00.000
5	2008-02-09 00:00:00.000
5	2008-02-09 01:00:00.000
5	2008-02-09 02:00:00.000
5	2008-02-09 03:00:00.000
5	2008-02-09 04:00:00.000
5	2008-02-09 05:00:00.000
5	2008-02-09 06:00:00.000
5	2008-02-09 07:00:00.000
5	2008-02-09 08:00:00.000
5	2008-02-09 09:00:00.000
5	2008-02-09 10:00:00.000
5	2008-02-09 11:00:00.000
5	2008-02-09 12:00:00.000
5	2008-02-09 13:00:00.000
5	2008-02-09 14:00:00.000
5	2008-02-09 15:00:00.000
5	2008-02-09 16:00:00.000
5	2008-02-09 17:00:00.000
5	2008-02-09 18:00:00.000
5	2008-02-09 19:00:00.000
5	2008-02-09 20:00:00.000
5	2008-02-09 21:00:00.000
5	2008-02-09 22:00:00.000
5	2008-02-09 23:00:00.000
5	2008-02-10 00:00:00.000
5	2008-02-10 01:00:00.000
5	2008-02-10 02:00:00.000
5	2008-02-10 03:00:00.000
5	2008-02-10 04:00:00.000
5	2008-02-10 05:00:00.000
5	2008-02-10 06:00:00.000
5	2008-02-10 07:00:00.000
5	2008-02-10 08:00:00.000
5	2008-02-10 09:00:00.000
5	2008-02-10 10:00:00.000
5	2008-02-10 11:00:00.000
5	2008-02-10 12:00:00.000
5	2008-02-10 13:00:00.000
5	2008-02-10 14:00:00.000
5	2008-02-10 15:00:00.000
5	2008-02-10 16:00:00.000
5	2008-02-10 17:00:00.000
5	2008-02-10 18:00:00.000
5	2008-02-10 19:00:00.000
5	2008-02-10 20:00:00.000
5	2008-02-10 21:00:00.000
5	2008-02-10 22:00:00.000
5	2008-02-10 23:00:00.000
5	2008-02-11 00:00:00.000
5	2008-02-11 01:00:00.000
5	2008-02-11 02:00:00.000
5	2008-02-11 03:00:00.000
5	2008-02-11 04:00:00.000
5	2008-02-11 05:00:00.000
5	2008-02-11 06:00:00.000
5	2008-02-11 07:00:00.000
5	2008-02-11 08:00:00.000
5	2008-02-11 09:00:00.000
5	2008-02-11 10:00:00.000
5	2008-02-11 11:00:00.000
5	2008-02-11 12:00:00.000
5	2008-02-11 13:00:00.000
5	2008-02-11 14:00:00.000
5	2008-02-11 15:00:00.000
5	2008-02-11 16:00:00.000
5	2008-02-11 17:00:00.000

what it does is to return all dates from start till end by add hour 1 each from count table master..spt_values

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-06 : 07:45:06

Sorry - I didn't really know what I was doing. That has sorted it now. thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 07:46:47

quote:
Originally posted by darkdusky

Sorry - I didn't really know what I was doing. That has sorted it now. thanks for your help.

no problem
you're welcome

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-08 : 12:01:15

I used visakh16's code successfully - but I am trying to improve performance by indexing the view. Unfortunately I can not include schemabinding because it references Master. Is there another solution to splitting by time or a way to index / boost speed of query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:21:46

try like below

;With CTE(ID,Date,EndDate)
AS
(SELECT ID,StartDate,EndDate
 FROM YourTable

 UNION ALL

 SELECT ID,DATEADD(hh,1,StartDate),EndDate
 FROM CTE
 WHERE DATEADD(hh,1,StartDate)<=EndDate
)


SELECT ID,Date FROM CTE

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-09 : 05:26:44

Thanks that worked greater thanks for your help - once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 05:34:07

quote:
Originally posted by darkdusky

Thanks that worked greater thanks for your help - once again.

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources