September 8, 2020 at 6:44 pm
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 8, 2020 at 7:19 pm
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
September 9, 2020 at 3:52 am
Thank you so much Jeffrey Williams. Its working for me
Thanks,
Abhas J.
September 9, 2020 at 4:23 am
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
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