# 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

Thursday, February 20, 2014 6:17 PM

• If you've a calendar table, you can just use this

```SELECT CONVERT(varchar(7),DATEADD(mm,DATEDIFF(mm,0,DateColumn),0),120) AS Month,
DATENAME(yyyy,DATEADD(mm,DATEDIFF(mm,0,DateColumn),0)) + CAST(MAX(DATEPART(wk,DateColumn)) AS varchar(2))AS WeekStamp
FROM CalendarTable
GROUP BY DATEDIFF(mm,0,DateColumn)```

• Marked as answer by Thursday, February 20, 2014 9:45 PM
Thursday, February 20, 2014 7:26 PM

### All replies

• Sorry I cannot able to help you without script and I'm not completely clear on what you are saying. Please do post script.

Thanks.

Thursday, February 20, 2014 7:02 PM
• What is the SQL version you use? Here is a code to get month end days and week numbers for past few months
```declare @dt datetime=getdate(), @months int=6;
with
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
CTE AS(select  Row_number()Over(Order by (SELECT NULL)) NUM from L2),
CTE_MONTHENDS AS(
select year(dt),datepart(wk,dt) from CTE_MONTHENDS
```

Thursday, February 20, 2014 7:04 PM
• If you've a calendar table, you can just use this

```SELECT CONVERT(varchar(7),DATEADD(mm,DATEDIFF(mm,0,DateColumn),0),120) AS Month,
DATENAME(yyyy,DATEADD(mm,DATEDIFF(mm,0,DateColumn),0)) + CAST(MAX(DATEPART(wk,DateColumn)) AS varchar(2))AS WeekStamp
FROM CalendarTable
GROUP BY DATEDIFF(mm,0,DateColumn)```