# 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),` – JimmyB Jul 22 '16 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. – dnoeth Jul 22 '16 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 – Dawood Awan Jul 22 '16 at 6:34
• See update in answer. +1 for adding sample data. – Lennart Jul 22 '16 at 6:38
• Thanks - Will the window function be more efficient performance wise than this? – Dawood Awan Jul 22 '16 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. – Lennart Jul 22 '16 at 7:01