"Jay A. Kreibich" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Fri, Mar 14, 2008 at 10:47:35AM -0400, Igor Tandetnik scratched on > the wall: >> P Kishor <[EMAIL PROTECTED]> wrote: >>> On 3/14/08, BandIT >>> <[EMAIL PROTECTED]> wrote: >>>> >>>> I have a table: >>>> >>>> CREATE TABLE Log >>>> ( >>>> TimeStamp char(14), -> Format YYYYMMDDHHNNSS >>>> Code char(5), -> 5 digit status code >>>> ) >>>> >>>> example: >>>> >>>> 20080314100030 A0001 >>>> 20080314101000 A0002 >>>> 20080314101700 A0000 >>>> 20080314102000 A0002 >>>> 20080314102100 A0000 >>>> >>>> I would like a query to output the the time between the status >>>> codes (sum) to know how long each status has been active. I am not >>>> so very familiar with SQL and SQLite, and I hope I am posting to >>>> the correct forum. >>> >>> SELECT Code, max(TimeStamp) -min(TimeStamp) AS TimePassed >>> FROM Log >>> GROUP BY Code; >> >> That would give TimePassed of zero for any code that only appears >> once. > > That might be considered an appropriate value
Might be by you, but not by the original poster. In another message in this thread, he explains what he wants. Basically, a record in the table indicates when a particular code goes "on". When a code goes "on", any code that was "on" before automatically goes "off" (only one code at a time is "on"). The OP wants to calculate for every code the total time this code was "on" (possibly in several disjoint intervals). Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
