• Welcome, Guest. Please login.
 
May 26, 2019, 04:33:13 am

News:

Welcome to the SQLitening support forums!


SQL (Hopefully) question

Started by Andrew Lindsay, December 01, 2009, 08:29:00 pm

Previous topic - Next topic

Andrew Lindsay

Greetings again,

I have a table that has the following set-up

RecordTime DATETIME,
extTens1 INTEGER,
Pitch1 REAL,
Pitch2 REAL,
Pitch3 REAL,
Roll1 REAL,....

The RecordTime is set as a unique key and index, and the values are stored every second, from some time in February until the beginning of October.

I would like to create a table say for Pitch 1, that would extract all the Pitch1 results per month, so I would have a table with newRecordTime, FebPitch1, MarPitch1, ...

Any assistance would be appreciated.

Regards

Andrew

David L Morris

Hj Andrew,  You might want to test something like this SQL and extend it to insert the results into your new table
.
select '07', sum(Pitch1) from YourTable where strftime('%m',RecordTime) = '07'

This should get total for July
Hope it helps
David

Fred Meier

If I understand your request, this could be a way to insert a record for a specific year(2009).

   Dim laPitch1s(1 to 12) as Double

   slSel "Select strftime('%m',RecordTime) as Month, Pitch1 from CurrentTable where strftime('%Y',RecordTime) = '2009'"
   do while slGerRow
      laPitch1s(val(slFN("Month")) = val(Picth1)     
   loop
   slExe slBuildInsertOrUpdate("NewTable", datetime('now','localtime') & $NUL & _
                                           format$(laPitch1s(1)) & $NUL & _
                                           format$(laPitch1s(2)) & $NUL & _
                                           etc.......


Bern Ertl

I like to store date/time as FILETIME (quad) values in the DB.  This allows you to do date math and selections with your SQL statements directly from the DB.