SQL Puzzle | The Date Puzzle – 1 | Improving my SQL BI Skills

Tags

SQL Puzzle | The Date Puzzle – 1

Here you need to write a query that get the number of hours each employee worked on 09/01/2014 between 10am to 1pm.

For details please check out the sample input and the expected output below.

Sample Inputs

DECLARE @Date AS DATE = ‘2014-09-01′
DECLARE @StartTime AS TIME = ’10:00′
DECLARE @EndTime AS TIME = ’13:00’

EmployeeID	StartDateTime	EndDateTime
1	2014-09-01 08:00:00.000	2014-09-01 16:45:00.000
2	2014-09-01 15:15:00.000	2014-09-02 18:00:00.000
3	2014-09-01 11:00:00.000	2014-09-01 18:45:00.000
4	2014-09-01 10:00:00.000	2014-09-01 18:45:00.000
5	2014-09-01 09:00:00.000	2014-09-01 18:45:00.000
6	2014-09-01 10:00:00.000	2014-09-01 18:45:00.000

Expected Output

Output
Emp 1 has 3 Hours
Emp 2 has 0 Hours
Emp 3 has 2 Hours
Emp 4 has 3 Hours
Emp 5 has 3 Hours
Emp 6 has 3 Hours

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 testDt
(
        EmployeeID INT              
       ,StartDateTime DATETIME                
       ,EndDateTime DATETIME
)
GO
 
INSERT INTO testDt VALUES
(1   ,'09/01/2014 08:00:00'    ,'09/01/2014 16:45:00'),
(2   ,'09/01/2014 15:15:00'    ,'09/02/2014 18:00:00'),
(3   ,'09/01/2014 11:00:00'    ,'09/01/2014 18:45:00'),
(4   ,'09/01/2014 10:00:00'    ,'09/01/2014 18:45:00'),
(5   ,'09/01/2014 09:00:00'    ,'09/01/2014 18:45:00'),
(6   ,'09/01/2014 10:00:00'    ,'09/01/2014 18:45:00')
GO
 
select * from testDt

DECLARE @Date AS DATE = '2014-09-01'
DECLARE @StartTime AS TIME = '10:00'
DECLARE @EndTime AS TIME = '13:00'

--

—

SOLUTION – 1

—

Solution will be uploaded tomorrow 🙂

—

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

2 thoughts on “SQL Puzzle | The Date Puzzle – 1”

Praveen Jangili said:

September 25, 2017 at 6:29 PM

DECLARE @Date AS DATE = ‘2014-09-01′
DECLARE @StartTime AS TIME = ’10:00′
DECLARE @EndTime AS TIME = ’13:00’

SELECT case when @Date = CONVERT(DAte,StartDateTime) then
CAse WHEn @StartTime >= CONVERT(Time,StartDateTime) then
Case When @EndTime <= CONVERT(Time,ENdDateTime)
Then '3' else DateDiff(hour,@StartTime,CONVERT(Time,ENdDateTime))
END
ELse CAse WHEn @StartTime CONVERT(Time,StartDateTime) then Case When @EndTime <= CONVERT(Time,ENdDateTime)
Then DateDiff(hour,CONVERT(Time,StartDateTime),@EndTime) else DateDiff(hour,CONVERT(Time,StartDateTime),CONVERT(Time,ENdDateTime))
ENd Else '0'
end
end
end

FROM testDt

LikeLike

Gregor Kralj said:

May 27, 2019 at 1:16 PM

DECLARE @StartDateTime AS DATETIME = CAST(@Date as DATETIME) + CAST(@StartTime as DATETIME)
DECLARE @EndDateTime AS DATETIME = CAST(@Date as DATETIME) + CAST(@EndTime as DATETIME)

;with cte as (
select
EmployeeID,
(
case

when
@StartDateTime between StartDateTime and EndDateTime
AND
@EndDateTime between StartDateTime and EndDateTime
THEN DATEDIFF(hour, @StartDateTime, @EndDateTime)

when
@StartDateTime = StartDateTime
AND
@EndDateTime <= EndDateTime
THEN DATEDIFF(hour, StartDateTime, @EndDateTime)

when
@StartDateTime = StartDateTime
AND
@EndDateTime > EndDateTime
THEN DATEDIFF(hour, StartDateTime, EndDateTime)

else 0
end
) as HoursWorked
from testDt
)
select CONCAT(‘Emp ‘,EmployeeID,’ has ‘, HoursWorked ,’ Hours’) from cte

LikeLiked by 1 person

Share this

Related

2 thoughts on “SQL Puzzle | The Date Puzzle – 1”

Leave a comment Cancel reply