Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Challenge 72 - Calculate the Payroll Hours of Employees Expand / Collapse
Author
Message
Posted Sunday, December 25, 2011 11:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted to this topic are about the item TSQL Challenge 72 - Calculate the Payroll Hours of Employees

.
Post #1226609
Posted Tuesday, December 27, 2011 12:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 6:57 AM
Points: 7, Visits: 11
SELECT dbo.TC72_Employees.EmpID, dbo.TC72_Employees.EmpName, SUBSTRING(CAST(CONVERT(Date, dbo.TC72_PayrollWeek.WeekStart, 102) AS Varchar(12)), 6, 2)
+ '-' + SUBSTRING(CAST(CONVERT(Date, dbo.TC72_PayrollWeek.WeekStart, 102) AS Varchar(12)), 9, 2) + '-' + SUBSTRING(CAST(CONVERT(Date,
dbo.TC72_PayrollWeek.WeekStart, 102) AS Varchar(12)), 1, 4) AS WeekStart, SUBSTRING(CAST(CONVERT(Date, dbo.TC72_PayrollWeek.WeekEnd, 102)
AS Varchar(12)), 6, 2) + '-' + SUBSTRING(CAST(CONVERT(Date, dbo.TC72_PayrollWeek.WeekEnd, 102) AS Varchar(12)), 9, 2) + '-' + SUBSTRING(CAST(CONVERT(Date,
dbo.TC72_PayrollWeek.WeekEnd, 102) AS Varchar(12)), 1, 4) AS WeekEnd, CAST(D.EarningCode AS Varchar) AS Expr1, D.Hours
FROM dbo.TC72_Employees INNER JOIN
dbo.TC72_PayrollWeek ON dbo.TC72_Employees.EmpID = dbo.TC72_PayrollWeek.EmpID INNER JOIN
(SELECT EmpID, MIN(VisitDate) AS WeekStartDate, MAX(VisitDate) AS WeekEndDate
FROM dbo.TC72_EmployeeVisits
GROUP BY EmpID) AS C INNER JOIN
(SELECT EarningCode, SUM(DATEDIFF(Minute, StartTime, EndTime) / 60.0) AS Hours, EmpID
FROM dbo.TC72_EmployeeVisits AS TC72_EmployeeVisits_1
GROUP BY EarningCode, EmpID) AS D ON C.EmpID = D.EmpID ON dbo.TC72_PayrollWeek.EmpID = C.EmpID AND
dbo.TC72_PayrollWeek.WeekStart = C.WeekStartDate AND dbo.TC72_PayrollWeek.WeekEnd = C.WeekEndDate
Post #1227072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse