Get first and last record for each date and calculate their difference

Question

I need to calculate the DATEDIFF in seconds between the First record for each date with EventCode = 1 and the last record for each date with EventCode = 2.

I have posted a sample data set for 2 days here: http://data.stackexchange.com/dba/query/515591/calculate-the-difference-between-first-event-and-last-event-for-each-date

So this is how I am getting the First Record for each Date:

Select
    LogDate,
    StartOfDay = MIN(LogDateTime),
    EventCode
FROM (
        Select 
            LogId,
            LogDateTime,
            LogDate = FORMAT(LogDateTime, 'dd-MMM-yyyy'),
            DriverId,
            EventCode
        FROM 
            #MyLogs
        WHERE EventCode = 1
     ) A 
Group By
        LogDate,  EventCode

And this is how I am getting the Last Record for each date where EventCode = 2

Select
    LogDate,
    EndOfDay = MAX(LogDateTime),
    EventCode
FROM (
        Select 
            LogId,
            LogDateTime,
            LogDate = FORMAT(LogDateTime, 'dd-MMM-yyyy'),
            DriverId,
            EventCode
        FROM 
            #MyLogs
        WHERE EventCode = 2
     ) A 
Group By
        LogDate, EventCode

But how do I get the same in single result set? And I need to get the difference in seconds between the First Record for each date and Last Record for each Date.

Example of Result Set:

| Date        | StartOfDay              | EndOfDay                | Start - End |
|-------------|-------------------------|-------------------------|-------------|
| 20-Jul-2016 | 2016-07-20 04:00:32.000 | 2016-07-20 13:05:19.000 |             |
| 21-Jul-2016 | 2016-07-21 04:55:52.000 | 2016-07-21 14:49:25.000 |             |
|             |                         |                         |             |

Answer 1

@Lennart's answer can be simplified to a single Group By:

SELECT LogDate, StartOfDay, EndOfDay, 
    DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds
FROM
  (
    SELECT
        -- instead of FORMAT better use a Standard SQL CAST:
        CAST(LogDateTime AS DATE) AS LogDate,

        MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay,
        MAX(CASE WHEN EventCode = 2 THEN LogDateTime END) AS EndOfDay
    FROM #MyLogs
    WHERE EventCode in (1,2)
    GROUP BY
        CAST(LogDateTime AS DATE)
        -- Pivot in a single stepby removing EventCode 
  ) AS dt;

see query

Answer 2

You can modify your query to:

SELECT LogDate, MAX(StartOfDay), MAX(EndOfDay), DATEDIFF(s, MAX(StartOfDay), MAX(EndOfDay))
FROM (
    Select
        LogDate,
        StartOfDay = MIN(case when EventCode = 1 THEN LogDateTime END),
        EndOfDay = MAX(case when EventCode = 2 THEN LogDateTime END),
        EventCode
    FROM (
        Select 
            LogId,
            LogDateTime,
            LogDate = FORMAT(LogDateTime, 'dd-MMM-yyyy'),
            DriverId,
            EventCode
        FROM 
            #MyLogs
        WHERE EventCode in (1,2)
     ) A 
    Group By
        LogDate,  EventCode
       ) B 
GROUP BY LogDate;   

i.e. get both EventCode 1 and 2 from the derived table A, and use a case expression in the aggregate function. Then we need another nesting to get rid of null's from the case expression.

You might want to have a look at window functions, they can simplify this type of query a lot.

I have added my query to:

http://data.stackexchange.com/dba/query/515887/calculate-the-difference-between-first-event-and-last-event-for-each-date