SQL Puzzle | Working Hours Puzzle

Write a T-SQL that gives us hours worked from 2016-09-01 07:00:00 to 2016-09-02 06:59:99.
by EmployeeType

For more please check out the sample input and the expected output.

Sample Inputs

EmployeeName EmployeeType StartDateTime EndDateTime
Pawan K N 2016-09-01 06:00:00.000 2016-09-01 14:00:00.000
Ramesh K N 2016-09-01 13:00:00.000 2016-09-02 09:00:00.000
Sharlee D D 2016-09-01 09:00:00.000 2016-09-01 13:00:00.000
Mizan D D 2016-09-01 20:00:00.000 2016-09-02 08:00:00.000

Expected Output

EmployeeType WorkDate Hours
N 2016-08-31 1
N 2016-09-01 25
N 2016-09-02 2
D 2016-09-01 15
D 2016-09-02 1

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.

Script | use below script to create table and insert some sample data

```
--

CREATE TABLE Employees1
(
EmployeeName         VARCHAR(100)
,EmployeeType        VARCHAR(100)
,StartDateTime    DATETIME
,EndDateTime DATETIME
)
GO

INSERT INTO Employees1 VALUES
('Pawan K'            ,          'N'                      ,'2016-09-01 06:00:00'    , '2016-09-01 14:00:00' ),
('Ramesh K'             ,             'N'                 ,     '2016-09-01 13:00:00' ,    '2016-09-02 09:00:00' ),
('Sharlee D'              ,         'D'                    , '2016-09-01 09:00:00'     ,'2016-09-01 13:00:00' ),
('Mizan D'                ,           'D'                  ,   '2016-09-01 20:00:00'    , '2016-09-02 08:00:00' )
GO

SELECT * FROM Employees1
GO

--

```

SOLUTION – 1

 ``` -- DECLARE @StartDate AS DATETIME = '2016-01-01' ;WITH SingleDigits(Number) AS ( SELECT Number FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(Number) ) ,Series AS ( SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number from SingleDigits as d1, SingleDigits as d2, SingleDigits as d3, SingleDigits as d4 ) ,CTE0 AS ( SELECT *, DATEADD(d,Number,@StartDate) st FROM Series ) ,CTE1 AS ( SELECT * , CASE WHEN StartDateTime > st then CAST(st AS DATE) else CAST(DATEADD(d,-1,CAST(StartDateTime AS DATE)) AS DATE) END starts , CASE WHEN EndDateTime > et then CAST(et AS DATE) else CAST(DATEADD(d,-1,CAST(EndDateTime AS DATE)) AS DATE) END Ends FROM ( SELECT EmployeeType,StartDateTime , EndDateTime , DATEADD(day, DATEDIFF(day, 0, CAST(StartDateTime AS DATE)), '07:00:00') st , DATEADD(day, DATEDIFF(day, 0, CAST(EndDateTime AS DATE)), '07:00:00') et FROM Employees1 )k ) SELECT EmployeeType, CAST(b.st AS DATE) WorkDate ,SUM(CASE WHEN starts = b.st THEN CASE WHEN a.st > StartDateTime THEN DATEDIFF(hh,StartDateTime,a.st) ELSE CASE WHEN starts <> Ends THEN DATEDIFF(hh,StartDateTime,CASE WHEN a.et < EndDateTime THEN a.et ELSE EndDateTime END) ELSE DATEDIFF(hh,StartDateTime,EndDateTime) END END ELSE CASE WHEN a.et < EndDateTime THEN DATEDIFF(hh,a.et,EndDateTime) ELSE DATEDIFF(hh,a.StartDateTime,a.et)END END) Hours FROM CTE1 a INNER JOIN CTE0 b ON b.st BETWEEN a.starts AND a.Ends GROUP BY EmployeeType,Number,CAST(b.st AS DATE) ORDER BY EmployeeType DESC,Number -- ```

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com