Get last six monts data


  • Hi guys, Im am trying to get data for the last six months data, i have the following columns in my table, Item Number, Amount and posting date. What I am trying to achieve is to get average sale for all the Items while having item number as the rows and have columns displaying the last six months this should be an automated process such that it goes on as the months continue

    Monday, June 09, 2014 1:07 PM


  • Maybe something like this:

    DECLARE @mth_1 AS date
    DECLARE @mth_2 AS date
    DECLARE @mth_3 AS date
    DECLARE @mth_4 AS date
    DECLARE @mth_5 AS date
    DECLARE @mth_6 AS date
    SET @mth_1 = SELECT DATEADD(month,-1,GETDATE())
    SET @mth_2 = SELECT DATEADD(month,-2,GETDATE())
    SET @mth_3 = SELECT DATEADD(month,-3,GETDATE())
    SET @mth_4 = SELECT DATEADD(month,-4,GETDATE())
    SET @mth_5 = SELECT DATEADD(month,-5,GETDATE())
    SET @mth_6 = SELECT DATEADD(month,-6,GETDATE())
    SELECT ItemNumber,
    SUM(CASE WHEN (Month(PostDate) = Month(@mth_1)) AND (Year(PostDate) = Year(@mth_1)) THEN Amount ELSE 0 END) AS Month_1Amount,
    SUM(CASE WHEN (Month(PostDate) = Month(@mth_2)) AND (Year(PostDate) = Year(@mth_2)) THEN Amount ELSE 0 END) AS Month_2Amount,
    SUM(CASE WHEN (Month(PostDate) = Month(@mth_2)) AND (Year(PostDate) = Year(@mth_3)) THEN Amount ELSE 0 END) AS Month_3Amount,
    SUM(CASE WHEN (Month(PostDate) = Month(@mth_4)) AND (Year(PostDate) = Year(@mth_4)) THEN Amount ELSE 0 END) AS Month_4Amount,
    SUM(CASE WHEN (Month(PostDate) = Month(@mth_5)) AND (Year(PostDate) = Year(@mth_5)) THEN Amount ELSE 0 END) AS Month_5Amount,
    SUM(CASE WHEN (Month(PostDate) = Month(@mth_6)) AND (Year(PostDate) = Year(@mth_6)) THEN Amount ELSE 0 END) AS Month_6Amount,
    FROM TableName
    WHERE ((PostDate) >= DATEFROMPARTS(Year(@mth_6),Month(@mth_6),1))
    AND ((PostDate) < DATEFROMPARTS(Year(GETDATE()),Month(GETDATE()),1))
    GROUP BY ItemNumber, Year(PostDate), Month(PostDate)

    I put in the variables to make it less cluttered. Also should work without WHERE clause, but the WHERE clause may make it faster.

    Wednesday, June 11, 2014 11:57 AM