-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE activitydetailsall (
    detail INTEGER,
    activityid INTEGER,
    status INTEGER,
    ts DATETIME,
    PRIMARY KEY(detail)
)
GO

INSERT INTO activitydetailsall VALUES(789802, 197476, 24, '2008-02-08 09:40:25.490')
GO
INSERT INTO activitydetailsall VALUES(789803, 197476, 26, '2008-02-08 09:40:36.473')
GO
INSERT INTO activitydetailsall VALUES(784262, 198059, 20, '2007-12-05 14:03:43.903')
GO
INSERT INTO activitydetailsall VALUES(785482, 198059, 19, '2007-12-07 11:47:13.337')
GO
INSERT INTO activitydetailsall VALUES(785496, 198059, 19, '2007-12-07 12:01:01.557')
GO
INSERT INTO activitydetailsall VALUES(785648, 198059, 10, '2007-12-07 14:01:51.490')
GO
INSERT INTO activitydetailsall VALUES(787693, 198059, 10, '2007-12-12 15:53:15.950')
GO
INSERT INTO activitydetailsall VALUES(789804, 198059, 10, '2008-02-08 09:41:26.490')
GO
INSERT INTO activitydetailsall VALUES(789805, 198059, 26, '2008-02-08 09:41:37.287')
GO
INSERT INTO activitydetailsall VALUES(790021, 198059, 19, '2008-05-04 10:10:41.510')
GO
INSERT INTO activitydetailsall VALUES(800021, 198059, 20, '2008-05-04 15:10:41.510')
GO
SELECT * FROM activitydetailsall
GO

SELECT ada1.activityid AS activityid, ada1.status as fromstatus, ada2.status as tostatus, DATEDIFF(dd,ada1.ts,ada2.ts) AS days
FROM activitydetailsall ada1, activitydetailsall ada2
WHERE ada1.activityid = ada2.activityid AND ada1.status=26 AND ada2.ts > ada1.ts
GO

SELECT aid,fromstatus,tostatus,days FROM (
SELECT ada1.activityid AS aid, ada1.status as fromstatus, ada2.status as tostatus, ada1.ts AS t1, ada2.ts AS t2, DATEDIFF(dd,ada1.ts,ada2.ts) AS days
FROM activitydetailsall ada1, activitydetailsall ada2
WHERE ada1.activityid = ada2.activityid AND ada1.status=26 AND ada2.ts > ada1.ts
) x WHERE t2 = (SELECT MIN(ts) FROM activitydetailsall WHERE activityid = aid AND ts > t1)
GO
