What's the best way to find the difference in dates?
I have a table for statements in which I want to calculate the time difference between two states (10-20) for the whole day. 
Here I want the time difference between "ActivityStatus" 10 and 20. we have a total of 3 groups of 10-20 statuses. there is no status 20 for the last status, in which case it will take the last oa_createdDate (i.e. oa_id 230141).
My expected output for this statement is date diff between cl_id 230096 and 230102, date diff between cl_id 230103 and 230107, date diff between cl_id 230109 and cl_id 230141. Once I get this difference, I want to sum all date values to calculate the occupied time for this operator.
Thanks in advance.
source to share
There's an easy way to do what I assume you want to do with the external one, for example:
select tmin.*, t.oa_CreateDate oa_CreateDate_20
, datediff(minute, tmin.oa_CreateDate, t.oa_CreateDate) DiffInMinutes
from testtable t
cross apply
(select top 1 *
from testtable tmin
where tmin.oa_CreateDate < t.oa_CreateDate and tmin.oa_OperatorId = t.oa_OperatorId
order by tmin.oa_CreateDate asc) tmin
where t.ActivityStatus = 20
and t.oa_CreateDate < (select min(oa_CreateDate) from testtable where ActivityStatus = 10 and oa_OperatorId = 1960)
and t.oa_OperatorId = 1960
union all
select t.*
, coalesce(a.oa_CreateDate,ma.MaxDate) oa_CreateDate_20
, datediff(minute, t.oa_CreateDate, coalesce(a.oa_CreateDate,ma.MaxDate)) DiffInMinutes
from testtable t
outer apply
(select top 1 a.oa_CreateDate
from testtable a
where a.oa_OperatorId = t.oa_OperatorId and a.ActivityStatus = 20
and t.oa_CreateDate < a.oa_CreateDate order by a.oa_CreateDate asc) a
outer apply
(select max(a2.oa_CreateDate) maxDate
from testtable a2
where a2.oa_OperatorId = t.oa_OperatorId
and t.oa_CreateDate < a2.oa_CreateDate) ma
where oa_OperatorId = 1960
and ActivityStatus = 10
order by oa_CreateDate asc, oa_CreateDate_20 asc
You can see the script here .
But of course you have to give us the format / clarity for a dated comparison. And that assumes that you will always have status 10 and 20 and that their timestamps never overlap.
EDIT: Updated answer based on your comment, check out new script and fiddle. The fill script now finds all status 10-20 points, and in case there is no status 20 after the last 10, the last existing timestamp after that state 10 will be used instead.
EDIT 2: Updated with your comment below. But at this point, the script is getting pretty ugly. Unfortunately, I don't have time to clean it up, so I ask that next time you post a question please make it as clear and clean as possible, as there are 3 different ride options when trying to answer the question. :)
This should work one way or another, a new section before UNION ALL in the script will only return results if there is a status of 20 with no preceding 10. Otherwise it will return nothing and will move to the main script part as before. Fiddle has been updated as well.
source to share
This is one way to do it.
The first OUTER APPLY
will fetch the next line with status 20 that is after the currently created datetime.
The second one OUTER APPLY
will retrieve the next line after the currently created datetime where there is no status 20.
SELECT
o.*
, COALESCE(NextStatus.oa_CreateDate, NextStatusIsNull.oa_CreateDate) AS NextTimestamp
, COALESCE(NextStatus.ActivityStatus, NextStatusIsNull.ActivityStatus) AS NextStatus
, DATEDIFF(MINUTE, o.oa_CreateDate,
COALESCE(NextStatus.oa_CreateDate, NextStatusIsNull.oa_CreateDate))
AS DifferenceInMinutes
FROM
operators AS o
OUTER APPLY
(
SELECT TOP 1
oa_CreateDate
, ActivityStatus
FROM
operators
WHERE
ActivityStatus = 20
AND oa_CreateDate > o.oa_CreateDate
ORDER BY
oa_CreateDate
) AS NextStatus
OUTER APPLY
(
SELECT TOP 1
oa_CreateDate
, ActivityStatus
FROM
operators
WHERE
NextStatus.oa_CreateDate IS NULL
AND oa_CreateDate > o.oa_CreateDate
ORDER BY
oa_CreateDate
) AS NextStatusIsNull
WHERE
ActivityStatus = 10
source to share
I used several different test data because you were using an image that I was unable to cut and paste from. It's easy to convert to a table:
Note that this should also work with missing start and end dates,
Also note that this was done without any joins to optimize performance.
Test table and data:
DECLARE @t table(ActivityStatus int, oa_createdate datetime, oa_operatorid int)
INSERT @t values
(30, '2015-07-23 08:20', 1960),(20, '2015-07-23 08:24', 1960),
(10, '2015-07-23 08:30', 1960),(20, '2015-07-23 08:40', 1960),
(10, '2015-07-23 08:50', 1960),(50, '2015-07-23 09:40', 1960)
Query:
;WITH cte as
(
SELECT
ActivityStatus,
oa_createdate,
oa_operatorid
FROM @t
WHERE ActivityStatus in (10,20)
UNION ALL
SELECT 20, max(oa_createdate), oa_operatorid
FROM @t
GROUP BY oa_operatorid
HAVING
max(case when ActivityStatus = 20 then oa_createdate end) <
max(case when ActivityStatus = 10 then oa_createdate end)
UNION ALL
SELECT 10, min(oa_createdate), oa_operatorid
FROM @t
GROUP BY oa_operatorid
HAVING
min(case when ActivityStatus = 20 then oa_createdate end) <
min(case when ActivityStatus = 10 then oa_createdate else '2999-01-01' end)
)
SELECT
cast(cast(sum(case when activitystatus = 10 then -1 else 1 end
* cast(oa_createdate as float)) as datetime) as time(0)) as difference_in_time,
oa_operatorid
FROM cte
GROUP BY oa_operatorid
Result:
difference_in_time oa_operatorid
01:04:00 1960
source to share
Data
create table #Table2 (oa_id int, oa_OperatorId int, ActivityStatus int, oa_CreateDate datetime)
insert into #Table2
values (1, 1960,10,'2015-08-10 10:55:12.317')
,(2, 1960,20,'2015-08-10 11:55:12.317')
,(3, 1960,30,'2015-08-10 14:55:12.317')
,(4, 1960,50,'2015-08-10 14:58:12.317')
,(5, 1960,10,'2015-08-10 15:55:12.317')
,(6, 1960,20,'2015-08-10 16:20:12.317')
,(7, 1960,10,'2015-08-10 16:30:12.317')
,(8, 1960,50,'2015-08-10 17:20:12.317')
Set the target table with the rows that interest us
select oa_id,
oa_operatorid,
ActivityStatus,
oa_createDate,
rn = row_number() over (order by oa_id desc)
into #Table
from #Table2
where ActivityStatus in (10, 20)
insert #Table
select top 1
oa_id,
oa_operatorid,
ActivityStatus,
oa_createDate,
0
from #Table2
order by oa_id desc
select * into #Table10 from #Table where ActivityStatus = 10
select * into #Table20 from #Table where ActivityStatus = 20
union
select * from #Table where rn = 0 /*add the last record*/
except
select * from #Table where rn = (select max(rn) from #Table) /**discard the first "20" record*/
/*free time info*/
select datediff(second, t10.oa_createDate, t20.oa_createDate) secondssincelast10,
t20.*
from #Table10 t10 join #Table20 t20
on t10.rn = t20.rn + 1
and t10.oa_OperatorId = t20.oa_OperatorId
/*Summarized info per operator*/
select sum(datediff(second, t10.oa_createDate, t20.oa_createDate)) totalbusytime,
t20.oa_OperatorId
from #Table10 t10 join #Table20 t20
on t10.rn = t20.rn + 1
and t10.oa_OperatorId = t20.oa_OperatorId
group by t20.oa_OperatorId
source to share