jrusso (8/20/2014)
I figured out a way to do it. Thank you
Does it look something like this?
SELECT
[AmountGiven] = ISNULL(SUM(a.AmountGiven), 0),
a.YearToDate,
a.EndDate
FROM ( -- a
SELECT
[AmountGiven] = MAX(x.[AmountGiven]),
[EndDate] = MAX(y.[EndDate]),
YearToDate = d.StartYear,
rf.EmployeeID
FROM (VALUES (2011),(2012),(2013),(2014)) d (StartYear)
LEFT JOIN [Staging].[dbo].[DB2] rf
ON YEAR(rf.Startdate) = d.StartYear
AND rf.EmployeeID = '300163315'
OUTER APPLY (
SELECT [AmountGiven] = SUM(d1.AmountGiven)
FROM [Production].[dbo].[DB1] d1
WHERE YEAR(d1.[Date]) = YEAR(d1.Startdate) -- Where is Startdate? d1 or rf?
AND d1.EmployeeID = rf.EmployeeID
) x
CROSS APPLY (SELECT [EndDate] = MAX([Date]) FROM [Production].[dbo].[DB1]) y
GROUP BY d.StartYear, rf.EmployeeID
) a
GROUP BY a.YearToDate, a.EndDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden