Re: [sqlite] Help with SQLite Query
Igor, That has worked perfectly. Thankyou very much for your assistance. Regards Andrew -- View this message in context: http://www.nabble.com/Help-with-SQLite-Query-tp24297858p24305860.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with SQLite Query
JokBoy wrote: > I have tried your query and I don't get any rows returned. Any ideas > why it wouldn't work? If you tried it against the database file you showed in your original post, note that you have MOEData.Date in different format from Criteria.StartTime_crit and EndTime_crit. Recall that SQLite doesn't have a dedicated date/time type: those fields are just strings, and are compared as strings, lexicographically. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with SQLite Query
Igor, I have tried your query and I don't get any rows returned. Any ideas why it wouldn't work? Regards Andrew -- View this message in context: http://www.nabble.com/Help-with-SQLite-Query-tp24297858p24298529.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with SQLite Query
JokBoy wrote: > Basically, I would like to be able to query one table based on > criteria entered into another table. > > I have attached an excel file with a couple of hours of monitoring > data, the 10 minute data has been interpolated to give minute answers > (as MOEData Tab in the excel sheet). > > This Table of Data would be called MOEData in the database (original I > know...) > > I then would like to have another table (See Criteria Table Tab in > excel sheet). This table would provide the criteria to be used. A > verbose description of the actual query would be as follows (when > looking at the first line of the table). > > Count all the rows between 0:00 and 1:20 on the 16/06/2009, where the > MOEData.Hs is greater than 1.3, OR the (MOEData.Vwind > 20 AND > (Twind is between 45 and 135, or Twind is between 225 and 315)) select Start_Time, End_Time, count(*) as "Above Criteria" from Criteria c join MOEData d on ( d.Date between c.Start_Time and c.End_Time and (d.HsSea > c.Hs_crit or (d.Vwind > c.Vwind_crit and (d.Twind between 45 and 135 or d.Twind between 225 and 315) ) ) ) group by c.rowid; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with SQLite Query
Basically, I would like to be able to query one table based on criteria entered into another table. I have attached an excel file with a couple of hours of monitoring data, the 10 minute data has been interpolated to give minute answers (as MOEData Tab in the excel sheet). This Table of Data would be called MOEData in the database (original I know...) I then would like to have another table (See Criteria Table Tab in excel sheet). This table would provide the criteria to be used. A verbose description of the actual query would be as follows (when looking at the first line of the table). Count all the rows between 0:00 and 1:20 on the 16/06/2009, where the MOEData.Hs is greater than 1.3, OR the (MOEData.Vwind > 20 AND (Twind is between 45 and 135, or Twind is between 225 and 315)) Based on the criteria in the table, the results of the query would be as per the results in the Results tab in the excel sheet. I have also included the above tables as an SQLite database. Regards Andrew http://www.nabble.com/file/p24297858/SQL%2BQuery%2BInformation.xls SQL+Query+Information.xls http://www.nabble.com/file/p24297858/TestDatabase.db3 TestDatabase.db3 -- View this message in context: http://www.nabble.com/Help-with-SQLite-Query-tp24297858p24297858.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users