asked
Subquery Calculation
I am trying to create a query to calc the market share difference and change over the previous period. I am not sure if the best approach is at row_number / partition or a pivot does anyone have any insights? create table #sample ( item varchar (5), snapshot_dt datetime, mktshare decimal (13,2)) insert into #sample values ( '12345', '2011-06-01', .50) insert into #sample values ( '12345', '2011-05-15', .25) insert into #sample values ( '12345', '2011-05-01', .35) insert into #sample values ( '12345', '2011-04-15', .45) insert into #sample values ( '12345', '2011-04-01', .25) insert into #sample values ( '12345', '2011-03-15', .45) insert into #sample values ( '12345', '2011-03-01', .55) insert into #sample values ( '12345', '2011-02-15', .50) insert into #sample values ( '12345', '2011-02-01', .30) insert into #sample values ( '12345', '2011-01-15', .50) insert into #sample values ( '12345', '2011-01-01', .40) select item, snapshot_dt,mktshare, ROW_NUMBER() OVER(ORDER BY snapshot_dt DESC) AS 'RowNumber' from #sample
I got it! - results shown are from sample data above ---Create CTE ; With tblDifference as ( Select Row_Number() OVER (Order by snapshot_dt) as RowNumber,item,snapshot_dt,mktshare from #sample ) --Actual Query Select cur.item, convert(varchar, Cur.snapshot_dt,103) as CurrentDay, convert(varchar, Prv.snapshot_dt,103) as PreviousDay, Cur.mktshare as CurrentValue, Prv.mktshare as PreviousValue, Cur.mktshare-Prv.mktshare as Difference from tblDifference Cur Left Outer Join tblDifference Prv On Cur.RowNumber=Prv.RowNumber+1 Order by Cur.snapshot_dt
glad you fixed it and thanks for posting the answer back here for others to reference.
