Best Practice in Scenario

Question

I am currently trying to accomplish the following:

get the Last Weekstamp for the last 6 Months, the following ilustrates how the end result might look like:

Month  | Weekstamp |
2013-12| 2013-52   |
2014-01| 2014-05   |

.... and so on

I have a auxiliary Table, which has all Weeks in it and allows me to connect to a Calender Table, which in turn has all months, meaning i am able to get all weekstamps per Month,

but how do i get all of the Last Week Numbers for the Last 6 Months ? my idea was a Temporary table of some sor (never used one, am a beginner when it Comes to SQL) which calculates all of the Weekstamps needing to be filtered out per month, and than gives out only values which i could than use to filter a query which contains all the data i Need.

Anybody have a better idea?

As i said I am just a beginner so i can't really say what the best way would be

Thanks a lot in Advance!

Answer 1

My guess is that your challenge is determining what the last six months are. To do this you can use a tally table (spt_values) and DateDiff to determine when the last six months are.

You can also depending on which DB and version easily do this without a calander or weeks table.

This

WITH rnge 
     AS (SELECT number 
         FROM   master..spt_values 
         WHERE  type = 'P' 
                AND number > 0 
                AND number < 7), 
     dates 
     AS (SELECT EOMONTH(Dateadd(m, number * -1, Getdate())) dt 
         FROM   rnge) 
SELECT Year(dt)         year, 
       Month(dt)        month, 
       Datepart(wk, dt) week 
FROM   dates 

Produces this output

| YEAR | MONTH | WEEK |
|------|-------|------|
| 2014 |     1 |    5 |
| 2013 |    12 |   53 |
| 2013 |    11 |   48 |
| 2013 |    10 |   44 |
| 2013 |     9 |   40 |
| 2013 |     8 |   35 |

Demo

I'll leave it to you to format the values

This assumes SQL Server 2012 since it uses EOMONTH see Get the last day of the month in SQL for previous versions of SQL Server