GROUPING by WEEK when weeks span multiple months

Question

Post reply

GROUPING by WEEK when weeks span multiple months

Adam Bean

One Orange Chip

Points: 26631
More actions
May 18, 2011 at 10:13 am

#238386

Hey all,
Struggling a little bit here trying to report on a data set I've created. Basically, I want to show totals by month/week and using the MONTH, DATEPART(WEEK) functions, I'm having an issue where data is split between two week numbers as it spans two months. For example, the last week of February ends in March; therefore I'm showing two entries for week #10.
I thought I had a work around by date shifting the month (DATEPART(MONTH,DATEADD(WEEK, DATEDIFF(WEEK,0,[BackupDate]), 0))). This did work; however now for week 1 of the year, they're actually showing up in month 12 of the current year, vs. month 12 of the previous year.
Any advice on the matter would be most helpful.
Thank you
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post sample data and required results... I'm just not getting the issue here!

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

See below.

Note how the week of 02.27 to 03.05 is week 10, but it spans both month 2 and 3 ... which is correct from a calendar perspective; however I want my data to be displayed as week 10, month 2 as I don't want two records for a week that spans multiple months.

Hope that clears it up a bit?

SELECT

[Date]

,DATENAME(WEEKDAY,[Date])AS [DayOfWeek]

,MONTH([Date])AS [Month]

,DATEPART(WEEK,[Date])AS [Week]

,DATEADD(DAY,1-DATEPART(WEEKDAY,[Date]),[Date]) AS [WeekStarting]

,DATEADD(DAY,7-DATEPART(WEEKDAY,[Date]),[Date]) AS [WeekEnding]

FROM

(

SELECT CONVERT(DATETIME,'20110226') UNION ALL

SELECT CONVERT(DATETIME,'20110227') UNION ALL

SELECT CONVERT(DATETIME,'20110228') UNION ALL

SELECT CONVERT(DATETIME,'20110301') UNION ALL

SELECT CONVERT(DATETIME,'20110302') UNION ALL

SELECT CONVERT(DATETIME,'20110303') UNION ALL

SELECT CONVERT(DATETIME,'20110304') UNION ALL

SELECT CONVERT(DATETIME,'20100305')

) a ([Date])

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

You need to just use the week start date for each date in your data.

I would recommend using a "dates" table, containing the year, month, date and week number, then you can simply join to it on date to get the year, month and week values for each date.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

... and to resolve this you'll need to first get month(startweekdate) and join that back to the rest of the result set. I don't see any ways to do it in 1 go.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Thanks mister. A route I had debated; however not really looking to take that avenue at this time. I'm still working on being able to do within my query without the need of the infamous calendar table.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Here... you'll have to get the right holidays for your country(ies) but the rest of it is done.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Adam Bean (5/18/2011)
Thanks mister. A route I had debated; however not really looking to take that avenue at this time. I'm still working on being able to do within my query without the need of the infamous calendar table.

I can't understand why you would avoid this?

You will need to work out the week start date for every date, then get the month and year for that week start date.

You might find that you come back to a "calendar" table - they are very useful!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Ninja's_RGR'us (5/18/2011)
Here... you'll have to get the right holidays for your country(ies) but the rest of it is done.

I'll have one of those, thanks a lot Ninj 😉

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

mister.magoo (5/18/2011)
Ninja's_RGR'us (5/18/2011)
Here... you'll have to get the right holidays for your country(ies) but the rest of it is done.
I'll have one of those, thanks a lot Ninj 😉

HTH, I'm just sad I lost the script that created those! Good thing I won't need it for another couple decades!

Lowell SSC Guru Points: 323518 More actions · Answer 10

Here's a Calendar Table Generator that I put together; it features all US National Holidays, as well some other that were fun to research and code; if the holiday is shared by others, the results are comma delimited,like "Guy Fawkes Day,Election Day" or "Thanksgiving Day,Hannukkah/Chanukah"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!