SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Split Date Range into Weeks?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql2013
Starting Member

3 Posts

Posted - 04/01/2013 :  09:35:33  Show Profile  Reply with Quote
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

Sweden
30218 Posts

Posted - 04/01/2013 :  09:46:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 04/01/2013 :  10:14:06  Show Profile  Reply with Quote
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.

Edited by - sql2013 on 04/01/2013 10:16:39
Go to Top of Page

sql2013
Starting Member

3 Posts

Posted - 04/01/2013 :  10:30:44  Show Profile  Reply with Quote
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.

Edited by - sql2013 on 04/01/2013 14:22:19
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000