On 9/07/2009 9:33 AM, Rick Ratchford wrote: > 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: [email protected] > [mailto:[email protected]] On Behalf Of Igor Tandetnik > Sent: Wednesday, July 08, 2009 6:17 PM > To: [email protected] > Subject: Re: [sqlite] Getting Complete Years Only > > John Machin <[email protected]> wrote: >> On 9/07/2009 3:39 AM, Igor Tandetnik wrote: >>> Rick Ratchford >>> <[email protected]> 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.
It seems you weren't lucky, but the OP has worked out how to adjust the query and happiness after all prevails :-) _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
