Rick Ratchford <r...@amazingaccuracy.com>
> Data Fields: ID, Date, Month, Day, Year, Price
>
> Problem: When provided the starting Month/Day numbers, and ending
> Month/Day numbers, what is the correct way to SQL the database so
> that the recordset created returns as follows (assume 4 years of
> data):
>
> In other words, all the 3/12's first, then 3/13's, then 3/14's, etc.
> all the way down to the ending Month/Date.
>
> Where I really get stuck is when the Starting Month number is greater
> than the Ending Month number. For example, say I want the starting
> Month/Day as 10/22 and the ending Month/Day as 4/16. Simply stating
> WHERE Month >= Start Month AND Month <= End Month doesn't seem
> correct. Since I want to return all the prices between 10/22 and 4/16
> of each year of data I have in the table, no Month number could be
> greater than/equal to 10 and also less than/equal to 4.
Try something like this:
select Month, Day, Price from mytable
where ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300 <
((:EndMonth - :StartMonth)*100 + (:EndDay - :StartDay) + 1300) % 1300
order by ((Month - :StartMonth)*100 + (Day - :StartDay) + 1300) % 1300;
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
