Compare data with previous period

Question

Post reply

Compare data with previous period

Abhas

SSC Enthusiast

Points: 178

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: 89773 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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Abhas SSC Enthusiast Points: 178 More actions · Answer 2

Abhas

SSC Enthusiast

Points: 178

September 9, 2020 at 3:52 am

#3787195

Thank you so much Jeffrey Williams. Its working for me

Thanks,

Abhas J.

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions