• Welcome, Guest. Please login.
 
August 19, 2019, 07:21:41 am

News:

Welcome to the SQLitening support forums!


Trouble with SQL Query

Started by Andrew Lindsay, June 30, 2009, 01:00:52 am

Previous topic - Next topic

Andrew Lindsay

I am having trouble with the following Query.

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009/04/01T00:00:00' AND RecordTime < '2009/04/01T01:00:00'"
  Replace "'" With $Dq In sqlQuery
  slSEL(sqlQuery,1)
  slGetRow(1)
  NumberOfRows = Val(slFN("RowCount",1))
  slCloseSet(1)


I should get an answer of 60 (I have data for every minute), however, I get an answer of 0.  Any assistance would be appreciated.

Regards


Andrew

Andrew Lindsay

June 30, 2009, 01:29:12 am #1 Last Edit: June 30, 2009, 10:58:57 am by Andrew Lindsay
OK,

I stumbled across the answer to above, it is a force of habit to format dates with a '/' character, but it needs to be a '-' character...

Now...

I am about to head offshore tonight so I will get my question in now, because I know I am not going to be able to solve it myself.

I have two tables.

Table one contains the monitoring data at minute intervals.  The table looks like this.
















Time    Hs        Vw        Tw   
2009-04-01 00:00:00    0.4        20        45   
2009-04-01 00:01:00    0.5        30        20   
2009-04-01 00:02:00    0.4        30        45   
...    ...        ...        ...   


Table two contains my reporting criteria


















Start Timeend Time    Hs_crit        Vw_crit        Tw_crit   
2009-04-01 00:00:002009-04-01 01:00:00    0.5        30        40   
2009-04-01 01:00:002009-04-01 02:00:00    0.25        30        20   
2009-04-01 02:00:002009-04-01 03:00:00    0.3        30        30   
...............


For each Criteria Row, I want to count the number of entries that exceed the criteria, i.e. the results would be something like...

















Start TimeEnd TimeCount
2009-04-01 00:00:002009-04-01 01:00:0035
2009-04-01 01:00:002009-04-01 02:00:0060
2009-04-01 02:00:002009-04-01 03:00:000
.........


Any assistance in this would be greatly appreciated.

Regards

Andrew

Rolf Brandt

June 30, 2009, 06:56:14 am #2 Last Edit: June 30, 2009, 06:58:43 am by Rolf Brandt
One or more things might cause the problem.

Why are you using Replace? What is the value of '$Dq' in your code?
Replace "'" With $Dq In sqlQuery

Since SQLite saves date and time anyway as a text string you should check how the data is saved. If your application wrote the data to the database then use the same format in your WHERE string. It should match the data.

Also when writing a DateTime value to the database it would be best to put the value in between quotes, like '2009-04-01 01:00:00'. This would make sure that SQLite does not interprete it as a numerical value.

So if you use the format "YYYY-MMYDD HH:MM:SS" then the WHERE part of you SQL string should look like this:
Where RecordTime >= '2009-04-01 00:00:00' AND RecordTime < '2009-04-01 01:00:00'

Rolf
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Bern Ertl

Hi Andrew, Rolf is correct, string values can be enclosed with single quotes in SQL statements.

Andrew Lindsay

June 30, 2009, 10:57:34 am #4 Last Edit: June 30, 2009, 11:04:14 am by Andrew Lindsay
Yes,

Thanks guys.  I've got that one sorted out.  I thought that my problem was that I was using single quotes and not double quotes, however, the reason for my error was that I was using a date format of YYYY/MM/DD instead of YYYY-MM-DD.  i,e.

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009/04/01T00:00:00' AND RecordTime < '2009/04/01T01:00:00'"
 
This should read

  sqlQuery = "SELECT COUNT(*) As RowCount FROM Data Where RecordTime >= '2009-04-01T00:00:00' AND RecordTime < '2009-04-01T01:00:00'"

Then it all works out fine...




Now I have moved onto the second part of the question where I am trying to use the criteria from one table as a basis for a query on another table.

Regards

Andrew