CODEWITHSUNDEEP
sqlsql-servert-sql
user2233326
user2233326

Reputation: 33

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.

Upvotes: 1

Views: 1283

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

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 0 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/#!18/7eea7/2

Upvotes: 2

Related Questions