Arun K

Arun K

  • 1.6k
  • 85
  • 787

Day Calculation in SQL

Jun 29 2018 9:16 AM
ADMID WardID FromDate ToDate
1280 6 2018-04-16 13:54:29 2018-04-19 07:19:48
1280 5 2018-04-19 07:19:48 2018-04-19 23:20:45
1280 6 2018-04-19 23:20:45 2018-04-21 23:20:45
I need below sql output for the table above :
 
Sno ADMID WardID FromDate ToDate Duration Day
1 1280 6 2018-04-16 13:54:00 2018-04-17 13:54:00 1 days, 0 hours, 0 minutes and 0 seconds 1
2 1280 6 2018-04-17 13:54:00 2018-04-18 13:54:00 1 days, 0 hours, 0 minutes and 0 seconds 2
3 1280 6 2018-04-18 13:54:00 2018-04-19 07:19:00 0 days, 17 hours, 25 minutes and 0 seconds 3
4 1280 5 2018-04-19 07:19:00 2018-04-19 13:54:00 0 days, 6 hours, 35 minutes and 0 seconds 3
5 1280 5 2018-04-19 13:54:00 2018-04-19 23:20:00 0 days, 9 hours, 26 minutes and 0 seconds 4
6 1280 6 2018-04-19 23:20:00 2018-04-20 13:54:00 0 days, 14 hours, 34 minutes and 0 seconds 4
7 1280 6 2018-04-20 13:54:00 2018-04-21 13:54:00 1 days, 0 hours, 0 minutes and 0 seconds 5
8 1280 6 2018-04-21 13:54:00 2018-04-21 23:20:00 0 days, 9 hours, 26 minutes and 0 seconds 6
 
Please suggest me on this, i tried a lot but cann't get a proper query.

Answers (7)