Stored Procedure That Needs To Loop Through DataSet And Summarize Based On TypeCode

Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.





This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.





Region FullName SHARP Year Ann Goal YTD Goal YTDActual


Region1 Doe10, John X 2003 20400 5100 0 Select


Region1 Doe10, John X 2003 0 0 3987 Select


Region1 Doe11, John X 2003 29645 7411.25 0 Select


Region1 Doe11, John X 2003 0 0 5377 Select





Here's my stored procedure:





CREATE PROCEDURE spFilterRegion





@RIDsent As Integer,


@StatusSent As Integer,


@SelectedRegion As NVARCHAR (50) Output





AS


SELECT Region.CountryID,


Employee.RegionID,


Employee.StatusID,


Employee.SHARP,


CASE


When Employee.SHARP = 1 THEN "X"


ELSE ""


END AS SHARPresult,


Employee.LastName,


Employee.FirstName,


Employee.LastName + ', ' + FirstName AS FullName,


Employee.EmployeeID,


Region.RegionName,


ProducerRevenue.RevenueTypeID,


CASE


When ProducerRevenue.RevenueTypeID = 1 Then


SUM(ProducerRevenue.Revenue)


ELSE 0


END AS AnnGoal,


CASE


When ProducerRevenue.RevenueTypeID = 1 Then


SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE())


ELSE 0


END AS YTDGoal,


CASE


When ProducerRevenue.RevenueTypeID = 2 Then


SUM(ProducerRevenue.Revenue)


ELSE 0


END AS Actual,


ProducerRevenue.YearID


FROM Employee


LEFT OUTER JOIN ProducerRevenue


ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND


ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND


ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND


ProducerRevenue.StatusID = 1 AND


ProducerRevenue.RevenueTypeID <= 2


LEFT OUTER JOIN Region


ON Employee.RegionID = Region.RegionID


WHERE Employee.StatusID = @StatusSent AND


Employee.RegionID = @RIDsent AND


Employee.RoleID = 1


GROUP BY Region.CountryID,


Employee.RegionID,


Region.RegionName,


Employee.RoleID,


Employee.StatusID,


Employee.SHARP,


Employee.LastName,


Employee.FirstName,


Employee.EmployeeID,


ProducerRevenue.RevenueTypeID,


ProducerRevenue.YearID


ORDER BY Region.CountryID,


Employee.RegionID,


Employee.RoleID,


Employee.StatusID,


Employee.SHARP,


Employee.LastName,


Employee.FirstName,


Employee.EmployeeID,


ProducerRevenue.RevenueTypeID,


ProducerRevenue.YearID