Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is an approach that uses correlated subqueries:</p> <pre><code>with tt as ( select tt.*, (select top 1 date from TempTable tt2 where tt2.Registration = tt.Registration and tt2.DriverID = tt.DriverID and (tt2.TrailerID = tt.TrailerID or tt2.TrailerID is null and tt.TrailerID is null) and tt2.Date &lt; tt.Date order by date desc ) prevDate from TempTable tt ) select registration, min(date) as startdate, max(date) as enddate, driverid, trailerid from (select tt.*, (select top 1 date from tt tt3 where prevDate is NULL and tt3.Date &lt;= tt.date order by Date desc ) as grp from TempTable tt ) tt group by grp, Registration, DriverID, trailerid; </code></pre> <p>The CTE is doing a <code>lag(date)</code> on the registration, driverid, and trailerid, producing the previous date for a record. This is <code>NULL</code> at the beginning of a sequence of records.</p> <p>The subquery then finds the most recent date on a NULL record on or before a given record. This acts as a grouping variable. Everything in a sequence has the same <code>grp</code> at this point.</p> <p>The final query aggregates this into the format that you want.</p> <p>This is a complicated query. The syntax can be simplified somewhat in SQL Server 2012, using the <code>lag()</code> and cumulative aggregation functions. With those functions, you can follow essentially the same approach.</p> <p>EDIT:</p> <p>Ouch. The above query has a logic error in calculating previous date. The fix requires assuming that the dates are unique in the data.</p> <p>The error above is that it looks for the previous date where the triple of columns matches. Dumb, dumb, dumb. Because there can be a triple that matches but earlier in the data. Instead, it needs to get the previous date and <em>then</em> see if the triple matches.</p> <p>The following implements this with an additional join. It is running <a href="http://www.sqlfiddle.com/#!3/7cd1d/11" rel="nofollow">here</a> on SQL Fiddle.</p> <pre><code>with tt as ( select tt.*, tt3.date as PrevDate from (select tt.*, (select top 1 date from TempTable tt2 where tt2.date &lt; tt.date order by date desc ) prevDate1 from TempTable tt ) tt left outer join TempTable tt3 on tt.prevdate1 = tt3.date and tt3.Registration = tt.Registration and tt3.DriverID = tt.DriverID and (tt3.TrailerID = tt.TrailerID or tt3.TrailerID is null and tt.TrailerID is null) ) select registration, count(*), min(date) as startdate, max(date) as enddate, driverid, trailerid from (select tt.*, (select top 1 date from tt tt3 where prevDate is NULL and tt3.Date &lt;= tt.date order by Date desc ) as grp from TempTable tt ) tt group by grp, Registration, DriverID, trailerid; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to group together sequential, timestamped rows in SQL and return the date range for each group
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGordon Linoff
    1. USGordon Linoff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFirst off, thanks for taking the time to answer my question. As I've been studying the query and attempting to apply it to my production data, I've realised that unfortunately it doesn't work in all cases. It seems that the query relies on each driver/trailer combo being preceded by another combo (even if it's different) that has its prevDate set to NULL (i.e. this is the first occurrence of that combo in the data). Unfortunately, in the production data, this is not the case as all the combinations can occur multiple times in the same data range.
      singulars
      1. PO
      1. USAmr Bekhit
    2. CO@AmrBekhit . . . I do not understand your comment. This query captures multiple sequences of rows, based on the date, that have the same `registration`, `driverId`, and `trailerId`. The same triple can appear multiple times, with different dates, and they will appear correspondingly multiple times in the data. The calculation of `prevDate` determines where each sequence begins.
      singulars
      1. PO
      1. USGordon Linoff
    3. COI modified your query to work on a subset of the data in the table (I've pasted the query here: http://pastebin.com/9E6a3J00). Try recreating the TempTable using the updated code in the question and with the new table, then run the query. You'll notice that for the groups that fall on 2013/08/03, the groupings are incorrect.
      singulars
      1. PO
      1. USAmr Bekhit
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload