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 if you're looking for the time between events. Or not. But it is a good point. > It will also overcount codes that appeared more than once, but with > other codes in between (such as A0002). I'm not following you on this one, and I'd like to understand. Given this table: sqlite> select * from log; ts code -------------- ---------- 20080314100030 A1 20080314101000 A2 20080314101700 A0 20080314102000 A2 20080314102100 A0 20080314102050 A2 I get these results: sqlite> SELECT code, max(ts) - min(ts) AS duration FROM log GROUP BY code; code duration ---------- ---------- A0 400 A1 0 A2 1050 Which is exactly what I expected... A1 is zero as you pointed out, but A2 is correctly listed as 1050, even through there is a higher max value in an A0 record that sits between the A2 records. I'm not sure what you mean by "overcounting" codes. Doesn't the GROUP BY essentually break up the select into sub-groups of rows with matching code values, and then (and only then) the min/max aggregate functions run over the rows of each individual sub-groups, reducing them to a single line and producing the desired results? What am I missing? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
