none
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!

    Thursday, February 20, 2014 6:17 PM

Answers

  • 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
    WHERE datecolumn >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)
    AND datecolumn < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
    GROUP BY DATEDIFF(mm,0,DateColumn)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by gbobj 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 num,Dateadd(d,-1,Dateadd(mm,Datediff(m,0,Dateadd(month,1,dateadd(month,-num,@dt))),0)) DT from cte)
    select year(dt),datepart(wk,dt) from CTE_MONTHENDS
    


    Satheesh
    My Blog | How to ask questions in technical forum


    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
    WHERE datecolumn >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)
    AND datecolumn < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
    GROUP BY DATEDIFF(mm,0,DateColumn)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

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