October 4, 2010 at 2:24 pm
I have 1 table that I am trying to build a record out of. Will say for sample purposes that the table has the following structure:
Name: MyTable
Columns:
EmployeeID int,
DataYear int,
YearlyWage float
I need to report off of this table so that each record returns 4 columns as follows:
EmployeeID | DataYear | YearlyWage | FiveYearWage
So I need to query the table and get the total wage for the last 5 years plus the current wage. I'm thinking I need to do a sub query or something of the sort. Here is some test data and required output:
-- This is the test data
DECLARE @MyTable TABLE
(
EmployeeID INT,
DataYear int,
YearlyWage float
)
INSERT INTO @MyTable (EmployeeID,DataYear,YearlyWage)
SELECT 1001,1990,25000 UNION ALL
SELECT 1001,1991,25000 UNION ALL
SELECT 1001,1992,30000 UNION ALL
SELECT 1001,1993,30000 UNION ALL
SELECT 1001,1994,40000 UNION ALL
SELECT 1001,1995,40000 UNION ALL
SELECT 1001,1996,50000 UNION ALL
SELECT 1001,1997,50000 UNION ALL
SELECT 1001,1998,60000
-- This is desired output
SELECT 1001,1990,25000,25000 UNION ALL
SELECT 1001,1991,25000,50000 UNION ALL
SELECT 1001,1992,30000,80000 UNION ALL
SELECT 1001,1993,30000,110000 UNION ALL
SELECT 1001,1994,40000,150000 UNION ALL
SELECT 1001,1995,40000,165000 UNION ALL
SELECT 1001,1996,50000,190000 UNION ALL
SELECT 1001,1997,50000,210000 UNION ALL
SELECT 1001,1998,60000,240000
This is my attempted solution, which of course fails
-- This is the test data
DECLARE @MyTable TABLE
(
EmployeeID INT,
DataYear int,
YearlyWage float
)
INSERT INTO @MyTable (EmployeeID,DataYear,YearlyWage)
SELECT 1001,1990,25000 UNION ALL
SELECT 1001,1991,25000 UNION ALL
SELECT 1001,1992,30000 UNION ALL
SELECT 1001,1993,30000 UNION ALL
SELECT 1001,1994,40000 UNION ALL
SELECT 1001,1995,40000 UNION ALL
SELECT 1001,1996,50000 UNION ALL
SELECT 1001,1997,50000 UNION ALL
SELECT 1001,1998,60000
-- This is my attempt
SELECT
MT.EmployeeID,
MT.DataYear,
MT.YearlyWage,
FiveYearWage =
(SELECT
SUM(CASE WHEN DataYear BETWEEN MT.DataYear - 4 AND MT.DataYear THEN YearlyWage ELSE 0 END)
FROM
@MyTable AS TEMP
WHERE
TEMP.EmployeeID = MT.EmployeeID
GROUP BY TEMP.EmployeeID)
FROM
@MyTable MT
October 4, 2010 at 4:39 pm
SELECT EmployeeID,
DataYear,
YearlyWage,
FiveYearWage = (SELECT SUM(YearlyWage)
FROM @MyTable t2
WHERE t2.EmployeeID = t1.EmployeeID
AND t2.DataYear <= t1.DataYear
AND t1.DataYear - t2.DataYear < 5)
FROM @MyTable t1;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 5, 2010 at 1:18 am
Or without a recursive query
select t1.EmployeeID, t1.DataYear, t1.YearlyWage, Sum(t2.YearlyWage)
from @MyTable t1
join @MyTable t2 on t2.EmployeeID = t1.EmployeeID
and t2.DataYear between t1.DataYear - 4 and t1.DataYear
group by t1.EmployeeID, t1.DataYear, t1.YearlyWage
order by t1.EmployeeID, t1.DataYear
/T
October 5, 2010 at 1:02 pm
WayneS (10/4/2010)
SELECT EmployeeID,DataYear,
YearlyWage,
FiveYearWage = (SELECT SUM(YearlyWage)
FROM @MyTable t2
WHERE t2.EmployeeID = t1.EmployeeID
AND t2.DataYear <= t1.DataYear
AND t1.DataYear - t2.DataYear < 5)
FROM @MyTable t1;
Thanks, this is what I was looking for. Just curious though, I thought you always had to use a GROUP BY clause when using an aggregate. Is this dismissed because it is assumed a sub query groups based on the join to the main query?
October 5, 2010 at 1:14 pm
loki1049 (10/5/2010)
WayneS (10/4/2010)
SELECT EmployeeID,DataYear,
YearlyWage,
FiveYearWage = (SELECT SUM(YearlyWage)
FROM @MyTable t2
WHERE t2.EmployeeID = t1.EmployeeID
AND t2.DataYear <= t1.DataYear
AND t1.DataYear - t2.DataYear < 5)
FROM @MyTable t1;
Thanks, this is what I was looking for. Just curious though, I thought you always had to use a GROUP BY clause when using an aggregate. Is this dismissed because it is assumed a sub query groups based on the join to the main query?
Nope, it's dismissed because you're not grouping on anything. Your where clause is limited (and reran) for each of the t1.columns, so your aggregate is determined based on that.
The other way to do it would be to use it as a subquery and group by on EmployeeID and DataYear, and have a 5 year for each datayear, then link back, which would require the group by.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2010 at 2:43 pm
Ok I got this thing working mostly. The problem is I simplified my example too much and now can't seem to get it to work. The table actaully holds a value for quarter (1-4) for each year, and depending on the quarter I have to look back and get 5years worth of quarters starting at the current records quarter. I also need to get the previous value for the last 5 quarters, which I have figured out thanks to the help. But I'm not too sure how to go about getting five years worth of previous quarters as a total...
So this is what I am working with now, changed the EmployeeID to just employee so I could see a name instead for the moment. Also added the quarters to the table.
-- This is the test data
DECLARE @MyTable TABLE
(
Employee nvarchar(4),
DataYear int,
DataQuarter int,
Dose float
)
INSERT INTO @MyTable (Employee,DataYear,Dose,DataQuarter)
SELECT 'Mike',2010,0.568,4 UNION ALL
SELECT 'Mike',2010,0.698,3 UNION ALL
SELECT 'Mike',2010,0.223,2 UNION ALL
SELECT 'Mike',2010,0.111,1 UNION ALL
SELECT 'Mike',2009,0.203,4 UNION ALL
SELECT 'Mike',2009,0.869,3 UNION ALL
SELECT 'Mike',2009,0.438,2 UNION ALL
SELECT 'Mike',2009,0.456,1 UNION ALL
SELECT 'Mike',2008,0.656,4 UNION ALL
SELECT 'Mike',2008,0.889,3 UNION ALL
SELECT 'Mike',2008,0.277,2 UNION ALL
SELECT 'Mike',2008,0.396,1 UNION ALL
-- Second test employee
SELECT 'Dave',2010,0.1,4 UNION ALL
SELECT 'Dave',2010,0.666,3 UNION ALL
SELECT 'Dave',2010,0.1,2 UNION ALL
SELECT 'Dave',2010,0.1,1 UNION ALL
SELECT 'Dave',2009,0.1,4 UNION ALL
SELECT 'Dave',2009,0.1,3 UNION ALL
SELECT 'Dave',2009,0.1,2 UNION ALL
SELECT 'Dave',2009,0.1,1 UNION ALL
SELECT 'Dave',2008,NULL,4 UNION ALL
SELECT 'Dave',2008,NULL,3 UNION ALL
SELECT 'Dave',2008,NULL,2 UNION ALL
SELECT 'Dave',2008,NULL,1
-- And would need to get a running total of 5 years worth of quarters back
SELECT
FiveYear = IsNull((SELECT SUM(IsNull(Dose,0)) FROM @MyTable t2 WHERE t2.Employee = t1.Employee
AND t2.DataYear <= t1.DataYear AND t1.DataYear - t2.DataYear < 5),0),
Employee,
DataYear,
DataQuarter,
Quarter1 = IsNull((SELECT SUM(IsNull(Dose,0)) FROM @MyTable t2 WHERE t2.Employee = t1.Employee
AND (t2.DataYear = t1.DataYear AND t2.DataQuarter = t1.DataQuarter)),0),
Quarter2 = IsNull((SELECT SUM(IsNull(Dose,0)) FROM @MyTable t2 WHERE t2.Employee = t1.Employee
AND t2.DataYear = (CASE WHEN t1.DataQuarter - 1 = 0 THEN t1.DataYear - 1 ELSE t1.DataYear END)
AND t2.DataQuarter = (CASE WHEN t1.DataQuarter - 1 = 0 THEN 4 ELSE t1.DataQuarter - 1 END)),0),
Quarter3 = IsNull((SELECT SUM(IsNull(Dose,0)) FROM @MyTable t2 WHERE t2.Employee = t1.Employee
AND t2.DataYear = (CASE WHEN t1.DataQuarter - 2 <= 0 THEN t1.DataYear - 1 ELSE t1.DataYear END)
AND t2.DataQuarter = (CASE WHEN t1.DataQuarter - 2 <= 0 THEN 4 + (t1.DataQuarter - 2) ELSE t1.DataQuarter - 2 END)),0),
Quarter4 = IsNull((SELECT SUM(IsNull(Dose,0)) FROM @MyTable t2 WHERE t2.Employee = t1.Employee
AND t2.DataYear = (CASE WHEN t1.DataQuarter - 3 <= 0 THEN t1.DataYear - 1 ELSE t1.DataYear END)
AND t2.DataQuarter = (CASE WHEN t1.DataQuarter - 3 <= 0 THEN 4 + (t1.DataQuarter - 3) ELSE t1.DataQuarter - 3 END)),0),
Quarter5 = IsNull((SELECT SUM(IsNull(Dose,0)) FROM @MyTable t2 WHERE t2.Employee = t1.Employee
AND t2.DataYear = t1.DataYear-1
AND t2.DataQuarter = t1.DataQuarter),0)
FROM @MyTable t1
ORDER BY Employee DESC, Datayear DESC, Dataquarter desc
I obviously don't have the correct method for doing the 5 year part, I have been playing around with it but can't figure out how. Also, if anyone thinkgs this is a horrible query I am open to suggestions. I want to create a view out of this so I can report indiviuals based on a query of a year and quarter and have it all sit in one view.
EDIT: Thanks for the help so far.
October 5, 2010 at 4:24 pm
I got it, damn that took a long time. Thanks for the examples I was dead in the water trying this all yesterday
To get 5 years worth of quarters data
FiveYear = (SELECT SUM(IsNull(Dose,0)) FROM @MyTable t2 WHERE t2.Employee = t1.Employee
AND t2.DataYear BETWEEN t1.DataYear - 5 AND t1.DataYear
AND(t2.DataQuarter BETWEEN
(CASE WHEN t2.DataYear = t1.DataYear - 5 THEN t1.DataQuarter+1 ELSE 1 END) AND (CASE WHEN t2.DataYear = t1.DataYear THEN t1.DataQuarter ELSE 4 END))
)
October 5, 2010 at 5:55 pm
loki1049 (10/5/2010)
WayneS (10/4/2010)
SELECT EmployeeID,DataYear,
YearlyWage,
FiveYearWage = (SELECT SUM(YearlyWage)
FROM @MyTable t2
WHERE t2.EmployeeID = t1.EmployeeID
AND t2.DataYear <= t1.DataYear
AND t1.DataYear - t2.DataYear < 5)
FROM @MyTable t1;
Thanks, this is what I was looking for. Just curious though, I thought you always had to use a GROUP BY clause when using an aggregate. Is this dismissed because it is assumed a sub query groups based on the join to the main query?
No, you have to use a GROUP BY when mixing columns and aggregates. If you not using any columns outside of the aggregate then the GROUP BY isn't needed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 6, 2010 at 11:25 am
Right.. I knew that. Clearly I am trying to make this too complicated in my mind. Thanks
October 7, 2010 at 2:50 am
SELECT t1.EmployeeID
,t1.DataYear
,t1.YearlyWage
,t3.total
FROM@MyTable t1
CROSS APPLY(SELECT SUM(t2.YearlyWage) AS Total
FROM@MyTable t2
WHERE t2.EmployeeID = t1.EmployeeID
ANDt2.DataYear < = t1.DataYear
GROUP BY t2.EmployeeID) AS t3
October 7, 2010 at 2:53 am
Oh, i've missed 5-year condition... :crying:
October 9, 2010 at 12:33 pm
Victor,
Just change your date comparison from:
AND t2.DataYear < = t1.DataYear
to
AND t2.DataYear BEWEEN t1.DataYear - 5 And T1.DataYear
Todd Fifield
October 11, 2010 at 1:36 am
Thanks, Todd!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply