Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

t-sql query

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 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