Re: [sqlite] Getting Complete Years Only

[John Machin]

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: 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.

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Complete Years Only

[Rick Ratchford]

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

Re: [sqlite] Getting Complete Years Only

[Igor Tandetnik]

John Machin  wrote:
> On 9/07/2009 3:39 AM, Igor Tandetnik wrote:
>> Rick Ratchford
>>  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

Re: [sqlite] Getting Complete Years Only

[John Machin]

On 9/07/2009 3:39 AM, Igor Tandetnik wrote:
> Rick Ratchford 
> 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. For example, if 
there were no records for January 1977, Igor's query would still use 
1976 as the first complete year.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Complete Years Only

[Rick Ratchford]

You're a genius Igor.

Thank you very much! Much easier than what I was thinking.

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 12:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Getting Complete Years Only

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);

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

Re: [sqlite] Getting Complete Years Only

[Igor Tandetnik]

Rick Ratchford 
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);

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users