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.

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.

• 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!"