On Mon, Aug 13, 2012 at 05:28:24PM -0700, Carey Tilden wrote: > how to solve it. I have a list of program start/stop times, and I want to > know how long each run takes to complete. The thing that's really tripping > me up is there are gaps in the sequence. I've figured out how to collapse > the results down to a single row per attempt, but I can't quite figure out > how to further collapse down each full run to its own row. It'd be easy if > I had a session_id or something to group on, but I don't. All I have are > the start/stop times. Looking at your data and the sample output you provided, it would appear that the "real" start time of a program is always the one that is started _after_ a completion (or else it's the very first start). In other words, it's never the case that a "start" in the program_runs data is the start of a new run when an existing, previously-started run hasn't completed. Is that right? If so, then you ought to be able to use windowing functions. For each completion, pick the earliest start before it that is _after_ a completion or, if there is no such completion, is the very first record. See the discussion of windowing functions in the manual for how to do this. Best, A -- Andrew Sullivan ajs@xxxxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
