March 29, 2006 at 8:57 pm
Let's say I have a table called events that looks like this:
| ID | StartDateTime | EndDateTime |
|---|---|---|
| 1 | 2004-12-30 | 2005-01-01 |
| 2 | 2004-02-28 | 2004-03-01 |
I would like to create a view that would repeat each event's ID for all days that fall between the start and end dates for each event, so for the above data, it would display:
| Day | ID |
|---|---|
| 2004-12-30 | 1 |
| 2004-12-31 | 1 |
| 2005-01-01 | 1 |
| 2004-02-28 | 2 |
| 2004-02-29 | 2 |
| 2004-03-01 | 2 |
One way I can think of doing this would be to start at the startdate and keep using dateadd until i reach the end date, but I'm thinking there must be a more efficient way.
Thanks,
ALi
March 30, 2006 at 12:52 am
The best way to do this is using a table with positive numbers. This way you can avoid using cursors, and even create a view.
This sql script ( test it in tempdb ) can do the job :
-- clean up : DO NOT RUN ON PRODUCTION DB : tables will be dropped !!
if db_name() = 'tempdb'
begin
declare @object_id int
set @object_id = object_id('dbo.PositiveNumbers')
exec sp_MSdrop_object @object_id
set @object_id = object_id('dbo.events')
exec sp_MSdrop_object @object_id
set @object_id = object_id('dbo.vw_events')
exec sp_MSdrop_object @object_id
end
go
-- Table with PositiveNumbers. This table can be used in a lot of scripts
-- where you want to avid using cursors ...
create table dbo.PositiveNumbers ( number int PRIMARY KEY CLUSTERED )
go
-- fill the table with 5000 rows : this is the maximum # days beteen StartDateTime and
-- EndDateTime we support ... Add more rows if needed
set nocount on
declare @count int
set @count = 0
while @count < 5000
begin
insert dbo.PositiveNumbers (number) values (@count )
set @count = @count + 1
end
go
-- The (test) table events
create table dbo.events
(
ID int identity(1,1) primary key clustered,
StartDateTime datetime not null,
EndDateTime datetime not null
)
go
-- insert some test values
insert events (StartDateTime,EndDateTime) values ( '2004-12-30','2005-01-01')
insert events (StartDateTime,EndDateTime) values ( '2004-02-28','2004-03-01')
go
-- the view as asked
create view vw_events
as
select Day = dateadd(dd,P.number,E.StartDateTime),
E.ID
from dbo.PositiveNumbers P, dbo.events E
where P.number between 0 and datediff(dd,E.StartDateTime,E.EndDateTime)
go
-- show results
select * from vw_events
/************* RESULT
| Day | ID |
|---|---|
| 2004-12-30 00:00:00.000 | 1 |
| 2004-12-31 00:00:00.000 | 1 |
| 2005-01-01 00:00:00.000 | 1 |
| 2004-02-28 00:00:00.000 | 2 |
| 2004-02-29 00:00:00.000 | 2 |
| 2004-03-01 00:00:00.000 | 2 |
******************/
March 30, 2006 at 5:05 pm
Brilliant solution,
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply