Day Calculation in SQL

Arun K

8y1.1kSQL

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.

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Arun K8y ago
Thanks Nitin Sontakke for you reply..Finally i got my exact output from one of my friend
Create table table_checkout ( WardStatusID int ,ADMID int ,WardID int ,FromDate datetime ,ToDate datetime )
CREATE TABLE table_checkout1(SNo      INT IDENTITY(1, 1), ADMID    INT, WardID   INT, FromDate DATETIME, ToDate   DATETIME)

insert into table_checkout
Select 1305,1280,6,'2018-04-16 13:54:29','2018-04-19 07:19:48' union all
Select 4308,1280,5,'2018-04-19 07:19:48','2018-04-19 23:20:45'union all
Select 6308,1280,6,'2018-04-19 23:20:45','2018-04-21 23:20:45'
declare @WardStatusID int ,@ADMID int ,@WardID int ,@FromDate datetime ,@ToDate datetime
declare hospital cursor  for
Select  * from table_checkout (nolock)
OPEN hospital
FETCH NEXT FROM hospital INTO @WardStatusID, @ADMID, @WardID,@FromDate,@ToDate
WHILE @@FETCH_STATUS = 0
BEGIN
    declare @fromdate1 datetime ,@MyTime      TIME = '13:54:00.00',
        @MyDate datetime
While @fromdate < @ToDate
begin
if Convert(date,@fromdate) =convert(date,@ToDate) And datediff(hh,@fromdate,@ToDate) > 11
Begin
    Select @MyDate = Convert(date,@fromdate)
    Select @fromdate1 =@fromdate
    Select @fromdate = Case when @ToDate < (CAST(@MyDate AS DATETIME) + CAST(@MyTime AS DATETIME)) then @ToDate  Else (CAST(@MyDate AS DATETIME) + CAST(@MyTime AS DATETIME)) End
End
Else
Begin
    Select @fromdate1 =@fromdate
    Select @fromdate = dateadd(dd,1,@fromdate)
    Select @fromdate = (CAST(Convert(date,@fromdate) AS DATETIME) + CAST(@MyTime AS DATETIME))
End
insert into table_checkout1
Select @ADMID, @WardID,@fromdate1,case when @fromdate>@ToDate then @ToDate  Else @fromdate End
End
FETCH NEXT FROM hospital INTO @WardStatusID, @ADMID, @WardID,@FromDate,@ToDate
End
close hospital
deallocate hospital
select * from table_checkout1
drop table table_checkout
drop table table_checkout1
Nitin Sontakke8y ago
Here is the latest update:
declare @someTable table
(
  [ADMID] integer not null
,[WardID] integer not null
,[FromDate] datetime not null
,[ToDate] datetime not null
)

insert into @someTable (ADMID, WardID, FromDate, ToDate) values
(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')

declare @dates table
(
  [someDate] date
)
insert into @dates values
('2018-04-16 00:00:00.000')
,('2018-04-17 00:00:00.000')
,('2018-04-18 00:00:00.000')
,('2018-04-19 00:00:00.000')
,('2018-04-20 00:00:00.000')
,('2018-04-21 00:00:00.000')

select st.[ADMID], st.[WardID] --, st.[FromDate], st.[ToDate]
  ,case when convert(date, st.[FromDate]) = d.[someDate] then st.[FromDate] else convert(datetime, convert(varchar, d.[someDate], 112) + ' ' + convert(varchar, st.[FromDate], 114)) end [FromDate]
  ,case when convert(date, st.[ToDate]) = d.[someDate] then st.[ToDate] else dateadd(d, 1, convert(datetime, convert(varchar, d.[someDate], 112) + ' ' + convert(varchar, st.[FromDate], 114))) end [ToDate]
--  ,*
from @someTable st
left outer join @dates d on d.[someDate] between st.[FromDate] - 1 and st.[ToDate]
You can now add the function given by Bryian to get the other column.
Nitin Sontakke8y ago
It is not quite clear to me why you are carrying on with 13:54:00 on row 4. Once that person has been moved to ward 5 at 07:19 then you should be continueing with 07:19 until ward is changed.

Can you explain, please. I am struggling to achieve get the original value from 1st row and it doesn't make sense to me either.
Nitin Sontakke8y ago
As already stated in my post, my solution is half-baked. Let me try to complete and post it back.
Arun K8y ago
Thanks Bryian Tan,Nitin Sontakke for your replies.I checked that but its not what i need. The concept is like a patient staying in a hospital for some days and in different rooms. we have to give breakup of his stay by 24-hrs format based on the room he stays. My output will show you the exact scenario.
Bryian Tan8y ago
You can create a function like
CREATE FUNCTION dbo.DisplayDayCalculation
(
    -- Add the parameters for the function here
    @fromDate   DATETIME,
    @toDate     DATETIME
)
RETURNS VARCHAR(150)
AS
BEGIN

      DECLARE @returnDateString VARCHAR(150)
       SET @returnDateString =
       CONVERT(VARCHAR(10), DateDiff(dd, @fromDate, @toDate)) + ' days, '
     + CONVERT(VARCHAR(10),DateDiff(hh, @fromDate, @toDate) % 24) + ' hours, '
     + CONVERT(VARCHAR(10),DateDiff(mi, @fromDate, @toDate) % 60) + ' minutes, '
     + CONVERT(VARCHAR(10),DateDiff(ss, @fromDate, @toDate) % 60) + ' seconds '

    RETURN @returnDateString

END
GO
Then call the function from the query, I'm using the temp table from @Nitin
DECLARE @someTable TABLE
(
    [ADMID] integer not null
    ,[WardID] integer not null
    ,[FromDate] datetime not null
    ,[ToDate] datetime not null
)

insert into @someTable (ADMID, WardID, FromDate, ToDate) values
    (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')

SELECT *, dbo.DisplayDayCalculation(FromDate, ToDate) 'Duration' FROM @someTable
Output
ADMID   WardID  FromDate    ToDate  Duration
1280    6   2018-04-16 13:54:29.000 2018-04-19 07:19:48.000 3 days, 18 hours, 25 minutes, 19 seconds
1280    5   2018-04-19 07:19:48.000 2018-04-19 23:20:45.000 0 days, 16 hours, 1 minutes, 57 seconds
1280    6   2018-04-19 23:20:45.000 2018-04-21 23:20:45.000 2 days, 0 hours, 0 minutes, 0 seconds
Nitin Sontakke8y ago
I am posting whatever I did so far. Will pick-up after some time. But this does looks promising and after a bit of tweeking an expected result can be achieved.
declare @someTable table
(
  [ADMID] integer not null
,[WardID] integer not null
,[FromDate] datetime not null
,[ToDate] datetime not null
)

insert into @someTable (ADMID, WardID, FromDate, ToDate) values
(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')

declare @dates table
(
  [someDate] date
)
insert into @dates values
('2018-04-16 00:00:00.000')
,('2018-04-17 00:00:00.000')
,('2018-04-18 00:00:00.000')
,('2018-04-19 00:00:00.000')
,('2018-04-20 00:00:00.000')
,('2018-04-21 00:00:00.000')

select st.[ADMID], st.[WardID] --, st.[FromDate], st.[ToDate]
  ,case when convert(date, st.[FromDate]) = d.[someDate] then st.[FromDate] else d.[someDate] end [FromDate]
  ,case when convert(date, st.[ToDate]) = d.[someDate] then st.[ToDate] else d.[someDate] end [ToDate]
--  ,*
from @someTable st
left outer join @dates d on d.[someDate] between st.[FromDate] - 1 and st.[ToDate]