user2233326 user2233326 - 6 months ago 49
SQL Question

SQL help in calculating regular and overtime hours for project billing

My table looks like this

First Name Last Name Date Day Hours Project Name
Joe Smith 6/22/15 Mon 8 Project A
Joe Smith 6/23/15 Tue 11.75 Project A
Joe Smith 6/24/15 Wed 10.25 Project A
Joe Smith 6/25/15 Thu 14.25 Project B
Joe Smith 6/26/15 Fri 11.5 Project B
Joe Smith 6/27/15 Sat 9.5 Project B
Joe Smith 6/28/15 Sun 8.53 Project B


I need to calculate regular time and overtime hours from this for each employee and each project worked.

Regular time :All week day hours for the week less than or equal to 40 hours
Overtime: Week-day hours over 40, any weekend hours

My result should be like this

Firstname Lastname Date Day Regular OverTime Project Name
Joe Smith 6/22/15 Mon 8.00 Project A
Joe Smith 6/23/15 Tue 11.75 Project A
Joe Smith 6/24/15 Wed 10.25 Project A
Joe Smith 6/25/15 Thu 10.00 4.25 Project B
Joe Smith 6/26/15 Fri 0.00 11.50 Project B
Joe Smith 6/27/15 Sat 0.00 9.50 Project B
Joe Smith 6/28/15 Sun 0.00 8.53 Project B
--------------------------------------------------------------------
Total 40.00 33.78


I am unable to achieve this result.

Answer

For a SQL server, below code will work. Please note that I am using COALESCE keyword to encapsulate the business logic for OverTime and Regular hours calculation. For these calculations I needed the cumulative hours in the week calculated using inner query, as well I needed to identify point of inflexion when the hours went past 40.

SELECT 
Q.FirstName, 
Q.LastName,
Q.[Date],
Q.Day,
COALESCE(
      CASE 
         WHEN Q.[Day] like 's%' THEN 0 ELSE NULL END,
       CASE 
         WHEN Q.Cumulative>40 AND Q.cumulative-Q.hours>40 
         THEN 0 ELSE NULL END,
       CASE 
          WHEN Q.Cumulative>40 AND Q.cumulative-Q.hours<40 
          THEN 40-Q.cumulative+ Q.hours ELSE Q.hours END
        ) AS Regular,
COALESCE(
      CASE 
         WHEN Q.[Day] like 's%' THEN Q.Hours ELSE NULL END,
       CASE 
         WHEN Q.Cumulative>40 AND Q.cumulative-Q.hours>40
         THEN Q.hours ELSE NULL END,
       CASE 
          WHEN Q.Cumulative>40 AND Q.cumulative-Q.hours<40 
          THEN Q.Cumulative-40 ELSE Q.hours END
        ) AS OverTime,
Q.[Project Name]
FROM
(SELECT 
B.FirstName,
B.LastName,
B.[Date],
B.Day,
B.hours,
B.[Project Name],
(SELECT SUM(A.hours) FROM  [dbo].[temp] A WHERE A.[Date]<=B.[Date]) cumulative
FROM [dbo].[temp] B )Q

Sql fiddle link http://sqlfiddle.com/#!6/7eea7/16