March 25, 2014 at 10:03 pm
Hi, all
I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not sucess..
/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS [Year],
DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-N, -1) [EndMonth],
CAST(RAND(CHECKSUM(NEWID())) * 100 as INT) + N*10 OrderAmt
Into #t
FROM R; -- select * from #t
*/
select t1.ClientID, t1.Rn, t1.EndMonth
,t1.OrderAmt
,t2.OrderAmt PrevOrderAmt
,t1.OrderAmt - t2.OrderAmt Delta
from #t t1
join #t t2 on t2.rn = t1.rn -1
Tx
Mario
March 25, 2014 at 10:49 pm
This query uses the LAG function;
select t1.ClientID, t1.Rn, t1.EndMonth
,t1.OrderAmt
,LAG(t1.OrderAmt,1,0) OVER
(
PARTITION BY ClientID
ORDER BY EndMonth ASC
) AS PrevOrderAmt
,t1.OrderAmt - LAG(t1.OrderAmt,1,0) OVER
(
PARTITION BY ClientID
ORDER BY EndMonth ASC
) AS Delta
from #t t1
March 26, 2014 at 1:00 am
Thanks much, Eirikur !
I also plan to put it all into SSRS 2012 with Columns Groups and I assume that's it's better do as much as possible in SQL.
Best
Mario
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply