1

How to split 1 row of datetime record into multiple rows by splitting duration based on datetime for every hour

Input:

tutor_id | login_time           | logout_time           | duration
12       |'2017-02-20 11:20:20' | '2017-02-20 12:10:00' | 00:50:20
13       |'2017-02-20 12:30:00' | '2017-02-20 14:10:00' | 01:40:00

Want to cut every hour by exact time by dividing duration

Output:

tutor_id | login_time           | logout_time           | duration
12       |'2017-02-20 11:20:20' | '2017-02-20 12:00:00' | 00:40:20
12       |'2017-02-20 12:00:00' | '2017-02-20 12:10:00' | 00:10:00
13       |'2017-02-20 12:30:00' | '2017-02-20 13:00:00' | 00:30:00
13       |'2017-02-20 13:00:00' | '2017-02-20 14:00:00' | 01:00:00
13       |'2017-02-20 14:00:00' | '2017-02-20 14:10:00' | 00:10:00
mck
  • 40,932
  • 13
  • 35
  • 50
learningstudent
  • 577
  • 1
  • 4
  • 14
  • See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Dec 11 '17 at 12:28
  • Refer this https://stackoverflow.com/questions/18821041/oracle-split-a-time-duration-row-by-one-hour-period – Girija Dec 11 '17 at 12:32

1 Answers1

-1

You should try this query

Select top 10  login_time, logout_time,  
CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,login_time,logout_time),'1990-01-01 00:00:00.000') as time) 
from Table

Here i have assumed that login and logout time have same date. it will give difference in minutes.

select * from [login-info]

login-time  logout-time
2017-12-13 11:09:38.557 2017-12-13 11:19:38.557
2017-12-13 11:15:45.490 2017-12-13 11:19:45.490
2017-12-13 11:06:49.700 2017-12-13 11:19:49.700
2017-12-13 11:11:07.580 2017-12-13 11:20:07.580


Select [login-time] ,[logout-time], 
CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,[login-time],[logout-time]),'1990-01-01 00:00:00.000') as time) MINUTEDIFF
from [login-info]

login-time  logout-time MINUTEDIFF
2017-12-13 11:09:38.557 2017-12-13 11:19:38.557 00:10:00.0000000
2017-12-13 11:15:45.490 2017-12-13 11:19:45.490 00:04:00.0000000
2017-12-13 11:06:49.700 2017-12-13 11:19:49.700 00:13:00.0000000
2017-12-13 11:11:07.580 2017-12-13 11:20:07.580 00:09:00.0000000
Krupa
  • 457
  • 3
  • 14