October 13, 2015 at 4:41 pm
Hello,
I am trying to write a query to calculate the running difference between data on different dates. Below is what my table of data looks like. Basically I want to calculate the difference between the total_completed for each state and date.
Date States Total_Completed
08/27/15 CA 19,952
09/11/15 CA 26,336
10/02/15 CA 35,444
10/08/15 CA 38,278
08/27/15 CO 2797
09/11/15 CO 3264
10/02/15 CO 4270
10/08/15 CO 4297
below is what I am trying to achieve:
Date States Total_Completed Completed_Difference
08/27/15 CA 19,952 0
09/11/15 CA 26,336 6,384
10/02/15 CA 35,444 9,108
10/08/15 CA 38,278 2,834
08/27/15 CO 2797 0
09/11/15 CO 3264 467
10/02/15 CO 4270 1,006
10/08/15 CO 4297 27
below is my code (I almost have what I need) I just can't figure out how show 0 as the completed_difference for the first Date for each state since there is no prior date to calculate against.
MRR_TOTALS_WEEK_OVER_WEEK AS
(
SELECT
T1.[Date]
,T1.States
,T2.Total_Completed
,ROW_NUMBER() OVER(PARTITION BY T1.States ORDER BY T1.States,T1.[Date]) AS ORDERING
FROM TOTAL_CHARTS T1
LEFT JOIN TOTAL_COMPLETED T2 ON T1.[Date] = T2.[Date] AND T1.States = T2.States
)
SELECT
T1.[Date]
,T1.States
,T1.Total_Completed
,(COALESCE(T2.Total_Completed,0) - COALESCE(T1.Total_Completed,0)) AS Completed_Difference
FROM MRR_TOTALS_WEEK_OVER_WEEK T1
LEFT JOIN MRR_TOTALS_WEEK_OVER_WEEK T2 ON (T1.ORDERING = T2.ORDERING - 1) AND T1.States = T2.States
October 13, 2015 at 6:34 pm
Since you posted in a 2014 forum, here is a solution that will work in any version after 2012.
Please read the link in my signature line and note how the data was setup for future posts.
declare @t table
(
TranDate date,
TranState char(2),
Completed int
)
insert @t (TranDate, TranState, Completed)
values
('08/27/15', 'CA', 19952),
('09/11/15', 'CA', 26336),
('10/02/15', 'CA', 35444),
('10/08/15', 'CA', 38278),
('08/27/15', 'CO', 2797),
('09/11/15', 'CO', 3264),
('10/02/15', 'CO', 4270),
('10/08/15', 'CO', 4297);
select *,
coalesce(completed - lag(completed) over(Partition by transtate order by trandate), 0) Diff
from @t
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 13, 2015 at 6:52 pm
LinksUp (10/13/2015)
Since you posted in a 2014 forum, here is a solution that will work in any version after 2012.
Just for the fun, here is a version that will work with versions pre 2012 to 2005
with cte as
(
Select TranDate, TranState, Completed,
Row_Number() over(Partition by TranState Order by TranDate) RowNum
from @t
)
Select c.TranState, c.TranDate, c.completed,
case when p.RowNum is null then 0 else c.completed - p.completed end Diff
from cte c
left outer join cte p
on p.transtate = c.transtate
and c.RowNum = p.RowNum + 1
Please note, this version is much more inefficient than the LAG version.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 13, 2015 at 6:52 pm
Thank you so much that worked like a charm... Also, I will make sure to use the correct format in future posts.
October 13, 2015 at 6:56 pm
brianconner (10/13/2015)
Thank you so much that worked like a charm...
Glad it worked for you.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply