The square brackets are very misleading... You need to join the table to itself to find consecutive recordings (ON datetime(t1.end_datetime,'3 minutes') >= t2.start_datetime). It may be a good idea to have a field "sequence_id" to store the sequence number in.
Iterative solution: UPDATE recordings SET sequence_id=(SELECT IFNULL(MAX(sequence_id)+1,1) FROM recordings WHERE sequence_id IS NOT NULL) WHERE start_datetime=(SELECT MIN(start_datetime) FROM recordings WHERE sequence_id IS NULL); UPDATE recordings SET sequence_id=(SELECT r2.sequence_id FROM recordings r2 WHERE datetime(end_datetime,'3 minutes') >= r2.start_datetime) WHERE sequence_id IS NULL; Someone will find a method to combine the two statements into one, and maybe replace iteration with a self join... -----Ursprüngliche Nachricht----- Von: jdp12383 [mailto:[email protected]] Gesendet: Mittwoch, 28. August 2013 23:59 An: [email protected] Betreff: [sqlite] T-SQL to retrieve needed records Hi, I am new to SQLite. I am using SQLite Manager Firefox add-on. I have created a database and a table. This table stores webcam recordings. Each record is a recording up to 3 min. I am trying to retrieve one record per continuous recording. If there is gap more than 3 min then it would be considered as a separate recording. Below is the script. [CODE] CREATE TABLE recordings ( [key] INTEGER PRIMARY KEY ASC AUTOINCREMENT, filename VARCHAR(50), start_datetime DATETIME, end_datetime DATETIME, deleted BOOLEAN ); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f1', '2013-08-26 00:00:00', '2013-08-26 00:03:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f2', '2013-08-26 00:03:01', '2013-08-26 00:06:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f3', '2013-08-26 00:06:01', '2013-08-26 00:09:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f4', '2013-08-26 00:14:00', '2013-08-26 00:17:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f5', '2013-08-26 00:17:01', '2013-08-26 00:20:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f6', '2013-08-26 00:20:01', '2013-08-26 00:23:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f7', '2013-08-26 00:30:00', '2013-08-26 00:33:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f8', '2013-08-26 00:33:01', '2013-08-26 00:36:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f9', '2013-08-26 00:36:01', '2013-08-26 00:39:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f10', '2013-08-26 00:44:00', '2013-08-26 00:47:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f11', '2013-08-26 00:47:01', '2013-08-26 00:50:00', 0); INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f12', '2013-08-26 00:50:01', '2013-08-26 00:53:00', 0); [/CODE] The result should be, [CODE] recording1 2013-08-26 00:00:00 2013-08-26 00:09:00 recording2 2013-08-26 00:14:00 2013-08-26 00:23:00 recording3 2013-08-26 00:30:00 2013-08-26 00:39:00 recording4 2013-08-26 00:44:00 2013-08-26 00:53:00 [/CODE] i would appreciate any help in resolving this. Thanks, Jdp -- View this message in context: http://sqlite.1065341.n5.nabble.com/T-SQL-to-retrieve-needed-records-tp70760.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: [email protected] This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
