Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I couldn't get @Alex W's queries to work. It is not standard SQL, so it required a lot of rewrite to be compatible with SQL Server (which I can test). But it did give me some inspiration, which I have expanded upon.</p> <hr> <p>Find all start-points of every period of uninterrupted waiting:</p> <pre><code>SELECT DISTINCT t1.ID, t1.d1 AS date, -DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d1) AS n FROM Orders t1 LEFT JOIN Orders t2 -- Join for any events occurring while this ON t2.ID = t1.ID -- is starting. If this is a start point, AND t2.d1 &lt;&gt; t1.d1 -- it won't match anything, which is what AND t1.d1 BETWEEN t2.d1 AND t2.d2 -- we want. GROUP BY t1.ID, t1.d1, t1.d2 HAVING COUNT(t2.ID) = 0 </code></pre> <p>And the equivalent for end-points:</p> <pre><code>SELECT DISTINCT t1.ID, t1.d2 AS date, DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d2) AS n FROM Orders t1 LEFT JOIN Orders t2 ON t2.ID = t1.ID AND t2.d2 &lt;&gt; t1.d2 AND t1.d2 BETWEEN t2.d1 AND t2.d2 GROUP BY t1.ID, t1.d1, t1.d2 HAVING COUNT(t2.ID) = 0 </code></pre> <p><code>n</code> is the number of days since some common point in time. Start-points have a negative value, and end-points have a positive value. This is so that we can just add them up to get the number of days in between.</p> <pre><code>span = end - start span = end + (-start) span1 + span2 = end1 + (-start1) + end2 + (-start2) </code></pre> <p>Finally, we just need to add things up:</p> <pre><code>SELECT ID, SUM(n) AS hold_days FROM ( SELECT DISTINCT t1.id, t1.d1 AS date, -DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d1) AS n FROM Orders t1 LEFT JOIN Orders t2 ON t2.ID = t1.ID AND t2.d1 &lt;&gt; t1.d1 AND t1.d1 BETWEEN t2.d1 AND t2.d2 GROUP BY t1.ID, t1.d1, t1.d2 HAVING COUNT(t2.ID) = 0 UNION ALL SELECT DISTINCT t1.id, t1.d2 AS date, DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d2) AS n FROM Orders t1 LEFT JOIN Orders t2 ON t2.ID = t1.ID AND t2.d2 &lt;&gt; t1.d2 AND t1.d2 BETWEEN t2.d1 AND t2.d2 GROUP BY t1.ID, t1.d1, t1.d2 HAVING COUNT(t2.ID) = 0 ORDER BY ID, date ) s GROUP BY ID; </code></pre> <p>Input table (Orders):</p> <pre><code>ID d1 d2 1 2011-08-01 2011-08-08 1 2011-08-02 2011-08-06 1 2011-08-03 2011-08-10 1 2011-08-12 2011-08-14 2 2011-08-01 2011-08-03 2 2011-08-02 2011-08-06 2 2011-08-05 2011-08-09 </code></pre> <p>Output:</p> <pre><code>ID hold_days 1 11 2 8 </code></pre> <hr> <p>Alternatively, you can do this with a stored procedure.</p> <pre><code>CREATE PROCEDURE CalculateHoldTimes @ID int = 0 AS BEGIN DECLARE Events CURSOR FOR SELECT * FROM ( SELECT d1 AS date, 1 AS diff FROM Orders WHERE ID = @ID UNION ALL SELECT d2 AS date, -1 AS diff FROM Orders WHERE ID = @ID ) s ORDER BY date; DECLARE @Events_date date, @Events_diff int, @Period_start date, @Period_accum int, @Total_start date, @Total_count int; OPEN Events; FETCH NEXT FROM Events INTO @Events_date, @Events_diff; SET @Period_start = @Events_date; SET @Period_accum = 0; SET @Total_start = @Events_date; SET @Total_count = 0; WHILE @@FETCH_STATUS = 0 BEGIN SET @Period_accum = @Period_accum + @Events_diff; IF @Period_accum = 1 AND @Events_diff = 1 -- Start of period SET @Period_start = @Events_date; ELSE IF @Period_accum = 0 AND @Events_diff = -1 -- End of period SET @Total_count = @Total_count + DATEDIFF(day, @Period_start, @Events_date); FETCH NEXT FROM Events INTO @Events_date, @Events_diff; END; SELECT @Total_start AS d1, @Events_date AS d2, @Total_count AS hold_time; END; </code></pre> <p>Call it with:</p> <pre><code>EXEC CalculateHoldTimes 1; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL to find time elapsed from multiple overlapping intervals
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMarkus Jarderot
    1. USMarkus Jarderot
    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
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you MizardX, this is exactly what we were looking for
      singulars
      1. PO
      1. USjon
    2. COsorry, i don't have enough points to click your answer up
      singulars
      1. PO
      1. USjon
    3. CONow you can. :) Anyway; You can still accept an answer if you think it answers your question. Click on the check-mark below the voting arrows.
      singulars
      1. PO
      1. USMarkus Jarderot
 

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