On Jun 13, 11:17 am, bsingle...@ibss.net (Bob Singleton) wrote:
  

Any suggestions on how I can build a result set that would return

ASSET 001 AAA 1:00 (1 hour)
ASSET 001 BBB 0:10 (10 minutes)
ASSET 001 CCC 0:08 (8 minutes)
ASSET 001 DDD {difference between timestamp and now()}
ASSET 002 AAA 0:03 (3 minutes)
ASSET 002 BBB {difference detween timestamp and now()}
ASSET 003 AAA{diff between timestamp and now()}
    

SELECT
TYPE, ID, STATUS
, (COALESCE(
	(SELECT MIN(DATETIME) FROM STATUSLOG
	WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
	, NOW()::TIMESTAMP
	) - DATETIME) AS DURATION
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

  

SELECT
TYPE, ID, STATUS, SUM(
    (COALESCE(
	(SELECT MIN(DATETIME) FROM STATUSLOG
	WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
	, NOW()::TIMESTAMP
	) - DATETIME))
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS

Thanks for the lesson!

Bob Singleton