Adding Week Numbers by Month

Question

Post reply

Adding Week Numbers by Month

JettaBar99

SSC Enthusiast

Points: 114

July 1, 2023 at 3:15 pm

Go to Answer

#4224375

I am new and have been away from working for quite a few years so I am so rusty.

I am trying to add week number by month so Feb would have weeks 1 -4, March 1-5 etc.

I want to scroll through each month and apply a number, once that month changes I want to start again until the year is done. The monthcheck is pulling the correct number but I am stuck on the insert part

SET @MonthCheck =
(SELECT min(CA4)
FROM  dbo.Cal_T0_Weeks
WHERE YearKey=@Year)
 
DROP TABLE IF EXISTS #WeekList
CREATE TABLE #WeekList (YearKey INT, WeekKey Int, CA4 Int, WeekNumber INT )

DECLARE csr CURSOR FOR  SELECT Yearkey, WeekKey, CA4
FROM  dbo.Cal_T0_Weeks
WHERE YearKey=@Year
 
Open csr
FETCH NEXT FROM csr
 
INSERT INTO #WeekList Values (
WHILE (CA4 = @MonthCheck)
BEGIN
SET @Count=1
    SELECT Yearkey, WeekKey, CA4, @Count as WeekNumber
FROM     dbo.Cal_T0_Weeks
WHERE YearKey=@Year
@Count= @Count=1
END
)


SELECT  YearKey, WeekKey, CA4, WeekNumber
FROM #WeekList

Jonathan AC Roberts

SSCoach

Points: 18356

July 1, 2023 at 5:02 pm

Answer

#4224459

DECLARE @YearKey int, 
        @WeekKey int, @CA4 int, 
        @PrevMonthCheck int= -1, 
        @Count int= 1

SET @MonthCheck = (SELECT MIN(CA4)
                     FROM dbo.Cal_T0_Weeks
                    WHERE YearKey=@Year)
 
DROP TABLE IF EXISTS #WeekList
CREATE TABLE #WeekList (YearKey INT, WeekKey INT, CA4 INT, WeekNumber INT )

DECLARE csr CURSOR 
    FOR SELECT Yearkey, WeekKey, CA4
          FROM dbo.Cal_T0_Weeks
         WHERE YearKey=@Year
         ORDER BY CA4, WeekKey
 
OPEN csr
FETCH NEXT FROM csr INTO @YearKey, @WeekKey, @CA4

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @CA4 = @PrevMonthCheck
        SET @Count = @Count + 1
    ELSE
        SET @Count = 1

    INSERT INTO #WeekList VALUES (@YearKey, @WeekKey, @CA4, @Count)
    
    SET @PrevMonthCheck = @CA4    
    FETCH NEXT FROM csr INTO @YearKey, @WeekKey, @CA4
END

CLOSE csr
DEALLOCATE csr

SELECT YearKey, WeekKey, CA4, WeekNumber
  FROM #WeekList

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

JettaBar99 wrote:

I am trying to add week number by month so Feb would have weeks 1 -4, March 1-5 etc.

To be sure, the reason why I'm asking questions is because I don't have your calendar object (Cal_T0_Weeks) and so your example code doesn't actually do anything to help much.

To be sure, what does the "WeekKey" column contain for values? Does it contain the ever increasing week numbers by week of the year?

And what is defined as the first week of the month? Can it be a partial week (I believe that the accountants will have your liver for lunch if this is true) that starts on the first day of the month or must it be a whole week that starts on the first week of the month that contains the 4th of the month?

And, sure... you can use a Cursor if you want but I don't believe you need to slow things down like that.

--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)

JettaBar99 SSC Enthusiast Points: 114 More actions · Answer 2

JettaBar99

SSC Enthusiast

Points: 114

July 1, 2023 at 5:21 pm

#4224462

Thank you so much Jonathan!!! Worked like a charm.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

With the aim to help get rid of that Cursor/While Loop and remove some "rust" in the process 😀 , what does the CA4 column contain.?

--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)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Is it something this you want to achieve?

WITH cte_mockup_calendar(the_date)
AS (
        SELECT  DATEADD(DAY, value, '20230101')
        FROM    GENERATE_SERIES(0, 364)
)
SELECT  the_date,
        (DAY(the_date) + 6) / 7 AS the_week_number
FROM    cte_mockup_calendar;

N 56°04'39.16"
E 12°55'05.25"

JettaBar99 SSC Enthusiast Points: 114 More actions · Answer 5

JettaBar99

SSC Enthusiast

Points: 114

July 3, 2023 at 1:48 pm

#4225829

C4 is the month.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

>> I am trying to add week number by month so Feb would have weeks 1 -4, March 1-5 etc. <

Build a calendar table.

First, look up the ISO 8601 standards for dates. There is a standard day – within – year format in the standard. It's very popular in the Scandinavian countries. You can also download calendars include both the regular ISO 8601 standard, and this one..

This format looks like yyyyWW[01-53]-[1-7] where the double W is a marker for the week. Within the year number, which is between one and (52 or 53). This is then followed by the day the week, numbered from 1 (monday) to 7 (sunday).

The obvious key for your calendar table is the date and we now have a DATE data type to use for it. There is also an ordinal day within year, yyyy-[001-(365 or 366)] that's handy.

Please post DDL and follow ANSI/ISO standards when asking for help.