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

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 |             |
|             |                         |                         |             |
``````

@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

• You don't even need to use a subquery: `DATEDIFF(s, MIN(CASE WHEN EventCode = 1 THEN LogDateTime END), MAX(CASE WHEN EventCode = 2 THEN LogDateTime END),` Commented Jul 22, 2016 at 12:56
• @JimmyB: Of course, but when the start and end need to be included, too, the MIN/Max must be repeated. Now it's a bit more readable and as long as there'n no Group By the optimizer should remove it anyway and you will get the same plan for both variations. Commented Jul 22, 2016 at 13:31

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

• Hi, The first query shows the Start and End in separate rows - Is there a way to show them in a single row? Like the example I added to the question - Note: You can test the Query on the data.stackexhange link I posted Commented Jul 22, 2016 at 6:34
• See update in answer. +1 for adding sample data. Commented Jul 22, 2016 at 6:38
• Thanks - Will the window function be more efficient performance wise than this? Commented Jul 22, 2016 at 6:52
• Window functions are usually more efficient, but one have to test from case to case. Nevertheless it is a good technique to know, so if you haven't tried them I recommend doing so. If you get stuck post back and I or someone else can have a look. Commented Jul 22, 2016 at 7:01