Day Calculation in SQL

Question

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.

Nitin Sontakke · Answer

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.

Arun K · Answer

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 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 Sontakke · Answer

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 Sontakke · Answer

As already stated in my post, my solution is half-baked. Let me try to complete and post it back.

Arun K · Answer

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 Tan · Answer

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 Sontakke · Answer

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]

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