?
Solved

t-sql query

Posted on 2014-03-04
3
Medium Priority
?
280 Views
Last Modified: 2014-03-05
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
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 39905608
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39907498
By using LAG function, I am not sure whether SQL2008 is supported
No.  This was added with SQL Server 2012.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39908144
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

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

777 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