SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Aggrating data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Menorel
Starting Member

USA
15 Posts

Posted - 07/15/2013 :  09:12:02  Show Profile  Reply with Quote
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/2013

Temp 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_Holiday
FROM         vw_PayRollDataWithHoliday
GROUP BY EmployeeID, ExternalID, StartDate, TempDept, ActivityType, Is_Paid, CASE WHEN Is_Holiday IS NULL THEN 0 ELSE Is_Holiday END
HAVING      (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_Holiday
520691 3662 249983 WORKED 5.98 1 0
520691 3662 249983 WORKED 7.93 1 0
520691 3662 249983 WORKED 8.11 1 0
520691 3662 249983 WORKED 3.49 1 0

I would like it to populate down to:

EmployeeID ExternalID TempDept ActivityType Worked Is_Paid Is_Holiday
520691 3662 249983 WORKED 25.51 1 0

Edited by - Menorel on 07/15/2013 09:13:39

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 07/15/2013 :  09:56:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		EmployeeID, 
		ExternalID, 
		TempDept, 
		ActivityType, 
		SUM(Worked) AS Worked, 
		Is_Paid, 
		ISNULL(Is_Holiday, 0) AS Is_Holiday
FROM		dbo.vw_PayRollDataWithHoliday
WHERE		StartDate BETWEEN @StartDate AND @EndDate
		AND ExternalID = @ExternalID
		AND ActivityType = N'WORKED'
GROUP BY	EmployeeID,
		ExternalID, 
		TempDept, 
		ActivityType, 
		Is_Paid, 
		ISNULL(Is_Holiday, 0);



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/15/2013 :  11:45:41  Show Profile  Reply with Quote
Worked like a charm.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000