t-sql query

I'm using sql server 2008.

I have a table that looks like this:

my table
Here is my create table script

CREATE TABLE [dbo].[TestTable](
	[EmployeeID] [smallint] NULL,
	[Region] [smallint] NULL,
	[WorkYear] [smallint] NULL,
	[ProjectedYear] [smallint] NULL,
	[GrossPay] [money] NULL,
	[Benefits] [money] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (1, 1, 2014, 0, 21522.1800, 5863.5700)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (1, 1, 2014, 1, 30253.2700, 6087.1000)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (1, 1, 2014, 2, 30755.3800, 6150.5500)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (1, 1, 2014, 3, 31371.2200, 6228.3100)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (1, 1, 2014, 4, 32225.3900, 6336.2100)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (1, 1, 2014, 5, 33187.2100, 6457.6700)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (2, 1, 2014, 0, 21200.0400, 2394.5600)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (2, 1, 2014, 1, 22340.0900, 2563.5900)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (2, 1, 2014, 2, 23546.3400, 2563.5900)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (2, 1, 2014, 3, 24356.2700, 2685.4500)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (2, 1, 2014, 4, 25636.4500, 2865.6500)
INSERT [dbo].[TestTable] ([EmployeeID], [Region], [WorkYear], [ProjectedYear], [GrossPay], [Benefits]) VALUES (2, 1, 2014, 5, 26435.5800, 2953.5200)

Open in new window


I have a query that looks like this:

SELECT [EmployeeID]
      ,[Region]
      ,[WorkYear]
      ,[ProjectedYear]
      ,[WorkYear] + [ProjectedYear] AS ActualYear
      ,[GrossPay]
      ,[Benefits]
      ,[GrossPay] + [Benefits] AS TotalCompensation
FROM [TestTable]

So my result looks like this:

my query result set
So now I want to add 2 columns to my query.
I want to add a column called DifferenceFromPriorYear and another one called DifferenceFromCurrentYear.

Here is a little explanation on the two columns I want to add to my query:


DifferenceFromPrior year
This column looks at the column called TotalCompensation. It takes the value in the TotalCompensation column and substractrs it from the prior year of that same column and then I want the result of that to be placed in the DifferenceFromPriorYear column.

If you look at the ProjectedYear column, the 0 means it is the current year.
This table is meant to show the salary projection of an employee for the current year all the way to 5 years from the current year.
If you notice, my table has data for two employees.


DifferenceFromCurrentYear
This column looks at the column called TotalCompensation. It takes the value in the TotalCompensation column and substracts it from the value in that same column pertaining to the item for that employee for the current year. So the value in the  TotalCompensation column is subtracted from the item for that employee where ProjectedYear equals 0.



Below is a pic of what I want my result set to be. The green and blue columns are the columns I want to add and what the resulting values should be in each row with the existing data that my table currently contains.

my desired result set
Here is a link to the spreadsheet of my pic i created, it might help in seeing how my 2 columns are calculated

Spreadsheet

Can anyone help?
LVL 1
maqskywalkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kishan ZunjareSr. Software EngineerCommented:
Hi,

There are two ways to do this;
1. By using LAG function, I am not sure whether SQL2008 is supported

DifferenceFromPriorYear
SELECT [EmployeeID]
      ,[Region]
      ,[WorkYear]
      ,[ProjectedYear]
      ,[WorkYear] + [ProjectedYear] AS ActualYear
      ,[GrossPay]
      ,[Benefits]
      ,[GrossPay] + [Benefits] AS TotalCompensation       
      ,CASE WHEN LAG(([GrossPay] + [Benefits]),1,0) OVER(ORDER BY ([GrossPay] + [Benefits]) ASC) = 0 THEN 0
            ELSE (([GrossPay] + [Benefits]) - LAG([GrossPay] + [Benefits],1,0) OVER(ORDER BY ([GrossPay] + [Benefits]) ASC) )
            END AS DifferenceFromPriorYear
FROM TestTable
WHERE employeeid = 1


DifferenceFromCurrentYear
WITH tempDifferenceFromPrior AS
(
      SELECT
             [EmployeeID]
            ,[Region]
            ,[WorkYear]
            ,[ProjectedYear]
            ,[WorkYear] + [ProjectedYear] AS ActualYear
            ,[GrossPay]
            ,[Benefits]
            ,[GrossPay] + [Benefits] AS TotalCompensation      
            ,CASE WHEN LAG(([GrossPay] + [Benefits]),1,0) OVER(ORDER BY ([GrossPay] + [Benefits]) ASC) = 0 THEN 0
            ELSE (([GrossPay] + [Benefits]) - LAG([GrossPay] + [Benefits],1,0) OVER(ORDER BY ([GrossPay] + [Benefits]) ASC) )
            END AS DifferenceFromPrior
      FROM TestTable
      WHERE employeeid = 1
)    

SELECT  t1.[EmployeeID]
            ,t1.[Region]
            ,t1.[WorkYear]
            ,t1.[ProjectedYear]
            ,t1.[WorkYear] + t1.[ProjectedYear] AS ActualYear
            ,t1.[GrossPay]
            ,t1.[Benefits]
            ,t1.[GrossPay] + t1.[Benefits] AS TotalCompensation
            ,t1.DifferenceFromPrior
            ,SUM(t2.DifferenceFromPrior) AS DifferenceFromCurrentYear  
FROM tempDifferenceFromPrior t1  
INNER JOIN tempDifferenceFromPrior t2 on t1.ProjectedYear >= t2.ProjectedYear  
GROUP BY t1.ProjectedYear, t1.DifferenceFromPrior ,t1.[EmployeeID]
            ,t1.[Region]
            ,t1.[WorkYear]
            ,t1.[ProjectedYear]            
            ,t1.[GrossPay]
            ,t1.[Benefits]
            ,t1.DifferenceFromPrior
ORDER BY t1.ProjectedYear

2. If LAG function not supported then first you need to create auto increment field as "id"

DifferenceFromPriorYear
SELECT
        a.[EmployeeID]
      ,a.[Region]
      ,a.[WorkYear]
      ,a.[ProjectedYear]
      ,a.[WorkYear] + a.[ProjectedYear] AS ActualYear
      ,a.[GrossPay]
      ,a.[Benefits]
      ,a.[GrossPay] + a.[Benefits] AS TotalCompensation      
        ,ISNULL((a.GrossPay + a.Benefits) - (b.GrossPay + b.Benefits),0) AS DifferenceFromPriorYear
FROM
      TestTable a      LEFT JOIN TestTable b
ON
      a.id - 1 = b.id
WHERE a.employeeid = 1


Hope this helps
-Kishan
0
Anthony PerkinsCommented:
By using LAG function, I am not sure whether SQL2008 is supported
No.  This was added with SQL Server 2012.
0
Scott PletcherSenior DBACommented:
You could use joins on the year, not on identities (which are not guaranteed to be sequential), but I prefer subqueries in this case, as they seem more logical to me.

You'll want an index on ( EmployeeId, WorkYear /*optional*/, ProjectedYear ).  Hopefully the table is already clustered that way, since that's how the table's likely to be used anyway.


SELECT [EmployeeID]
      ,[Region]
      ,[WorkYear]
      ,[ProjectedYear]
      ,[WorkYear] + [ProjectedYear] AS ActualYear
      ,[GrossPay]
      ,[Benefits]
      ,TotalCompensation
      ,CASE WHEN ProjectedYear = 0 THEN 0 ELSE TotalCompensation - (
           --get prior year's TotalCompensation
           SELECT [GrossPay] + [Benefits]
           FROM [TestTable] tt2
           WHERE
               tt2.[EmployeeID] = tt.[EmployeeID] AND
               tt2.[WorkYear] = tt.[WorkYear] AND
               tt2.[ProjectedYear] = tt.[ProjectedYear] - 1
           ) END AS DifferenceFromPriorYear
      ,CASE WHEN ProjectedYear = 0 THEN 0 ELSE TotalCompensation - (
           --get work year's TotalCompensation
           SELECT [GrossPay] + [Benefits]
           FROM [TestTable] tt2
           WHERE
               tt2.[EmployeeID] = tt.[EmployeeID] AND
               tt2.[WorkYear] = tt.[WorkYear] AND
               tt2.[ProjectedYear] = 0
           ) END AS DifferenceFromCurrentYear
FROM [TestTable] tt
CROSS APPLY (
    SELECT [GrossPay] + [Benefits] AS TotalCompensation
) AS cross_apply_1
ORDER BY
    tt.EmployeeID, tt.WorkYear, tt.ProjectedYear
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.