Selecting Data For Current Month Week to Week

Question

Selecting Data For Current Month Week to Week

SonicG

Old Hand

Points: 373
More actions
June 15, 2012 at 6:03 pm

#258176

I'm not sure how to explain what I'm attempting to do, so here it goes.
I would like to select the current month's data on a week to week basis, starting at the first of the month and ending on the last day of the month. I'm trying to accumulate weekly counts for the month but I don't want to extend any counts into the next month's dates. The first week and/or last week could contain less than 7 days
Using the Month of June 2012 as an example.
June 1st is on a Friday; 1st week = Friday 1st to Sunday 3rd [short week counts], 2nd week starts on Monday the 4th, 3rd on the 11th; 4th - 18th; 5th - 25th to the last day - 30th falls on Saturday [short week counts].
What I'm attempting to do is to use the current month; Select the first week (Jun 1st [Fri] to the 1st Sun Jun 3rd) and calling that week 1; week 2 = 4th to the 10th; week 3 = 11th to 17th; week 4; 18th to 24th; week 5 = 25th to the end of the month - 30th which falls on Sat.
I then want to start over for July for week 1 = 1st [Sun] (only one day for that week) then wk 2 = 2nd to 8th to the 8th,and so on until the last week is basically 2 days - Jul 30th and 31st.
I've created other queries to get the previous month or week's data based on what ever day of the week I run the query, but nothing that automatically uses the current month and selects week to week, ending on the last day of the month.
Way off base? Thanks in advance for any responses.

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Although I don't understand the benefit of counting by short week, a Calendar table would make short work of this problem.

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

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

Not sure what the output is going to be at the end of your project ;

But you could use the MONTH/YEAR to determine the top level and then using the RoundUp( day number divide by 7 ) to give a weekly bucket.

This way you will not need to worry about the day number itself.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

DiverKas SSCrazy Points: 2049 More actions · Answer 3

I would think making use of the DATEPART function with week to get the range of dates for each week would be trivial. You would need to know or get the week number to start for each month but again, that is pretty easy.

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 4

This could work:

--===Declare variables

DECLARE @StartDate DATE, @EndDate DATE;

--===Set our variables based on the current date

select @StartDate = dateadd(mm, datediff(mm, 0, GETDATE()), 0);

select @EndDate = dateadd(mm, datediff(mm, 0, GETDATE()) + 1, -1);

--=== Use this to confirm that variables are set correctly

--SELECT @StartDate, @EndDate

--=== CTE for the calendar month. Extend as needed. A static calendar table would be better

WITH iCal AS (

--=== Limits to the longest month

SELECT TOP 31

--=== The date in our calendar

DATEADD(DD, N - 1,@StartDate) AS xDate,

-- === Gets the week of the month based on the week in the year

(DATEPART(WW,DATEADD(DD, N - 1,@StartDate)) - DATEPART(WW,DATEADD(DD,0,@StartDate))) + 1 AS xWeek

FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N

FROM master.sys.all_columns )T)

--=== This will show the whole table

--SELECT xDate, xWeek FROM iCal where xDate BETWEEN @StartDate AND @EndDate

--=== This gets just the row that matches today

SELECT xDate, xWeek FROM iCal where xDate = CAST(GETDATE() AS DATE);

Converting oxygen into carbon dioxide, since 1955.

SonicG Old Hand Points: 373 More actions · Answer 5

Thanks for your replies. They have given me some ideas to start working with.