InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:47:14 GMT20http://www.sqlservercentral.com/Forums/Topic1226609-356-1.aspxSELECT 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.HoursFROM 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.WeekEndDateTue, 27 Dec 2011 12:55:36 GMTmahesh_patel2004ahttp://www.sqlservercentral.com/Forums/Topic1226609-356-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL+Challenges/87148/">TSQL Challenge 72 - Calculate the Payroll Hours of Employees</A>[/B]Sun, 25 Dec 2011 23:33:10 GMTjacob sebastian