Re: [sqlite] Need Help SQL

[Rick Ratchford]

Thanks! 

Cheers!
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of sub sk79
#>Sent: Monday, October 12, 2009 9:35 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Need Help SQL
#>
#>Hi!,
#>
#>Here is a non-math version using PL/SQL date operators and functions
#>available in StepSqlite (https://www.metatranz.com/stepsqlite/).
#>Hopefully this should be easier to follow.
#>
#>You can compile the below code directly to a win32 dll on the
#>StepSqlite website and then use it in your VB code.
#>
#>Assumes Date column is in '-MM-DD'. If using a different format,
#>just call   DateTime.setDateFormat()   to set proper format.
#>
#>
#>create table items(ID integer, Date date, Price float);
#>PACKAGE BODY MyPackage IS
#>PROCEDURE get_prices (start_month char, start_day char, end_month
#>char, end_day char  ) IS
#>BEGIN
#>-- n_* below are dates normalized to fall in a given year, here I
#>chose year 2000 because its a leap year and has all possible
#>day-numbers for proper normalization.
#>FOR item IN
#>(SELECT id, to_char(date, 'MM') mon, to_char(date, 'DD') day, price
#> FROM (SELECT id, date, price,
#> to_date('2000-' || start_month||'-'||start_day,
#>'-MM-DD') n_start,
#> to_date('2000-' || end_month  ||'-'||end_day,
#>'-MM-DD') n_end,
#> to_date(to_char(date, '2000-MM-DD'), '-MM-DD')
#>n_date
#>FROM items
#>  )
#> WHERE (n_start < n_end AND n_date between n_start and n_end)
#>OR (n_start > n_end AND n_date NOT between n_end and n_start)
#> ORDER BY to_char(date, 'MM-DD')
#> )
#>LOOP
#>DBMS_OUTPUT.put_line(item.id || '  ' || item.mon || '
#>'||item.day||'  '||item.price);
#>END LOOP;
#>END;
#>
#>BEGIN
#>insert into items(id, date, price) values(1,'2004-01-01',  1.1);
#>insert into items(id, date, price) values(2,'2004-02-01',  1.1);
#>insert into items(id, date, price) values(3,'2004-02-16',  1.1);
#>insert into items(id, date, price) values(4,'2004-10-01',  1.1);
#>insert into items(id, date, price) values(5,'2004-10-22',  1.1);
#>
#>insert into items(id, date, price) values(51,'2005-01-01',  5.1);
#>insert into items(id, date, price) values(52,'2005-02-01',  5.1);
#>insert into items(id, date, price) values(53,'2005-02-16',  5.1);
#>insert into items(id, date, price) values(54,'2005-10-01',  5.1);
#>insert into items(id, date, price) values(55,'2005-10-22',  5.1);
#>
#>insert into items(id, date, price) values(61,'2006-01-01',  6.1);
#>insert into items(id, date, price) values(62,'2006-02-01',  6.1);
#>insert into items(id, date, price) values(63,'2006-02-16',  6.1);
#>insert into items(id, date, price) values(64,'2006-10-01',  6.1);
#>insert into items(id, date, price) values(65,'2006-10-22',  6.1);
#>
#>DBMS_OUTPUT.put_line('Price data Range: 02-15 to 10-21');
#>get_prices('02', '15', '10', '21');
#>
#>DBMS_OUTPUT.put_line('Price data Range: 10-21 to 02-15');
#>get_prices('10', '21', '02','15');
#>
#>rollback;
#>END;
#>
#>
#>Result:
#>
#>Price data Range: 02-15 to 10-21
#>3  02  16  1.1
#>53  02  16  5.1
#>63  02  16  6.1
#>4  10  01  1.1
#>54  10  01  5.1
#>64  10  01  6.1
#>Price data Range: 10-21 to 02-15
#>1  01  01  1.1
#>51  01  01  5.1
#>61  01  01  6.1
#>2  02  01  1.1
#>52  02  01  5.1
#>62  02  01  6.1
#>5  10  22  1.1
#>55  10  22  5.1
#>65  10  22  6.1
#>
#>
#>
#>Regards,
#>SK
#>___
#>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] Need Help SQL

[sub sk79]

Hi!,

Here is a non-math version using PL/SQL date operators and functions
available in StepSqlite (https://www.metatranz.com/stepsqlite/).
Hopefully this should be easier to follow.

You can compile the below code directly to a win32 dll on the
StepSqlite website and then use it in your VB code.

Assumes Date column is in '-MM-DD'. If using a different format,
just call   DateTime.setDateFormat()   to set proper format.


create table items(ID integer, Date date, Price float);
PACKAGE BODY MyPackage IS
PROCEDURE get_prices (start_month char, start_day char, end_month
char, end_day char  ) IS
BEGIN
-- n_* below are dates normalized to fall in a given year, here I
chose year 2000 because its a leap year and has all possible
day-numbers for proper normalization.
FOR item IN
(SELECT id, to_char(date, 'MM') mon, to_char(date, 'DD') day, price
 FROM (SELECT id, date, price,
 to_date('2000-' || start_month||'-'||start_day,
'-MM-DD') n_start,
 to_date('2000-' || end_month  ||'-'||end_day,
'-MM-DD') n_end,
 to_date(to_char(date, '2000-MM-DD'), '-MM-DD')
n_date
FROM items
  )
 WHERE (n_start < n_end AND n_date between n_start and n_end)
OR (n_start > n_end AND n_date NOT between n_end and n_start)
 ORDER BY to_char(date, 'MM-DD')
 )
LOOP
DBMS_OUTPUT.put_line(item.id || '  ' || item.mon || '
'||item.day||'  '||item.price);
END LOOP;
END;

BEGIN
insert into items(id, date, price) values(1,'2004-01-01',  1.1);
insert into items(id, date, price) values(2,'2004-02-01',  1.1);
insert into items(id, date, price) values(3,'2004-02-16',  1.1);
insert into items(id, date, price) values(4,'2004-10-01',  1.1);
insert into items(id, date, price) values(5,'2004-10-22',  1.1);

insert into items(id, date, price) values(51,'2005-01-01',  5.1);
insert into items(id, date, price) values(52,'2005-02-01',  5.1);
insert into items(id, date, price) values(53,'2005-02-16',  5.1);
insert into items(id, date, price) values(54,'2005-10-01',  5.1);
insert into items(id, date, price) values(55,'2005-10-22',  5.1);

insert into items(id, date, price) values(61,'2006-01-01',  6.1);
insert into items(id, date, price) values(62,'2006-02-01',  6.1);
insert into items(id, date, price) values(63,'2006-02-16',  6.1);
insert into items(id, date, price) values(64,'2006-10-01',  6.1);
insert into items(id, date, price) values(65,'2006-10-22',  6.1);

DBMS_OUTPUT.put_line('Price data Range: 02-15 to 10-21');
get_prices('02', '15', '10', '21');

DBMS_OUTPUT.put_line('Price data Range: 10-21 to 02-15');
get_prices('10', '21', '02','15');

rollback;
END;


Result:

Price data Range: 02-15 to 10-21
3  02  16  1.1
53  02  16  5.1
63  02  16  6.1
4  10  01  1.1
54  10  01  5.1
64  10  01  6.1
Price data Range: 10-21 to 02-15
1  01  01  1.1
51  01  01  5.1
61  01  01  6.1
2  02  01  1.1
52  02  01  5.1
62  02  01  6.1
5  10  22  1.1
55  10  22  5.1
65  10  22  6.1



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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rick Ratchford 
wrote: 
> Your code:
> 
> ((:EndMonth - :StartMonth) * 100 + (:EndDay - :StartDay) + 1300) %
> 1300 
> 
> VB:
> 
> (" & lngEndMth - lngStartMth) & ") * 100 + " & ((lngEndDay -
> lngStartDay) + 1300) Mod 1300
> 
> In the VB version, I'm simply doing all the math outside the string
> itself

Not all math: you are doing "*100 + " in SQL. Herein lies the problem.

> and concat it within the string. Just for giggles, I changed
> so the math is done inside the SQL string and changed mod back to %.
> Same results. 
> 
> SELECT Date, Month, Day, Open, High, Low, Close FROM [AUS $, COMP-R
> AN_REV] WHERE ((Month - 8)*100 + (Day - 1) + 1300) % 1300 <= -4 * 100
> + 0 ORDER BY ((Month - 8)*100 + (Day - 1) + 1300) % 1300

This is not equivalent to what I wrote. It lacks "+1300) % 1300) on the right 
hand side. In my statement, both sides of the comparison are always 
non-negative.

> Well, after going through all the above steps explaining what I did to
> convert, it appears that I can SEE what you mean by the above
> statement. 
> 
> Rather than this...
> 
> (" & lngEndMth - lngStartMth & ") * 100 + ((" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 "
> 
> It should have been this...
> 
> ((" & lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 "

Either that, or 

& ((lngEndMth - lngStartMth) * 100 +  (lngEndDay - lngStartDay) + 1300) Mod 
1300 &

It's OK to do the math on VB side, as long as you actually do the correct math.

Igor Tandetnik


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

Re: [sqlite] Need Help SQL

[RB Smissaert]

> I'm using Olaf Schmidt's VB SQLite binder.

That does use parameterized statements.
Look at the methods and properties of the cCommand object in the object
browser. Also look at the demo code that comes with dhRichClient3.

RBS


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: 12 October 2009 20:16
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Need Help SQL

#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik

I'm not up on the terminology. I'm using Olaf Schmidt's VB SQLite binder.
He'd probably be better to answer this question than I.

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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

#>> Here is that string from your earlier example:
#>>
#>> sSQL = "SELECT Date, Month, Day, Open, High, Low, Close FROM [" &
#>> gsTableName & "] " & "WHERE ((Month - " & lngStartMth & ")*100 + (Day
#>> - " &
#>> lngStartDay & ") + 1300) % 1300 <= " & lngEndMth - lngStartMth & " *
#>> 100 + " & ((lngEndDay - lngStartDay) + 1300) Mod 1300 & " ORDER BY
#>> ((Month - " &
#>> lngStartMth & ")*100 + (Day - " & lngStartDay & ") + 1300) % 1300"
#>
#>In the the right-hand side of <= operator, you only apply  "mod 1300" to
#>((lngEndDay - lngStartDay) + 1300) part but not to
#>(lngEndMth - lngStartMth)*100 part. Be careful which calculations you
#>perform in the host language, and which you embed in SQL
#>statement. You have a strange mix of the two.
#>
#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik

The <= replaced the < you had in the original because it would return UP TO
but not including the Ending mm/dd.

Since the problem starts following the WHERE clause, I'm not going to
include the info before it.

I have in my code...

WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") +
1300) % 1300 <= (" & lngEndMth - lngStartMth & ") * 100 + " & ((lngEndDay -
lngStartDay) + 1300) Mod 1300 & " ORDER BY ((Month - " & lngStartMth &
")*100 + (Day - " & lngStartDay & ") + 1300) % 1300"

Your code:

WHERE ((Month - :StartMonth) * 100 + (Day - :StartDay) + 1300) % 1300 <

VB:

WHERE ((Month - " & lngStartMth & ") * 100 + (Day - " & lngStartDay & ") +
1300 % 1300 <=

All that was done is that :StartMonth is replaced with variable lngStartMth
and :StartDay replaced with variable lngStartDay. Both long values.

Your code:

((:EndMonth - :StartMonth) * 100 + (:EndDay - :StartDay) + 1300) % 1300

VB:

(" & lngEndMth - lngStartMth) & ") * 100 + " & ((lngEndDay - lngStartDay) +
1300) Mod 1300

In the VB version, I'm simply doing all the math outside the string itself
and concat it within the string. Just for giggles, I changed so the math is
done inside the SQL string and changed mod back to %. Same results.

SELECT Date, Month, Day, Open, High, Low, Close FROM [AUS $, COMP-R AN_REV]
WHERE ((Month - 8)*100 + (Day - 1) + 1300) % 1300 <= -4 * 100 + 0 ORDER BY
((Month - 8)*100 + (Day - 1) + 1300) % 1300

  
#>In the the right-hand side of <= operator, you only apply  "mod 1300" to
#>((lngEndDay - lngStartDay) + 1300) part but not to
#>(lngEndMth - lngStartMth)*100 part.

Well, after going through all the above steps explaining what I did to
convert, it appears that I can SEE what you mean by the above statement.

Rather than this...

(" & lngEndMth - lngStartMth & ") * 100 + ((" & lngEndDay - lngStartDay & ")
+ 1300) % 1300 "

It should have been this...

((" & lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay - lngStartDay & ")
+ 1300) % 1300 "


I'm HAPPY to say that it WORKS!!!

Thanks for your help and time.

Cheers!

Rick












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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik

I'm not up on the terminology. I'm using Olaf Schmidt's VB SQLite binder.
He'd probably be better to answer this question than I.

:-)
Rick



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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rich Shepard  wrote:
> On Sun, 11 Oct 2009, Igor Tandetnik wrote:
>
>> I simply combine (month, day) pair into a single number - the same
>> way you combine two digits of a decimal number by multiplying the
>> first by 10 and adding the second. The multiplier doesn't have to be
>> 100 - any number greater than 31 will do.
>
>   Would it not be easier to use the DATE column and STRFTIME()?

It would be possible. Would it be easier? I'm not sure. Why don't you write an 
equivalent statement using this approach, then 
compare and contrast yours and mine?

> Specifying start and end dates as -MM-DD removes the need for
> such manipulations

As far as I can tell, the OP wants to only specify start and end month/day, and 
expects data from all years that fall into the 
specified window. E.g., for the span of 1/1 through 1/31, he wants all entries 
from January regardless of the year.

> and will work within a single year as well as over
> multi-year spans.

I don't quite see how you plan to pull that off. Please enlighten me.

>   I ask because that's the way I would approach the solution to the
> question Rick asked. The MONTH and DAY columns seem repetitive to me
> and a potential souce of loss of integrity. If those columns are
> filled by extracting the appropriate portions of the DATE column, why
> not use the latter itself?

Suppose dates where represented in the database as -MM-DD  - how would that 
help you solve the problem? Again, I suggest you 
show an example.

Igor Tandetnik 


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

Re: [sqlite] Need Help SQL

[Rich Shepard]

On Sun, 11 Oct 2009, Igor Tandetnik wrote:

> I simply combine (month, day) pair into a single number - the same way you
> combine two digits of a decimal number by multiplying the first by 10 and
> adding the second. The multiplier doesn't have to be 100 - any number
> greater than 31 will do.

Igor,

   Would it not be easier to use the DATE column and STRFTIME()? Specifying
start and end dates as -MM-DD removes the need for such manipulations
and will work within a single year as well as over multi-year spans.

   I ask because that's the way I would approach the solution to the question
Rick asked. The MONTH and DAY columns seem repetitive to me and a potential
souce of loss of integrity. If those columns are filled by extracting the
appropriate portions of the DATE column, why not use the latter itself?

Curious minds want to know,

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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

Rick Ratchford wrote:
> #>There is a slight complication with the case where endDate <
> startDate, #>where you want to wrap around to next year. For that, I
> simply need to #>perform calculations modulo a large number - any
> number greater than the #>representation of 12/31 in my scheme. 1300
> is one such number. #>
> #>Igor Tandetnik
> 
> That slight complication is still there though. It works when the
> start < end, but it fails the other way around.

Precisely how does it fail? Seems to work for me:

create table t(month integer, day integer, price integer);
insert into t values(1, 10, 1);
insert into t values(5, 10, 2);
insert into t values(12, 10, 3);

-- select rows between 10/1 and 3/31
select Month, Day, Price
from t, (select 10 as StartMonth, 1 as StartDay, 3 as EndMonth, 31 as EndDay)
where ((Month - StartMonth)*100 + (Day - StartDay) + 1300) % 1300 <
   ((EndMonth - StartMonth)*100 + (EndDay - StartDay) + 1300) % 1300
order by ((Month - StartMonth)*100 + (Day - StartDay) + 1300) % 1300;
12|10|3
1|10|1

Isn't that what you wanted?

Igor Tandetnik


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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

#>There is a slight complication with the case where endDate < startDate,
#>where you want to wrap around to next year. For that, I simply need to
#>perform calculations modulo a large number - any number greater than the
#>representation of 12/31 in my scheme. 1300 is one such number.
#>
#>Igor Tandetnik

That slight complication is still there though. It works when the start <
end, but it fails the other way around.

Pretty clever with the numbering system though. And thanks for suggestion.

:-)

Rick



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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

Igor,

Okay, I give.

What do the 100 and 1300 values signify? I am completely baffled at how you
arrived at these values and what they do.

Thanks.

Rick 
 
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Friday, October 09, 2009 6:46 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Need Help SQL
#>
#>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



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

Re: [sqlite] Need Help SQL

[Rick Ratchford]

Thank you Igor. I will. :-)

Cheers!
 
Rick 
 
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Friday, October 09, 2009 6:46 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Need Help SQL
#>
#>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



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

Re: [sqlite] Need Help SQL

[Igor Tandetnik]

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

[sqlite] Need Help SQL

[Rick Ratchford]

I'm trying to correctly word an SQL statement.

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

Example:

Starting Month = 3
Starting Day = 12

Ending Month = 7
Ending Day = 5

The recordset created needs to return the data in these columns:

Month   Day   Price
===
3  12  24.50
3  12  12.34
3  12  33.01
3  12   8.76
3  13  11.72
3  13  77.55
3  13  12.00
.
.
7   5   99.87
7   56.22
7   5   54.61


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.

I'm still pretty green on working out these SQL statements. I'm hoping some
suggestions will help.

Thanks.

Rick



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