Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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?
0
maqskywalker
Asked:
maqskywalker
1 Solution
 
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
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now