Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

t-sql query

Posted on 2014-03-04
3
Medium Priority
?
289 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 70

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

609 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