Solved

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

Posted on 2015-02-23
14
92 Views
Last Modified: 2015-02-23
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!
0
Comment
Question by:Gladys Kerns
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40626514
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
 
LVL 7

Author Comment

by:Gladys Kerns
ID: 40626556
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40626612
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
 
LVL 7

Author Comment

by:Gladys Kerns
ID: 40626634
This seems to have worked PERFECTLY... thank you so much!!
0
 
LVL 7

Author Closing Comment

by:Gladys Kerns
ID: 40626636
The perfect solution for my problem - thank you!
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40626644
-- 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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40626734
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40626864
@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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40627152
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
 
LVL 7

Author Comment

by:Gladys Kerns
ID: 40627182
Thanks again everyone! I'll revise my code tomorrow, Paul. Thank you!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40627239
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
 
LVL 7

Author Comment

by:Gladys Kerns
ID: 40627259
I'm OK nulls in this data, for the purpose of later aggregation, null means something different than zero. thanks so much again!
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40627461
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40627472
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now