Create a table by day and week

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)

Create a table by day and week

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-03-03 : 18:51:13

Hello, I want to create a table which likes


PRD_ID  CD     STRT_DT                        END_DT
1000     7    1/1/2009 4:00:00 AM            1/2/2009 4:00:00 AM
1001     7    1/2/2009 4:00:00 AM            1/3/2009 4:00:00 AM
1002     7    1/3/2009 4:00:00 AM            1/4/2009 4:00:00 AM
.......
1365     7  12/31/2009 4:00:00 AM            1/1/2010 4:00:00 AM
1366     7    1/1/2010 4:00:00 AM            1/2/2009 4:00:00 AM
1367     7    1/2/2010 4:00:00 AM            1/3/2009 4:00:00 AM
....  Keep going, need 10 years data then from the first sunday
.... to the second sunday
20000    8    1/4/2010 4:00:00 AM            1/11/2009 4:00:00 AM
20001    8    1/11/2010 4:00:00 AM            1/18/2009 4:00:00 AM
...keep to 10 years

Thanks for kindly help.

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-03 : 23:54:19

I'm not sure what exactly you are looking for, but see this



WITH Single as
(
SELECT 1000 AS ProjectId, CONVERT(DATETIME, '2009-01-01') AS Date
UNION ALL 
SELECT ProjectId +1,DateAdd(dd,1,date) as Date
FROM Single
WHERE date<'2010-01-03'

)
, Sundays as
(
SELECT 20000 AS ProjectId, CONVERT(DATETIME, '2010-01-04') AS Date
UNION ALL 
SELECT ProjectId +1,DateAdd(dd,7,date) as Date
FROM Sundays
WHERE DateAdd(dd,7,date)<'2020-12-31'

)
SELECT ProjectId,Date as St_Date,DateAdd(dd,1,date) as End_Dt
FROM Single
UNION ALL
SELECT ProjectId,Date as St_Date,DateAdd(dd,7,date)as End_Dt
FROM sundays

OPTION (MAXRECURSION 0)

Also see how to create a Calendar Table - [url]http://mangalpardeshi.blogspot.com/2008/12/how-to-create-time-dimension-in-sql.html[/url]

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources