Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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