Databases
StackOverflow2013
Postsdbo
POCompare data within a table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCompare data within a table
    primarykey
    data
    text
    <p>I have a table with data related to emergency incidents. I'm looking to compare how many incidents overlap (by time). I know that database is inherently unordered, but I have the incident start (and end) time, so I can order the records by time.</p> <p>I think the final result would be a result set that had each record and an extra field that indicated whether it overlapped any incidents (0 for none, 1 for one overlap, 2 for two overlaps...). Historically there is a finite number of overlaps (6 is the current max), but it would be possible to have more.</p> <p>Currently, I do this type of analysis in Excel, with a helper column that compares the current record with previous records to determine if there's an overlap (or how many) and it works ok, but it seems like I should be able to accomplish this strictly within SQL.</p> <p>EDIT: Here's a sample from Excel, but the data is held in a single, SQL table. I have admin priveleges for the database, so I can implement just about any solution-except changing the database structure (its a COTS system). I've included the formula for the overlap column. Also, I stopped the sample formula at 2 overlaps, but I'll account for more when I implment the answer.</p> <pre><code>Incident Start End Overlap Overlap Formula 1 2012-01-01 07:00 2012-01-01 08:00 2 2012-01-02 07:00 2012-01-02 08:00 0 =IF(C2&gt;B3,1,0) 3 2012-01-02 07:30 2012-01-02 08:30 1 =IF(C3&gt;B4,IF(C2&gt;B4,2,1),0) 4 2012-01-03 07:00 2012-01-03 08:00 0 =IF(C4&gt;B5,IF(C3&gt;B5,2,1),0) 5 2012-01-04 07:00 2012-01-04 08:00 0 =IF(C5&gt;B6,IF(C4&gt;B6,2,1),0) 6 2012-01-04 07:30 2012-01-04 08:30 1 =IF(C6&gt;B7,IF(C5&gt;B7,2,1),0) 7 2012-01-04 07:45 2012-01-04 08:45 2 =IF(C7&gt;B8,IF(C6&gt;B8,2,1),0) 8 2012-01-04 08:45 2012-01-04 09:45 0 =IF(C8&gt;B9,IF(C7&gt;B9,2,1),0) 9 2012-01-05 07:00 2012-01-05 08:00 0 =IF(C9&gt;B10,IF(C8&gt;B10,2,1),0) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdav
    1. USdav
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCompare data within a table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you show a few rows of sample data and desired results? Then we can construct a useful answer instead of guessing at schema/data and giving you queries with table and column names like foo and bar. Also posting your Excel function might be useful to see exactly what you're doing.
      singulars
      1. POCompare data within a table
      1. USAaron Bertrand
    2. COI'm away from my desk now, but will add some data when I get back. Thx
      singulars
      1. POCompare data within a table
      1. USdav
 

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