split year by week with start date and end 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

General SQL Server Forums

New to SQL Server Programming

split year by week with start date and end date

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2009-11-28 : 12:44:23

Hi All.
I would like create function or stored procedure to split year by week and display first date of the week and last date of the week. The week start from Sunday. For instance, result looks like:
2 | 1/4/2009 - 1/10/2009
If it possible not only for current year and for 5 years early and 5 years forward.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-28 : 13:40:02

do you mean this?


SELECT MIN(yourdatefield),MAX(yourdatefield)
FROM YourTable
GROUP BY DATEADD(wk,DATEDIFF(wk,0,yourdatefield),0)

eugz
Posting Yak Master

210 Posts

Posted - 2009-11-28 : 18:19:03

I would like to get Year, # of week, first date of the week, and last date of the week. For 5 years early and 5 years forward from current, where week first day od week is Sunday. The source is CallDate datetime field. The result should looks like:

Year | #wk | Start week | End week
-------------------------------
...
2006 | 1 | 1/1/2006 | 1/7/2006
2006 | 2 | 1/8/2006 | 1/14/2006
...
and so on.

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-28 : 18:39:24

You didn't explain how you want to handle the weeks when Jan 1 of a year is not Sunday. You will need to define that that, and how it will be handled.

Take a look at the function on the link below for generating a calendar table.
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-29 : 12:41:12

quote:
Originally posted by eugz

I would like to get Year, # of week, first date of the week, and last date of the week. For 5 years early and 5 years forward from current, where week first day od week is Sunday. The source is CallDate datetime field. The result should looks like:

Year | #wk | Start week | End week
-------------------------------
...
2006 | 1 | 1/1/2006 | 1/7/2006
2006 | 2 | 1/8/2006 | 1/14/2006
...
and so on.

Thanks.


SELECT YEAR(Date),DATEPART(wk,Date),
MIN(Date) AS StartDate,
MAX(Date) AS EndDate
FROM table
WHERE Date >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
AND Date < DATEADD(yy,DATEDIFF(yy,0,GETDATE())+5,0)+1
GROUP BY YEAR(Date),DATEPART(wk,Date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources