T-SQL how to calculate datediff from previous or next row on log?

Question

I use MS SSMS 2008 R2 to extract data from our company management software, which registers our employee actions and schedules. The table has and ID field, which is unique to each entry. job is the activity the user is performing. user is the user ID. start_time and duration are exactly that. Then there is a "type" where 0 is login (the user logs into the job) and 1 is available time (while performing a job the user may be available or not). "reason" is the reason why the user has become unavailable (break, coffee, lunch, training, etc). Type 0 entries have no reason so reason is always null.

I need to extract the unavailable times by reason and all I'm being able to achieve is to do a DATEADD of duration to start_time in order to get end_time and then use Excel to manually calculate the times for each row.

The SQL table looks like this:

    id      job user    start_time          duration    type    reason
    4436812 3   758     05-06-2015 09:00    125670      0       NULL
    4436814 3   758     05-06-2015 09:00    6970        1       1004
    4436944 3   758     05-06-2015 09:14    39280       1       1004
    4437119 3   758     05-06-2015 10:20    0           1       1002
    4437172 3   758     05-06-2015 10:35    18470       1       1004
    4437312 3   758     05-06-2015 11:09    3960        1       1004
    4437350 3   758     05-06-2015 11:16    0           1       1006
    4437360 3   758     05-06-2015 11:19    30080       1       1004
    4437638 3   758     05-06-2015 12:13    6730        1       1004
    4437695 3   758     05-06-2015 12:24    0           1       1007
    4438227 3   758     05-06-2015 13:43    NULL        0       NULL
    4438228 3   758     05-06-2015 13:43    NULL        1       NULL

(job = 3 and user = 758)

This is the query I made:

    select CONVERT(date,start_time) Data, a.job, a.user, convert(varchar(15),convert(datetime,a.start_time),108) StartTime, a.duration duracao,
     convert(varchar(15),convert(datetime,DATEADD(second,a.duration/10,a.start_time)),108) EndTime, a.type, a.reason
    from schedule_log a
    where a.job = 3
    and a.user = 758
    and CONVERT(date,start_time) = '20150605'
    order by a.start_time, a.type

Which translates to:

Date        job user    LogTime     Avail       NotAvail
2015-06-05  3   758     04:44:01    04:10:23    00:33:38

So, for each reason, I have to do a DATEDIFF from end time (start+duration) to either the next type 1 start_time or the previous type 0 end time, which ever happened first (the user may become unavailable and then logoff).

How do I do this?

ps: duration is in tenths of second.

Answer 1

Ok, here is my updated suggestion. It is broken into three steps for clarity, but the temp tables are unnecessary - they could become subqueries.

Step 1: Calculate the end time for each period of activity, excluding logins.
Step 2: Join each row to the row that occurred immediately after it, to get the unavailable time following each reason. Note: some of your timestamps do not line up properly, possibly as a result of storing duration in seconds but timestamps only to the minute.
Step 3: Total the unavailable time, and subtract from the duration of the login to get the available time.
Step 4: Total the unavailable time by reason.

SELECT *
    ,dateadd(s, duration / 10, start_time) AS Endtime
    ,row_number() OVER (
        PARTITION BY job ,[user] ORDER BY start_time, [type]
        ) AS RN
INTO #temp2
FROM MyTable
WHERE [type] = 1

SELECT a.[user]
    ,a.job
    ,a.reason
    ,a.start_time
    ,a.type
    ,a.duration / 10 AS AvailableSeconds
    ,datediff(s, a.Endtime, b.start_time) AS UnavailableSeconds
INTO #temp3
FROM #temp2 a
LEFT JOIN #temp2 b
    ON a.[user] = b.[user]
        AND a.job = b.job
        AND a.RN = b.RN - 1

SELECT cast(a.start_time AS DATE) AS [Date]
    ,a.job
    ,a.[user]
    ,b.duration / 10 AS LogTime
    ,b.duration / 10 - sum(UnavailableSeconds) AS Avail
    ,sum(UnavailableSeconds) AS NotAvail
FROM #temp3 a
LEFT JOIN MyTable b
    ON a.job = b.job
        AND a.[user] = b.[user]
        AND b.[type] = 0
    AND b.duration IS NOT NULL
GROUP BY cast(a.start_time AS DATE)
    ,a.job
    ,a.[user]
    ,b.duration

SELECT cast(a.start_time AS DATE) AS [Date]
    ,a.job
    ,a.[user]
    ,a.reason
    ,sum(UnavailableSeconds) AS NotAvail
FROM #temp3 a
where reason is not null
GROUP BY cast(a.start_time AS DATE)
    ,a.job
    ,a.[user]
    ,a.reason