Lucky nothing. You're just brilliant. Such humility. :-) Yes, it worked wonderfully. The dataset only contains data that does not fall on weekends. Stock data to be exact.
It is likely to start sometime during the year of the first year available and end sometime during the year of the last year. I want to only return 'complete years' from January to December. Now I will admit that I made a minor adjustment once Igor pointed me in the right direction. Since stock data does not start on Jan 01, as that is New Year's and no trading occurs then, and that not all years will see trading on Dec 31. I simply adjusted the statement to allow a few days following Jan 01 and a few before Dec 31. Thanks again. :-) Rick -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, July 08, 2009 6:17 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Getting Complete Years Only John Machin <sjmac...@lexicon.net> wrote: > On 9/07/2009 3:39 AM, Igor Tandetnik wrote: >> Rick Ratchford >> <r...@amazingaccuracy.com> wrote: >>> Can someone help me with a SQL request? >>> >>> The Table contains Date, as well as Year, Month and Day columns. >>> >>> I would like to return a recordset that is made up of only COMPLETE >>> YEARS, from January to December. >>> >>> Say my data starts on August 14, 1975 and ends with May 4, 2009. >>> >>> How do I get just those records that start from January 1, 1976 to >>> December 31, 2008, as those would be the only COMPLETE YEARS from >>> January to December in my table? >> >> Perhaps something like this: >> >> select * from myTable >> where Year between >> (select min(Year) from myTable where Month=1 and Day=1) and >> (select max(Year) from myTable where Month=12 and Day=31); >> > > This assumes something that wasn't explicitly stated: there is data > for each and every day from the start date to the end date. Hence "perhaps". In the face of incomplete information, I made an educated guess and designed the simplest query that would produce correct results under certain reasonable assumptions. Occam's razor and all that. Had I guessed wrong, the OP would have come back with the explanation as to why the query didn't work for him, necessarily supplying the missing information in the process. Then I could come up with a more complex query taking new facts into account. It seems I got lucky on the first attempt, and the OP is happy with the results. Igor Tandetnik _______________________________________________ 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
