Compare data with previous period

  • Hi All,

    I want to compare data of current period with previous period. sample table n data is below.

    create table #tempdata
    (
    Country varchar(50),
    Fiscal_year varchar(50),
    Posting_period varchar(50),
    Amount float
    )

    insert into #tempdata(Country,Fiscal_year,Posting_period,Amount) values
    ('IND' ,'2020' ,'006' ,30410.6)
    ,('IND' ,'2019' ,'005' ,5711.44)
    ,('IND' ,'2020' ,'005' ,10909.2)
    ,('IND' ,'2019' ,'004' ,10641.7)
    ,('IND' ,'2020' ,'004' ,11382.9)
    ,('US' ,'2020' ,'003' ,21021.3)
    ,('US' ,'2020' ,'002' ,8991.6 )
    ,('US' ,'2020' ,'001' ,31823.4)
    ,('US' ,'2019' ,'001' ,2872.75)

    SELECT * From #tempdata c
    order by c.Country ,c.Posting_period desc

    By using above data , I want output as below.

     

    Please help.

    Thanks,

    Abhas.

     

     

     

     

  • If the totals are already aggregated this way - then you can do this:

       With currentYear
    As (
    Select t.Country
    , t.Fiscal_year
    , t.Posting_period
    , t.Amount
    From #tempdata t
    Where t.Fiscal_year = year(getdate())
    )
    Select cy.Country
    , cy.Fiscal_year
    , cy.Posting_period
    , CurrentYearAmount = cy.Amount
    , PreviousYearAmount = coalesce(py.Amount, 0.00)
    From currentYear cy
    Left Join #tempdata py On py.Country = cy.Country
    And py.Fiscal_year = year(getdate()) - 1
    And py.Posting_period = cy.Posting_period;

    If you have other code that is performing the aggregation - it might be easier to cross-tab the data at the same time as the aggregation, would have to see that other code to be sure.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thank you so much Jeffrey Williams. Its working for me

     

    Thanks,

    Abhas J.

  • Since you're using a version of SQL Server that's 2012 or above, here's a method that eliminates the need for a self join and the extra reads that go with it.

        WITH ctePrev AS
    (
    SELECT c.Country
    ,c.Fiscal_year
    ,c.Posting_Period
    ,Current_Year_Amount = c.Amount
    ,Prev_Year_Amount = LAG(c.Amount,1,0) OVER (PARTITION BY c.Country, c.Posting_period ORDER BY c.Fiscal_year)
    FROM #tempdata c
    WHERE c.Fiscal_Year >= DATENAME(yy,DATEADD(yy,-1,GETDATE()))
    )
    SELECT *
    FROM ctePrev prv
    WHERE prv.Fiscal_Year = DATENAME(yy,GETDATE())
    ORDER BY prv.Country, prv.Posting_period DESC
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

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

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