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

  • 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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.