Databases
StackOverflow2013
Postsdbo
POGet Time difference between group of records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGet Time difference between group of records
    primarykey
    data
    text
    <p>I have a table with the following structure</p> <pre><code>ID ActivityTime Status 19 2013-08-23 14:52 1 19 2013-08-23 14:50 1 19 2013-08-23 14:45 2 19 2013-08-23 14:35 2 19 2013-08-23 14:32 1 19 2013-08-23 14:30 1 19 2013-08-23 14:25 2 19 2013-08-23 14:22 2 53 2013-08-23 14:59 1 53 2013-08-23 14:56 1 53 2013-08-23 14:57 1 53 2013-08-23 14:52 2 53 2013-08-23 14:50 2 53 2013-08-23 14:49 2 53 2013-08-23 14:18 2 53 2013-08-23 14:30 1 </code></pre> <p>I want to calculate the total time difference for each ID against Status 2. For example ID 19 stayed on Status 2 from 14:35 To 14:50 (15 minutes), then 14:22 to 14:30 (8 minutes). So the total ID 19 stayed on Status 2 is 23 minutes. <strong>(In both cases I considered the minimum time for status 2 to status 1)</strong> <strong>The problem is</strong> how do I only include difference between Status where they are different in <strong>next row</strong>. </p> <p>For example I would exclude the first record in the table with the status 1 and pick the second row. Same is the case with Status 2. I would pick the minimum time, calculate their difference and then add them up for multiple groups of status against each channel. </p> <p>I have tried using CURSOR but can't seem to make it work. I am also pretty confident that I can achieve that in C# after getting all the data and then looping through it. But I am just wondering if there is a direct way of getting the information without a loop. I also created a <a href="http://sqlfiddle.com/#!3/07830/8" rel="nofollow">SQL fiddle</a> with data, but I can't seem to make it work. I am coming from C# background with basic knowledge of SQL. Would be glad if someone can help me out. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGreg
    1. USuser2711965
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGet Time difference between group of records
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POGet Time difference between group of records
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO`14:35 To 14:50 (5 minutes)`? 5 minutes or 15 minutes? You should review the rule in your question and make sure it is what you want because it's very important for the answers being able to help you.
      singulars
      1. POGet Time difference between group of records
      1. USKing King
    2. COI don't quite get what you want to do.. could you maybe elaborate a little bit more?
      singulars
      1. POGet Time difference between group of records
      1. USBotis
    3. CO@KingKing, that was a typo, thanks for pointing that out.
      singulars
      1. POGet Time difference between group of records
      1. USuser2711965
 

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