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

Advertisements