SQL query: time difference

Question

Hi all,

This is going to seem like a lame question for all experts in SQL server views but...

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.

One of the pieces of data they want is the time difference between two tasks that may have run:

select caseid, hy.createdate
from app_history hy
where hy.activityid in (303734, 303724)

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.

The app_history table holds userid, caseid and activityid as foreign keys. The PK is the identity column ID.

Is there a better way to write the query?

AFter help from KM:

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

This nearly works.

I now have this issue:

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

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. :(

Answer 1

USE LEFT JOIN

SELECT
    a.caseid, a.createdate
        ,b.caseid, b.createdate
        ,CASE
             WHEN b.caseid IS NOT NULL THEN DATEDIFF(mi,a.createdate,b.createdate)
             ELSE NULL
         END AS Difference
    FROM app_history                a
        LEFT OUTER JOIN app_history b ON b.activityid=303724
    WHERE a.activityid=303734

EDIT after a little more schema info...

SELECT
    a.caseid, a.createdate
        ,b.caseid, b.createdate
        ,CASE
             WHEN b.caseid IS NOT NULL THEN DATEDIFF(mi,a.createdate,b.createdate)
             ELSE NULL
         END AS Difference
    FROM (SELECT MAX(ID) AS MaxID FROM app_history WHERE activityid=303734)                 aa
        INNER JOIN app_history                                                               a ON aa.MaxID=a.ID
        LEFT OUTER JOIN a(SELECT MAX(ID) AS MaxID FROM app_history WHERE activityid=303724) bb ON 1=1
        LEFT OUTER JOIN app_history                                                          b ON bb.MaxID=b.ID

Answer 2

do something like this

select datediff(
day,
(select isnull(hy.createdate,0) from app_history hy where hy.activityid =303734),
(select isnull(hy.createdate,0) from app_history hy where hy.activityid =303724)
)