2

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

@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

2
  • 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
1

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

4
  • 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 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? 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

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .