Rolling Months - Calculations

  • Hi,

    Below is the script includes required output as well. Source data is excel -file format is fixed and excel data will be loaded to SQL.

    In excel file data used to come one month advance, means In the month of may  we used to get June data will be available along with old data

    Logic : Currently ,we are in May month then Jan - Jun Months should be in year 2019 and July to Dec months in 2018 years

    one more example lets say we are in 2020 March- then  Jan-April are in 2020 and May to Dec are in 2019 - Hope it is clear.

    Thanks for help in advance.

     

    CREATE TABLE #TEMP
    (
    MID INT,
    JAN_Subsidy MONEY,
    FEB_Subsidy MONEY,
    MAR_Subsidy MONEY,
    APR_Subsidy MONEY,
    MAY_Subsidy MONEY,
    JUN_Subsidy MONEY,
    JUL_Subsidy MONEY,
    AUG_Subsidy MONEY,
    SEP_Subsidy MONEY,
    OCT_Subsidy MONEY,
    NOV_Subsidy MONEY,
    DEC_Subsidy MONEY
    )

    INSERT INTO #TEMP
    SELECT 101,0,0,10,10,10,10,0,0,0,0,0,0
    UNION ALL
    SELECT 102,0,0,0,0,40,40,0,0,0,0,0,0

    SELECT * FROM #TEMP


    --OUTPUT
    MID Total_Amt Billed_Month(MM/dd/yyyy)
    101 0 01/01/2019
    101 0 02/01/2019
    101 10 03/01/2019
    101 10 04/01/2019
    101 10 05/01/2019
    101 10 06/01/2019
    101 0 07/01/2018
    101 0 08/01/2018
    101 0 09/01/2018
    101 0 10/01/2018
    101 0 11/01/2018
    101 0 12/01/2018
    102 0 01/01/2019
    102 0 02/01/2019
    102 0 03/01/2019
    102 0 04/01/2019
    102 40 05/01/2019
    102 40 06/01/2019
    102 0 07/01/2018
    102 0 08/01/2018
    102 0 09/01/2018
    102 0 10/01/2018
    102 0 11/01/2018
    102 0 12/01/2018
  • This is a just an unpivot. Personally, I prefer using a VALUES clause to unpivot the data, rather than the UNPIVOT operator. This ends up giving you the below:

    SELECT T.MID,
    V.TotalAmt,
    V.BilledMonth
    FROM #TEMP T
    CROSS APPLY (VALUES(T.JAN_Subsidy,CONVERT(date,'20190101')),
    (T.FEB_Subsidy,CONVERT(date,'20190201')),
    (T.MAR_Subsidy,CONVERT(date,'20190301')),
    (T.APR_Subsidy,CONVERT(date,'20190401')),
    (T.MAY_Subsidy,CONVERT(date,'20190501')),
    (T.JUN_Subsidy,CONVERT(date,'20190601')),
    (T.JUL_Subsidy,CONVERT(date,'20190701')),
    (T.AUG_Subsidy,CONVERT(date,'20190801')),
    (T.SEP_Subsidy,CONVERT(date,'20190901')),
    (T.OCT_Subsidy,CONVERT(date,'20191001')),
    (T.NOV_Subsidy,CONVERT(date,'20191101')),
    (T.DEC_Subsidy,CONVERT(date,'20191101'))) V(TotalAmt, BilledMonth)
    ORDER BY T.MID,
    V.BilledMonth;

    Note I have returned the dates back as a date, not in the format MM/dd/yyyy. If you need a specific format do so in your presentation layer not in your SQL. Date(time) data types don't have a format in SQL Server, they are stored as binary data, so the only way to present them with a format would be to convert it to a varchar, which brings it own problems.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom, Thanks for response, Can u have look output once, it is contains both 2018 and 2019 years data- derived based on current month. Thanks!

  • koti.raavi wrote:

    Thom, Thanks for response, Can u have look output once, it is contains both 2018 and 2019 years data- derived based on current month. Thanks!

    In the sample data there is no year demoninator, so how do you know what year it is?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I believe what you're looking for is a 12 month counter, starting with the current month + 1 and moving backwards for 12 months. If I understand this, for any 12 sets of integer values, you have:

    • Today is May 2019, so the data is from July 2018-June 2019
    • Next month is June 2019, so the data will be from Aug 2018-July 2019
    • Last month was April 2019, so the data would have been from June 2018-May 2019

     

    Is this correct?

    If that's the case, you need some ordering mechanism that maps Jan -1,Feb -2, etc., then you assign those values to dates based on the current month.

    DECLARE
    @today DATE = SYSDATETIME()
    , @window INT = 11;

    --SELECT DATEPART(MONTH, @today), @today;

    WITH myTally(n)
    AS
    (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) a(n)
    )
    SELECT 0 - @window + n,
    DATEADD(MONTH, (0 - @window) + n, @today),
    DATENAME(MONTH, DATEADD(MONTH, (0 - @window) + n, @today)) AS 'MonthValue'
    , DATEPART(YEAR, DATEADD(MONTH, 0 - @window + n, @today)) AS 'YearValue'
    FROM myTally
    ORDER BY DATEADD(MONTH, DATEPART(MONTH, @today) - 11 + n, @today);

    In your table, to match this up, you need a way to ensure a row is known to be a particular month. If this is the order of the columns, that's fine, but when you have 2 rows of data, how do you know which one goes with which window? There is NO order in the table for rows, regardless of when they are inserted. You need  to have some way to differentiate rows.

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

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