title: SQL query: time difference
body: <p>This is going to seem like a lame question for all experts in SQL server views but...</p>
  
  <p>So I have a small set of data that my client needs for reporting purposes. I have to admit that although I did ask them their reporting requirements, it isn't till now that I see that my db could be better optimised.</p>
  
  <p>One of the pieces of data they want is the time difference between two tasks that may have run:</p>
  
  <pre><code>select caseid, hy.createdate
  from app_history hy
  where hy.activityid in (303734, 303724)
  </code></pre>
  
  <p>This gives me two rows (after edit) per case-submission which then have to be measured; but a few wiggles: 
  Activity 303734 will always run, activity 303724 might run.
  Each 303734 and 303724 combo match up. Conceiveably a case can have 1 un-matched 303734 with a matched pair afterwards on the 2nd submission. Matching these might be down to intuition. Not good.
  There maybe more than one submission per caseid and if that is the case then both activities will run every subsequent time.
  There is no way to write the submission number to this table.</p>
  
  <p>The app_history table holds userid, caseid and activityid as foreign keys. The PK is the identity column ID.</p>
  
  <p>Is there a better way to write the query? </p>
  
  <p>AFter help from KM:</p>
  
  <pre><code>select
      c.id, c.submissionno, hya.caseid, hya.createtime, hyb.caseid, hyb.createtime
      ,CASE
           WHEN hyb.caseid IS NOT NULL THEN DATEDIFF(mi,hya.createtime,hyb.createtime)
           ELSE NULL
       END AS Difference 
      from app_case c
          inner join app_history hya on c.id = hya.caseid 
          left outer join  app_history hyb on c.id = hyb.caseid  
      where hya.activityid in (303734) and hyb.activityid in (303724) order by c.id asc
  </code></pre>
  
  <p>This nearly works.</p>
  
  <p>I now have this issue:</p>
  
  <pre><code>460509|2|460509|15:15:39.000|460509|15:16:13.000|1
  460509|2|460509|15:15:39.000|460509|15:18:13.000|3
  460509|2|460509|15:17:52.000|460509|15:16:13.000|-1
  460509|2|460509|15:17:52.000|460509|15:18:13.000|1
  </code></pre>
  
  <p>So I am now getting 1 row comparing each of the two for each of the four rows... mmm I think it is the best I can hope for. :(</p>