Duplicating Records For Each Date

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

Transact-SQL (2000)

Duplicating Records For Each Date

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2008-02-14 : 05:55:10

Heya,

I have an event table in my database which stores events. Each event has a start datetime and an end datetime.

I am producing a print of the events by day, I was wondering if there is anyway to query this data so it brings back a record for an event for each day its on?

So changing this....

EVENT NAME     START DATE     END DATE
----------------------------------------
Holiday        2007/04/01     2007/04/05
Meeting        2007/05/23     2007/05/23

Into.....

DATE          EVENT NAME     START DATE     END DATE
--------------------------------------------------------
2007/04/01    Holiday        2007/04/01     2007/04/05
2007/04/02    Holiday        2007/04/01     2007/04/05
2007/04/03    Holiday        2007/04/01     2007/04/05
2007/04/04    Holiday        2007/04/01     2007/04/05
2007/04/05    Holiday        2007/04/01     2007/04/05
2007/05/23    Meeting        2007/05/23     2007/05/23

A date range will be passed into the query as well, so if an event starts before this date range but ends after it only the appropiate records should be returned.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 06:08:02

Try this

Select dateadd(day,number,START_DATE),EVENT_NAME,START_DATE,END_DATE from your_table t inner join master..spt_values m on m.type='p' where number<=DATEDIFF(day,START_DATE,END_DATE)+1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 06:09:18

[code]DECLARE @Sample TABLE (EventName VARCHAR(20), StartDate DATETIME, EndDate DATETIME)

INSERT @Sample
SELECT 'Holiday', '2007/04/01', '2007/04/05' UNION ALL
SELECT 'Meeting', '2007/05/23', '2007/05/23'

SELECT DATEADD(DAY, v.Number, s.StartDate) AS Date,
s.EventName,
s.StartDate,
s.EndDate
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE v.Number <= DATEDIFF(DAY, s.StartDate, s.EndDate)
ORDER BY s.StartDate,
v.Number[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 06:16:26

Well. We dont need DATEDIFF(day,START_DATE,END_DATE)+1 as number starts with 0
Thanks