SELECT to return sum and last value in table 1

[timroop]

I need to return a period to date total as well as a last value from a table. Given the following data set...

Declare @Temp Table(EmpId VarChar(20), Checkdate smalldatetime, Gross_Pay numeric(19,5), IRA_Contribution numeric(19,5))
Insert Into @Temp Values('01123DOE', '01/01/2010', 500.00, 5.00)
Insert Into @Temp Values('01123DOE', '01/02/2010', 600.00, 6.00)
Insert Into @Temp Values('01123DOE', '01/03/2010', 550.00, 5.50)
Insert Into @Temp Values('01123TIM', '01/01/2010', 1000.00, 10.00)
Insert Into @Temp Values('01123TIM', '01/02/2010', 1100.00, 11.00)
Insert Into @Temp Values('01123TIM', '01/03/2010', 1150.00, 11.50)
Insert Into @Temp Values('01123MRK', '01/01/2010', 1000.00, 10.00)
Insert Into @Temp Values('01123MRK', '01/02/2010', 1100.00, 11.00)

I need to return the sum of the gross pay as well as the last value (by date) of the IRA_Contrbution grouped by the Employee's ID. So the query should return something like...

EmpID    Sum_Gross Last_IRA
01123DOE   1650.00     5.50
01123TIM   3250.00    11.00
01123MRK   2100.00     0.00*

*The IRA_contribution for 01123MRK is 0 becuase there was no check for the last check date of 01-03-2010.

The Sum_gross is simple. I don't see an elegant way to grab the latest value for the IRA though. I was looking for something like the LAST() aggregate in Access but I know SQL doesn't have that type of function.

Any ideas?


Thank you.

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[r937]

SELECT t.EmpId
     , m.Sum_Gross
     , CASE WHEN m.Last_Date = '01/03/2010'
            THEN t.IRA_Contribution 
            ELSE 0 END AS Last_IRA
  FROM ( SELECT EmpId
              , SUM(Gross_Pay) AS Sum_Gross
              , MAX(Checkdate) AS Last_Date
           FROM @Temp
         GROUP
             BY EmpId ) AS m
INNER
  JOIN @Temp AS t
    ON t.EmpId = m.EmpId
   AND t.CheckDate = m.Last_Date

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RiverGuy]

At first, I misread and thought you were wanting the Last_IRA as specific to the employee's last check--but it becomes simpler if you're simply wanting to find the IRA amount for each employee on a specific date. Here's an example. You could get rid of the derived table "a"--I had it in there initially to get the last check date for each employee, but decided to leave it in there in because it will be easier to modify in case you do need to get the Last IRA amount per each employee's last check.

DECLARE @LastCheckDate SMALLDATETIME
--SELECT @LastCheckDate = '20100103'					--Either this one
SELECT @LastCheckDate = MAX(CheckDate) FROM @Temp	--Or this

SELECT GrossPay.*, ISNULL(LastIRA.IRA_Contribution, 0) AS Last_IRA
FROM
	(SELECT EmpID, SUM(Gross_Pay) AS Sum_Gross_Pay
	FROM @Temp
	GROUP BY EmpID) GrossPay
LEFT OUTER JOIN
	(SELECT b.*
	FROM @Temp b
	INNER JOIN
		(SELECT EmpID, Checkdate AS MaxDate
		FROM @Temp
		WHERE Checkdate = @LastCheckDate 
		GROUP BY EmpID, CheckDate) a
	ON b.EmpId = a.EmpId 
	AND b.Checkdate = a.MaxDate) LastIRA
ON GrossPay.EmpId = LastIRA.EmpId

 

[timroop]

[bold]RiverGuy[/bold]

Thank you so much. That last bit of code was the closer. I was close but couldn't get the last bit down.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva