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)
 Split Date Range into Weeks?

Author  Topic 

sql2013
Starting Member

3 Posts

Posted - 2013-04-01 : 09:35:33
I have the following table with specific date ranges and related periods. The actual table has data for all years until 2012.


StartDate EndDate Year Period


2008-01-01 2008-01-26 2008 1
2008-01-27 2008-02-23 2008 2
2008-02-24 2008-03-22 2008 3
2008-03-23 2008-04-19 2008 4
2008-04-20 2008-05-17 2008 5
2008-05-18 2008-06-14 2008 6
2008-06-15 2008-07-12 2008 7
2008-07-13 2008-08-09 2008 8
.
.
.
.
.
2008-11-30 2008-12-31 2008 13
2009-01-01 2009-01-24 2009 1


How do I split this table into a table like the one below using T-SQL?

Year Period StartofWeek EndofWeek


2008 1 2008-01-01 2008-01-05
2008 1 2008-01-06 2008-01-12
2008 1 2008-01-13 2008-01-19
2008 1 2008-01-20 2008-01-26
.
.
.
.
2008 13 2008-11-30 2008-12-06
2008 13 2008-12-07 2008-12-13
2008 13 2008-12-14 2008-12-20
2008 13 2008-12-21 2008-12-27
2008 13 2008-12-28 2008-12-31
2009 1 2009-01-01 2009-01-03
2009 1 2009-01-04 2009-01-10
.
.
.


The week starts on Sundays and ends on Saturdays. But, the end of the week would not carry over for a year.

It would end with the last day of the calendar year. And a new period starts again for the new year, for example 2009 above.

Please let me know if you need moe details.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-04-01 : 09:46:23
See F_TABLE_DATE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

sql2013
Starting Member

3 Posts

Posted - 2013-04-01 : 10:14:06
Thanks.I created the F table but not sure it will help me.

For example, for 2009-01-01, I need a WeekStart date of 2009-01-01 and WeekEnd of 2009-01-03.

I dont want the previous year's WeekStart date of 2008-12-28.
Go to Top of Page

sql2013
Starting Member

3 Posts

Posted - 2013-04-01 : 10:30:44
The last answer in the following post may help me. Looking into it.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136467

Will update soon.

Update: This is the solution I was looking for.
Go to Top of Page
   

- Advertisement -