In the data table there is the possibility of each date having multiple time entries for a user. as well as multiple dates for the user. I am attempting to aggragte this down to a single record but I am still getting one record for each date.Is there something I am missing about aggragation that is not allowing it to populate to one record.Date range used would be for Between 7/7/2013 and 7/13/2013Temp Table and Query:CREATE TABLE #tbl_WORKEDPayRoll(EmployeeID int,ExternalID int,TempDept int,ActivityType nchar(6),Worked decimal(10,2),Is_Paid bit,Is_Holiday bit)INSERT INTO #tbl_WORKEDPayRoll (EmployeeID, ExternalID, TempDept, ActivityType, Worked, Is_Paid, Is_Holiday)SELECT EmployeeID, ExternalID, TempDept, ActivityType, SUM(Worked) AS Worked, Is_Paid, CASE WHEN Is_Holiday IS NULL THEN 0 ELSE Is_Holiday END AS Is_HolidayFROM vw_PayRollDataWithHolidayGROUP BY EmployeeID, ExternalID, StartDate, TempDept, ActivityType, Is_Paid, CASE WHEN Is_Holiday IS NULL THEN 0 ELSE Is_Holiday ENDHAVING (StartDate BETWEEN @StartDate AND @EndDate) AND (ExternalID = @ExternalID) AND (ActivityType = N'WORKED')SELECT * FROM #tbl_WORKEDPayRoll
Sample data from Select statement:EmployeeID ExternalID TempDept ActivityType Worked Is_Paid Is_Holiday520691 3662 249983 WORKED 5.98 1 0520691 3662 249983 WORKED 7.93 1 0520691 3662 249983 WORKED 8.11 1 0520691 3662 249983 WORKED 3.49 1 0I would like it to populate down to:EmployeeID ExternalID TempDept ActivityType Worked Is_Paid Is_Holiday520691 3662 249983 WORKED 25.51 1 0
Aggrating data
Posted - 2013-07-15 : 09:12:02
