TSQL Challenge 72 - Calculate the Payroll Hours of Employees

• jacob sebastian

  SSChampion

  Points: 11822

  More actions

  December 25, 2011 at 11:33 pm

  #144682

  Comments posted to this topic are about the item TSQL Challenge 72 - Calculate the Payroll Hours of Employees

  .

• mahesh_patel2004a

  SSC Journeyman

  Points: 81

  More actions

  December 27, 2011 at 12:55 pm

  #1425840

  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