T-SQL calculate difference between 1 and 5 year intervals in a value column

Hey Friends - I'm not sure how to approach this

I have a table, let's say it's similar to:

Name      Year      Value
Joe           2014       25
Joe           2013       22
Joe           2012       26
Mark       2014        33
Mark       2013        31
Mark        2012       34

and it goes back years.

I want to add two columns for "1 year change" and "5 year change" on a running difference for a given name.

So the finished output would look like this (if possible)

Name      Year      Value      1-yr-change      5-yr-change
Joe           2014       25              3                        8
Joe           2013       22              -4                        -7
Joe           2012       26               2                       11
Mark       2014        33              2                        -14
Mark       2013        31              -3                        6
Mark        2012       34              1                        4

Is this possible to do in a T-SQL Query on my SQL Server 2008r2 instance - in a select statement?

If not - what is possible?

Thank you so much!
LVL 7
Gladys KernsAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
Yeah, that join is not going to work, because it gives you one row for each combination of the two tables.  Try this instead.

select t1.Agency
, t1.FY
, t1.[Tax Appropriations]
, t1.[Tax Appropriations] - (SELECT T2.[Tax Appropriations] FROM dbo.SHEFMain as t2 WHERE T2.Agency = T1.Agency AND T2.FY = T1.Agency-1) as 1-yr-change
, t1.[Tax Appropriations] - (SELECT T3.[Tax Appropriations] FROM dbo.SHEFMain as t3 WHERE t3.Agency = T1.Agency AND T3.FY = T1.Agency - 5) as 5-yr-change
from dbo.SHEFMain as t1
order by T1.agency, T1.fy DESC
0
 
Dale FyeCommented:
From your sample data, it is difficult to see where you are getting the 5-yr-change, but I suppose something like the following might work.

SELECT T1.Name, T1.Year, T1.Value
,1-yr-change=CASE WHEN T2.Year = T1.Year - 1 Then T1.Value - T2.Value ELSE NULL END
,5-yr-change=CASE WHEN T2.Year = T1.Year - 5 Then T1.Value - T2.Value ELSE NULL
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.[Name] = T2.[Name] AND T2.Year < T1.Year
0
 
Gladys KernsAuthor Commented:
Dale - this is close... I'm sorry I didn't supply enough sample data but you definitely understood what I'm after.  Thank you!

Here's what I wrote based on your model using the actual fields from my table:

select t1.Agency, t1.FY, t1.[Tax Appropriations],
case when t2.FY = T1.FY - 1 then T1.[Tax Appropriations] - T2.[Tax Appropriations] else null end as '1-yr-change',
case when t2.FY = T1.FY - 5 then T1.[Tax Appropriations] - T2.[Tax Appropriations] else null end as '5-yr-change'
from dbo.SHEFMain as t1
LEFT JOIN dbo.SHEFMain as t2 on T1.Agency = T2.Agency and T2.FY < T1.FY
order by agency, fy desc

I'm getting the correct calculations but also a bunch of duplicate rows with null values in the calculation.  I'm confused as to why this is.  The source table does not contain duplicate values so this is a join confusion. Obviously I'm new at this!

Here's a sample of the first few rows of output:

Alabama	2015	1465539719.00	23677415.00	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	41697377.00
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2015	1465539719.00	NULL	NULL
Alabama	2014	1441862304.00	34963811.00	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	-139346642.00
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2014	1441862304.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	-87684688.00	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	-554909849.00
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2013	1406898493.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	NULL	-190484308.00
Alabama	2012	1494583181.00	NULL	NULL
Alabama	2012	1494583181.00	69666131.00	NULL

Open in new window


Thanks for your help so far!  We're close!
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Gladys KernsAuthor Commented:
This seems to have worked PERFECTLY... thank you so much!!
0
 
Gladys KernsAuthor Commented:
The perfect solution for my problem - thank you!
0
 
Valliappan ANSenior Tech ConsultantCommented:
-- basically use 2 CTEs for each total, group by agency and financial year, and then join everything to get the query
WITH OneYear
AS
(
select t1.Agency, t1.FY, SUM(T1.[Tax Appropriations] - T2.[Tax Appropriations]) as '1-yr-change',
from dbo.SHEFMain as t1
JOIN dbo.SHEFMain as t2 on T1.Agency = T2.Agency and T2.FY = T1.FY-1
GROUP BY t1.Agency, t1.FY
),
WITH FiveYear
AS
(
select t1.Agency, t1.FY, SUM(T1.[Tax Appropriations] - T2.[Tax Appropriations]) as '5-yr-change',
from dbo.SHEFMain as t1
JOIN dbo.SHEFMain as t2 on T1.Agency = T2.Agency and T2.FY = T1.FY-5
GROUP BY t1.Agency, t1.FY
)
,
select t1.Agency, t1.FY, t1.[Tax Appropriations],
'1-yr-change',
'5-yr-change'
from dbo.SHEFMain as t1
LEFT JOIN OneYear as t2 on T1.Agency = T2.Agency and T2.FY = T1.FY
LEFT JOIN FiveYear as t3 on T1.Agency = T3.Agency and T3.FY = T1.FY
order by agency, fy desc

Open in new window


I could not test the query, so please run and let know if that works.
HTH.
0
 
Dale FyeCommented:
I was going to mention that I am not an expert a SQL Server, so the solution provided by Valliappan AN might run faster.
0
 
PortletPaulfreelancerCommented:
@Dale

In SQL Server you can avoid correlated subqueries in the select clause by using "APPLY" these can be CROSS APPLY or OUTER APPLY (where the outer apply allows unmatched rows from other tables in the query).

The advantage is that these are part of the FROM clause and hence are treated along with the other sets of that clause making them more efficient than correlated subqueries within a SELECT clause. Additionally aliases used in the apply operator can be reused elsewhere in the query.

e.g.
SELECT
      T1.Agency
    , T1.FY
    , T1.[Tax Appropriations]
    , T1.[Tax Appropriations] - OA1.minusone AS [1-YR-CHANGE]
    , T1.[Tax Appropriations] - OA2.minusfive AS [5-YR-CHANGE]
FROM dbo.SHEFMain AS T1
OUTER APPLY (
      SELECT
            T2.[Tax Appropriations]
      FROM dbo.SHEFMain AS T2
      WHERE T2.Agency = T1.Agency
            AND T2.FY = T1.Agency - 1
      ) OA1 (MINUSONE)
OUTER APPLY (
      SELECT
            T3.[Tax Appropriations]
      FROM dbo.SHEFMain AS T3
      WHERE T3.Agency = T1.Agency
            AND T3.FY = T1.Agency - 5
      ) OA2 (MINUSFIVE)
ORDER BY T1.agency, T1.fy DESC

Open in new window

0
 
Dale FyeCommented:
Thanks, Paul.

After 20+ years of writing Access queries, I know I've got a lot to learn to write efficient queries in SQL Server.

Dale
0
 
Gladys KernsAuthor Commented:
Thanks again everyone! I'll revise my code tomorrow, Paul. Thank you!
0
 
PortletPaulfreelancerCommented:
oh, without data to test with you may also need ISNULL()

conceptually you could have data where there is no information for past years, so either of those subqueries could return NULL

in that case you may need ISNULL() for those 2 calculations like this

SELECT
      T1.Agency
    , T1.FY
    , T1.[Tax Appropriations]
    , T1.[Tax Appropriations] - ISNULL(OA1.minusone,0) AS [1-YR-CHANGE]
    , T1.[Tax Appropriations] - ISNULL(OA2.minusfive,0) AS [5-YR-CHANGE]
0
 
Gladys KernsAuthor Commented:
I'm OK nulls in this data, for the purpose of later aggregation, null means something different than zero. thanks so much again!
0
 
Valliappan ANSenior Tech ConsultantCommented:
Yes, subqueries take up more CPU and/or reads and so a longer query duration.

Paul, does not CROSS APPLY also run similar to subqueries when scan is considered, since for each record there needs to be a CROSS APPLY, instead of an OUTER JOIN. IMO, an OUTER JOIN could be better in this scenario, not sure though, need to check the performance, and/or estimation plans.

HTH.
0
 
PortletPaulfreelancerCommented:
without more information it may not be possible to identify the optimal choice - as always the execution plan should be the reference. outer join might be better but so too might be use of windowing functions if the version of sql server supports the ones needed. i.e. we don't know what we don't know :)

I was really only trying to show a quick alternative to correlated subqueries in the select clause.

and we may be confusing otter77 who has already found a functionally acceptable solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.