13 Periods of 4 weeks - SQL Server Forums

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

13 Periods of 4 weeks

Author

Topic

NuPath
Starting Member

4 Posts

Posted - 2010-02-16 : 16:15:42

I have a table that has a date column. I can group the records by year, month and week using the date column. However, I want to be able to group the records into a 13 periods with each period containing 4 weeks. The first period would begin on the first Monday in January, the second period would continue 4 weeks after that and so on. Any help on this type of query would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 00:29:41

[code]SELECT * INTO #CalendarTable
FROM dbo.CalendarTable('20090101','20091231',0)c
DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate=MIN(CASE WHEN [Day]='Monday' THEN [Date] ELSE NULL END),
@EndDate=MAX([Date])
FROM #CalendarTable

;With Period_CTE(PeriodNo,Start,[End])
AS
(
SELECT 1,@StartDate,DATEADD(wk,4,@StartDate) -1
UNION ALL
SELECT PeriodNo+1,DATEADD(wk,4,Start),DATEADD(wk,4,[End])
FROM Period_CTE
WHERE DATEADD(wk,4,[End])< =@EndDate
OR PeriodNo+1 <=13
)

SELECT PeriodNo,Start,[End]
FROM Period_CTE
OPTION (MAXRECURSION 0)

output
-----------------------------------------------------
PeriodNo Start End
1 2009-01-05 00:00:00.000 2009-02-01 00:00:00.000
2 2009-02-02 00:00:00.000 2009-03-01 00:00:00.000
3 2009-03-02 00:00:00.000 2009-03-29 00:00:00.000
4 2009-03-30 00:00:00.000 2009-04-26 00:00:00.000
5 2009-04-27 00:00:00.000 2009-05-24 00:00:00.000
6 2009-05-25 00:00:00.000 2009-06-21 00:00:00.000
7 2009-06-22 00:00:00.000 2009-07-19 00:00:00.000
8 2009-07-20 00:00:00.000 2009-08-16 00:00:00.000
9 2009-08-17 00:00:00.000 2009-09-13 00:00:00.000
10 2009-09-14 00:00:00.000 2009-10-11 00:00:00.000
11 2009-10-12 00:00:00.000 2009-11-08 00:00:00.000
12 2009-11-09 00:00:00.000 2009-12-06 00:00:00.000
13 2009-12-07 00:00:00.000 2010-01-03 00:00:00.000

dbo.CalendarTable function can be found here

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NuPath
Starting Member

4 Posts

Posted - 2010-02-17 : 01:01:44

Wow, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 01:03:52

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NuPath
Starting Member

4 Posts

Posted - 2010-02-17 : 02:13:22

Hello again visakh16,

Thanks again for your quick reply and your code works great. Assuming I have the periods, start date and end date that your code produces could you also help me with a query to find what periodno a certain date falls in?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 02:17:17

quote:
Originally posted by NuPath

Hello again visakh16,

Thanks again for your quick reply and your code works great. Assuming I have the periods, start date and end date that your code produces could you also help me with a query to find what periodno a certain date falls in?

Sure. thats even simpler.I'll show relevant part alone


....


;With Period_CTE(PeriodNo,Start,[End])
AS
(
SELECT 1,@StartDate,DATEADD(wk,4,@StartDate) -1
UNION ALL
SELECT PeriodNo+1,DATEADD(wk,4,Start),DATEADD(wk,4,[End])
FROM Period_CTE
WHERE DATEADD(wk,4,[End])< =@EndDate
OR PeriodNo+1 <=13
)

SELECT c.PeriodNo,c.Start,c.[End],
COUNT(*) AS RecordCountinPeriod,
.... other aggregated figures
FROM Period_CTE c
LEFT JOIN Yourtable t
ON t.datefield BETWEEn c.Start AND c.[End]
GROUP BY c.PeriodNo,c.Start,c.[End]
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources