Weekly data converted to months

Question

Post reply

Weekly data converted to months

scottsanpedro

SSCrazy

Points: 2130
More actions
November 22, 2012 at 8:02 am

#285085

Hi all. Hope you can help. I have data that is structured by week format.
Year Week Total
2012 49 10
2012 50 10
2012 51 20
2012 52 10
2013 1 10
I want to be able to total by the month. I would presume that I would need to figure out the dates and find out how many days are in the month divided by 7. So maybe 3/7 on one month and add 4/7 on the other.
Are there any pointers on doing this, clever ways etc.?
If not I will work it out as above.
I am using ISO weeks.
Thanks for any help. Scott

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 1

You'll run into trouble if a week spans 2 months.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 2

Give this ago.

First the function: (You can use DATEPART instead of ufn_ISOWeek)

CREATE FUNCTION [dbo].[ufn_GetDateFromISOWeek]

(

@YearNr SmallInt,

@WeekNr TinyInt,

@SE Char(1),

@StartMonth TinyInt

)

RETURNS SmallDateTime

AS

BEGIN

--DECLARE -- Parameters

--@YearNr SmallInt,

--@WeekNr TinyInt,

--@SE Char(1),

--@StartMonth TinyInt -- Some years have 2x week 52. 01-01-2012 is the last day of week 52 of year 2011. But also 24-12 to 30-12 is week 52.

--SET @YearNr = 2012

--SET @weekNr = 52

--SET @SE = 'E'

--SET @StartMonth = 1

DECLARE -- Returns

@RetDate SmallDateTime

DECLARE -- Variables

@TmpDate SmallDateTime,

@TmpWeekNr TinyInt

IF @WeekNr > 53 OR @WeekNr < 1

BEGIN

RETURN NULL

END

IF UPPER(@SE) NOT IN ('S','E')

BEGIN

RETURN NULL

END

SET @TmpDate = CAST('01-' + CAST(@StartMonth AS Varchar) + '-' + CAST(@YearNr AS Varchar) AS SmallDateTime)

WHILE 1=1 AND YEAR(@TmpDate) <= @YearNr + 1

BEGIN

SET @TmpWeekNr = dbo.ufn_ISOWeek(@TmpDate)

IF @WeekNr = @TmpWeekNr

BEGIN

IF @SE = 'S'

BEGIN

WHILE @WeekNr = dbo.ufn_ISOWeek(DATEADD(DD,-1,@TmpDate))

BEGIN

SET @TmpDate = DATEADD(DD,-1,@TmpDate)

END

-- First date found. Stop

SET @RetDate = @TmpDate

END

ELSE IF @SE = 'E'

BEGIN

WHILE @WeekNr = dbo.ufn_ISOWeek(DATEADD(DD,1,@TmpDate))

BEGIN

SET @TmpDate = DATEADD(DD,1,@TmpDate)

END

-- Last date found.

SET @RetDate = @TmpDate

END

BREAK

END

SET @TmpDate = DATEADD(dd, 1, @TmpDate)

END

RETURN @RetDate

END

Demo code

CREATE TABLE #Tmp (YearNr SmallInt, WeekNr TinyInt, Total SmallInt)

INSERT INTO #Tmp VALUES (2012, 49, 10)

INSERT INTO #Tmp VALUES (2012, 50, 10)

INSERT INTO #Tmp VALUES (2012, 51, 20)

INSERT INTO #Tmp VALUES (2012, 52, 10)

INSERT INTO #Tmp VALUES (2013, 1, 10)

ALTER TABLE #Tmp ADD FirstDate SmallDateTime

ALTER TABLE #Tmp ADD LastDate SmallDateTime

UPDATE #Tmp

SETFirstDate= dbo.ufn_GetDateFromISOWeek(YearNr, WeekNr, 'S', 1),

LastDate= dbo.ufn_GetDateFromISOWeek(YearNr, WeekNr, 'E', 1)

SELECT * FROM #Tmp

ALTER TABLE #Tmp ADD MonthNr1 TinyInt

ALTER TABLE #Tmp ADD MonthNr2 TinyInt

UPDATE #Tmp

SETMonthNr1 = MONTH(FirstDate),

MonthNr2 = MONTH(LastDate)

SELECT * FROM #Tmp

DROP TABLE #Tmp

Hope this helps.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

scottsanpedro SSCrazy Points: 2130 More actions · Answer 3

Thats great. Thanks for your input. Here are the results.

I am using a Monday as the first day. Just going to try and work that out from your code.

Also 30th/31st Dec is missing from the numbers.

YearNrWeekNrTotalFirstDateLastDate

201249102012-12-02 00:00:002012-12-08 00:00:00

201250102012-12-09 00:00:002012-12-15 00:00:00

201251202012-12-16 00:00:002012-12-22 00:00:00

201252102012-12-23 00:00:002012-12-29 00:00:00

20131102013-01-01 00:00:002013-01-05 00:00:00

Thanks again for your help on this. Scott

scottsanpedro SSCrazy Points: 2130 More actions · Answer 4

Fixed. It was due to me not using ISOWK in the datepart function. Excellant.

Let me check all details and I'll respond

scottsanpedro SSCrazy Points: 2130 More actions · Answer 5

No. its lost some days.

201249102012-12-03 00:00:002012-12-09 00:00:001212

201250102012-12-10 00:00:002012-12-16 00:00:001212

201251202012-12-17 00:00:002012-12-23 00:00:001212 --Stops 23

201252102011-12-26 00:00:002012-01-01 00:00:00121 -- Starts 26

20131102012-12-31 00:00:002013-01-06 00:00:00121

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

scottsanpedro (11/22/2012)
Hi all. Hope you can help. I have data that is structured by week format.
Year Week Total
2012 49 10
2012 50 10
2012 51 20
2012 52 10
2013 1 10
I want to be able to total by the month. I would presume that I would need to figure out the dates and find out how many days are in the month divided by 7. So maybe 3/7 on one month and add 4/7 on the other.
Are there any pointers on doing this, clever ways etc.?
If not I will work it out as above.
I am using ISO weeks.
Thanks for any help. Scott

Do you have a calendar table which contains the ISO week numbers for all 7 days of each week?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

scottsanpedro SSCrazy Points: 2130 More actions · Answer 7

Hi Jeff. No I currently do not have that.

Are you suggesting I need to do this to progress this as a solution?

I'll be back on this tomorrow so will start to think it through again.

Thanks for your comments. Scott

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 8

scottsanpedro (11/22/2012)
Hi Jeff. No I currently do not have that.
Are you suggesting I need to do this to progress this as a solution?
I'll be back on this tomorrow so will start to think it through again.
Thanks for your comments. Scott

You can certainly get away without it but I believe it would make things a lot easier especially at the boundaries of months and years.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)