Compare data with previous period

Question

Post reply

Compare data with previous period

Abhas

SSC Enthusiast

Points: 181

September 8, 2020 at 6:44 pm

#3787086

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.

September 9, 2020 at 5:57 am

This was removed by the editor as SPAM

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Abhas SSC Enthusiast Points: 181 More actions · Answer 2

Thank you so much Jeffrey Williams. Its working for me

Thanks,

Abhas J.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

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.

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