To add to my last post shown below, what I've done is added the "Date" to
the ORDER BY, thus putting the unwanted rows at the very top. It doesn't
remove it, but it does allow for stripping it off easier when transferring
to an array if that is the best way to go.
sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" &
gsTableName & "] " & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
lngStartDay & ") + 1300) % 1300 <= ((" & _
lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
lngStartDay & ") + 1300) % 1300 " & _
"ORDER BY Date, Year, ((Month - " & lngStartMth & ")*100 + (Day
- " & lngStartDay & ") + 1300) % 1300"
Cheers!
Rick
#>-----Original Message-----
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
#>Sent: Tuesday, November 10, 2009 10:59 PM
#>To: 'General Discussion of SQLite Database'
#>Subject: [sqlite] Reverse Referencing Rows
#>
#>A while back, Igor gave me some help on pulling out mm/dd
#>ranges (sets) from my table.
#>
#>This is the code that does that.
#>
#> sSQL = "SELECT Date, Year, Month, Day, Open, High, Low,
#>Close FROM [" & gsTableName & "] " & _
#> "WHERE ((Month - " & lngStartMth & ")*100 + (Day
#>- " & lngStartDay & ") + 1300) % 1300 <= ((" & _
#> lngEndMth - lngStartMth & ") * 100 + (" &
#>lngEndDay - lngStartDay & ") + 1300) % 1300 " & _
#> "ORDER BY Year, ((Month - " & lngStartMth &
#>")*100 + (Day - " & lngStartDay & ") + 1300) % 1300"
#>
#>The only problem I have is that there are some stray rows
#>that are not wanted.
#>
#>For example, say that I want to run this SQL statement to
#>pull out SETS that start with a MM/DD of 12/28 to 01/05. That
#>means, each 'set' would be from December 25 to January 05,
#>which means that each 'set' will cross a year end date (where
#>the year value increments by 1).
#>
#>While the above SQL statement will do this, to a point, the
#>first set is usually not correct. It will look like this:
#>
#>Date | Year | Month | Day
#>
#>12/28/1988 1988 12 28
#>12/29/1988 1988 12 29
#>12/30/1988 1988 12 30
#>01/04/1988 1988 01 04
#>01/05/1988 1988 01 05
#>12/28/1989 1989 12 28
#>12/29/1989 1989 12 29
#>01/03/1989 1989 01 03
#>01/04/1989 1989 01 04
#>01/05/1989 1989 01 05
#>
#>As you can see, the first set has a problem It goes from
#>December 28, 1988 to January 05, 1988, rather than January
#>05, 1989 like it should for the first SET.
#>
#>I fugure the way to correct this issue is to make sure that each ROW
#>(record) has a DATE that is greater than the last ROW.
#>
#>Is it possible to have the SQL statement above do this as well?
#>
#>I know I can loop through this recordset, transferring each
#>record to an array that has a date greater than the last
#>record (row) date value. It's an extra step and easy to program.
#>
#>I'm just curious if it was possible within the above SQL
#>statement, and whether it would be worth doing it via the SQL
#>or would perform better if I added the looping into array step.
#>
#>Thank you.
#>
#>Rick
#>
#>
#>_______________________________________________
#>sqlite-users mailing list
#>sqlite-users@sqlite.org
#>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
#>
#>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
