ite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
#>Sent: Wednesday, November 11, 2009 4:53 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Reverse Referencing Rows
#>
#>
#>On 11 Nov 2009, at 7:12pm, Rick Ratchford wrote:
#>
#>> To determine
Thanks.
Rick
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Wednesday, November 11, 2009 12:38 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Reverse Referencin
te.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Wednesday, November 11, 2009 11:44 AM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Reverse Referencing Rows
#>
#>Rick Ratchford <r...@amazingaccuracy.com>
#>wrote:
#>> #>
#>>
#>> Date | Year | Month | Day
#>>
#>> 12/28/1988 1988 12 28
#>> 12/29/1988 1988 12 29
#>> 12/30/1988 1988 12 30
#>> 01/04/1988 1988 01 04
#>> 01/05/1988 1988 01 05
#>> 12/28/1989 1989 12 28
#>> 12/29/1989 1989 12 29
#>> 01/03/1989 1989 01
StartMth & ") * 100 + (" & lngEndDay -
lngStartDay & ") + 1300) % 1300 " & _
"ORDER BY Date, Year, ((Month - " & lngStartMth & ")*100 + (Day
- " & lngStartDay & ") + 1300) % 1300"
Cheers!
Rick
#>
A while back, Igor gave me some help on pulling out mm/dd ranges (sets) from
my table.
This is the code that does that.
sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" &
gsTableName & "] " & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
quot;] GROUP BY Year in the statement.
This seems to do the trick.
Thanks ya'll.
:-)
Rick
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Tuesday, November 10, 2009 2:33 PM
#>To: sqlit
Suppose you had a column called SampleNumber.
And in this column, you might have...
1
1
1
2
2
2
2
3
3
4
4
4
5
5
5
5
etc.
How would you write the SQL statement that would return the maximum number
of a sample?
For example, if within the SampleNumber column, the SampleNumber 17 had more
[sqlite] Tackling the Date Scan SQL
#>
#>Try to change this:
#>
#>> "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " &
#>lngStartDay
#>> & ") + 1300) % 1300"
#>
#>to this:
#>
#>> "ORDER BY Year
With Igor's help, I have this SQL statement that pulls out records based on
a start and stop date.
"SELECT Year, Month, Day, Open, High, Low, Close FROM [" & gsTableName & "]
" & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") +
1300) % 1300 <= ((" & _
lngEndMth -
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
What I'm missing is a stiff drink.
Ouy!
I really bumbled this easy task. My one allowance this month, eh?
Cheers!
Rick
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
#>boun...@sqlite.org] On Behalf Of Jean-Christophe Deschamps
#>Sent: Monday,
% 1300 = 1400 - (1400 / 1300) * 1300 = 1400 - 1 * 1300 = 100
#>So you have indeed misinterpreted the modulo operator
#>http://en.wikipedia.org/wiki/Modulo_operation.
#>
#>Pavel
#>
#>On Mon, Oct 12, 2009 at 7:49 PM, Rick Ratchford
#><r...@amazingaccuracy.com> wrote:
#&
#>Hi Rick,
#>
#>
#>You seem to be misinterpreting the semantics of the % (modulo) operator.
#>
#> X % Y returns the (integral) remainder of the Euclidean division of
#>X by Y (both integers).
#>
#>Now things should be clearer.
#>
Hi.
I didn't misinterpret the %. I understand how it works. :-)
Igor-
While your math works, I'm still puzzled as to how you came up with this.
((Month - StartMth)*100 + (Day - StartDay) + 1300) % 1300 <=
((EndMth - StartMth)* 100 + (EndDay - StartDay) + 1300) % 1300
With this...
((Month - StartMth)*100 + (Day - StartDay) + 1300) % 1300
The result will
#>> 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 + " &
#>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
#>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
#>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 star
ite] 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
#&g
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
#>Rick Ratchford wrote:
#>> #>Try
#>> #>
#>> #>date(max(Date), 'weekday 5')
#>>
#>> It's likely I'm not using it correctly, because it returns
#>nothing. :(
#>
#>I mean, replace "Date" in your statement with this expression. As i
#>> What I ended up with are the number of days per each week (row) and
#>> the last date for that week that had data.
#>
#>Yes, of course. What did you expect?
Exactly what I got. :)
#>
#>> Here are the last few rows.
#>>
#>> count(*) max(Date)
#>> =
#>> 5 2009-06-26
#>> 4
.org
#>Subject: Re: [sqlite] Date Cutoff Statement
#>
#>Rick Ratchford wrote:
#>> SELECT Date FROM MyTable GROUP BY Year, Week
#>>
#>> This creates a recordset that groups all my prices into 'weekly'
#>> prices. In other words, each row represents the High,
#>>
#>> What I want to do is modify this SELECT statement so that the rows
#>> returned do not go past a certain date. Let's call it dStopDate.
#>>
#>> If I have dStopDate = '2009-28-07'
#>
#>Did you mean '2009-07-28' ?
#>
Yes.
#>> for example, then the last row I want to return is
I'm stuck on a problem that is halting my project. I hope someone can help
on this one as I think it's a SQL related question.
MyTable contains the Date, Year, Week (and other columns).
Week is a week number.
MyTable holds my stock prices for each trading day.
I've omited the price data
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>Sent: Monday, August 03, 2009 5:18 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>
qlite.org] On Behalf Of Rich Shepard
#>Sent: Monday, August 03, 2009 3:45 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>On Mon, 3 Aug 2009, Rick Ratchford wrote:
#>
#>> The native Date in a table without any additional express
; '"& sDateTemp & "'"
#>
#>Without the quote, I think sqlite is subtracting the day from
#>the month from the year, and comparing that number with the
#>Date string.
#>
#>David
#>
#>
#>--- On Mon, 8/3/09, Rick Ratchford <r...@amazingaccuracy.c
-boun...@sqlite.org] On Behalf Of Rick Ratchford
#>Sent: Monday, August 03, 2009 1:45 PM
#>To: 'General Discussion of SQLite Database'
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>That's the clincer.
#>
#>The resulting DATE column is actually the format of the
#>
, August 03, 2009 1:38 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>Rick Ratchford wrote:
#>> The Date is being stored as -mm-dd. Note the "Format$(Date,
#>> '-mm-dd') as Date" that assures this.
#>
#>The "Dat
ormat
#>'-mm-dd' in database.
#>Without it comparison will not work.
#>
#>Pavel
#>
#>On Mon, Aug 3, 2009 at 2:19 PM, Rick
#>Ratchford<r...@amazingaccuracy.com> wrote:
#>> This works:
#>>
#>> SQLString = "SELECT Format$(Date, '-mm-dd') a
This works:
SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP BY
Year, Month,
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin
#>Sent: Sunday, July 26, 2009 8:43 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Little Help on SQL
#>
#>On 27/07/2009
Seems my answer was a simple one after all.
I only needed to be aware of GROUP BY.
Found it though. GROUP BY DayNum. Works.
:-)
Rick
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
#>
Hello.
I'm not yet there in my study of SQL with Rick's book, so I thought someone
might help me with an SQL query I need pretty quick.
I have a TABLE with a column of Day Numbers (1 to 366) called DayNum.
I already have my SQL to where it will extract a number of complete years
from January
LOL!
I didn't intend for the thread to get out of hand like this.
Anyway, the question was answered regardless. And to EVERYONE who had a
comment, thanks.
The book is VERY THICK. Just the way I like them. For the cost, it better
be.
Why such a book on SQL for SQLite when SQLite is 'easy'? I
Message-
#>#>>From: sqlite-users-boun...@sqlite.org
#>#>>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>#>>Sent: Saturday, July 18, 2009 8:00 PM
#>#>>To: General Discussion of SQLite Database
#>#>>Subject: Re: [sqlite] Th
inal Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>Sent: Saturday, July 18, 2009 8:00 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] The SQL Guide to SQLite
#>
#>On Sat, 18 Jul 2009, R
pretty common in academic papers, actually.
#>
#> -T
#>
#>
#>On 7/18/09, Rick Ratchford <r...@amazingaccuracy.com> wrote:
#>> Yes. You are correct. That is what they are.
#>>
#>> Thanks for pointing this out. It probably should have been
#>made clear
sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Saturday, July 18, 2009 10:42 AM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] The SQL Guide to SQLite
#>
#>Rick Ratchford wrote:
#>> I just received my copy
Greetings!
I just received my copy of the new book "The SQL Guide to SQLite" by Rick F.
van der Lans.
There are many references within the book that are contained in square
brackets and some sort of keyword or code.
Example: "...written about SQLite; see for example [NEWM05] and [OWEN06]."
#>"Simple" is relative - as you write yourself - your App
#>already performs faster using SQL for the right things - and
#>that don't have to be only "simple queries" - what you
#>already do with all these nice Group By queries - directly
#>delivering weekly or monthly stock-data, derived from
#>I was trying to figuring out if you are doing something of
#>graph data analysis, I do it almost everyday in our Stock
#>Trader applications...
#>I never did this way (direct SQL), cause our graph series
#>data sources are implement throught a common interface, that
#>could be a SQL query, a
#>"Rick Ratchford" <r...@amazingaccuracy.com> schrieb im
#>Newsbeitrag news:c9ce387e92004e7b9c16ddaa2bd36...@dolphin...
#>
#>> So modifying TmpTable, which will still be needed for other
#>> procedures, is not preferred. It would be great if a
#>records
;On 10 Jul 2009, at 11:36pm, Rick Ratchford wrote:
#>
#>> I understand what you're saying Simon.
#>
#>Sorry, Rick. I didn't mean to rail on you personally. Your
#>post happened to be the one that triggered me to post the
#>rant. I understand your reasoning and don't think you
#>
#>This will create another table TmpTable (tax, direction),
#>using the values from the table MarketTable:
#>
#>create table TmpTable as
#>select tax,
#>(select
#> case when b.tax < MarketTable .tax
#> then "Up"
#> when b.tax>=MarketTable .tax
#> then "Down"
#> else null
#> end
#>
#>On 10 Jul 2009, at 9:31pm, Rick Ratchford wrote:
#>
#>> After examining the above, it appears that what this does is modify
#>> the table itself. So I suppose then that it is not possible
#>to create
#>> a recordset instead that meets what I'm trying to do. If
#&
Seems there was a question in your reply I didn't catch the first time.
>
#>What do you mean by "previous one"? Records in a table don't
#>have any implicit ordering. Do you have some kind of a
#>timestamp field that imposes the order?
The table, each time, has been in order from oldest Date
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Friday, July 10, 2009 2:50 PM
#>To: sqlite-users@sqlite.org
#>Subject: Re: [sqlite] Is it Possible in SQL...
#>
Is it possible, using SQL, to do comparisions across records?
Suppose that you had 1 field called TAX and you wanted to compare each one
to the previous one.
Record 1 = TAX (45)
Record 2 = TAX (65)
Record 3 = TAX (22)
So using the data above, I would want to compare Record 2 (65) to Record 1
Cheers!
Rick Ratchford
ProfitMax Trading Inc.
http://www.amazingaccuracy.com
#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
#>Sent: Thursday, July 09, 2009 1:15 PM
#>To: sqlite-u
Rick Ratchford wrote:
> Okay, this worked, but I have NO IDEA why.
>
>SQLString = "SELECT min(Year) FROM TmpTable " & _
>"WHERE Month=1 UNION " & _
>"SELECT max(Year) FROM TmpTable " & _
>
Rick Ratchford wrote:
> Okay, this worked, but I have NO IDEA why.
>
>SQLString = "SELECT min(Year) FROM TmpTable " & _
>"WHERE Month=1 UNION " & _
>"SELECT max(Year) FROM TmpTable " & _
>
While this returned the correct answers:
1988
2008
What I don't understand is why it didn't simply return:
1988
1988
Since there is at least 15 or more days in Month=1 (Jan).
Anyone?
Thanks.
Rick
Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sql
I've been trying all kinds of different ways to accomplish the following,
and my head is spinning.
Problem: How do you return from the DB just the YEAR of the first and last
YEAR that had dates from 1st week of January to last week of December?
Clarification: Suppose you had a database that
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 requ
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 CO
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
ennis Cote
Sent: Tuesday, July 07, 2009 8:22 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query by Day
Rick Ratchford wrote:
> So what I need to do then is to make the return of strftime of type INT.
>
> Can CAST(strftime('%d', Date), INTEGER) be used in this context, or
: Re: [sqlite] Query by Day
2009/7/6 Rick Ratchford <r...@amazingaccuracy.com>:
> Greetings!
>
Hi Rick,
> I'm having trouble with what I thought would be a simple SQL query.
>
> SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as
>
Greetings!
I'm having trouble with what I thought would be a simple SQL query.
SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
IsSwingBtm1 as Btms " & _
"FROM TmpTable WHERE Day = 11"
I'm trying to create a recordset where the only records returned
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Friday, July 03, 2009 9:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL Query Question
Rick Ratchford wrote:
> For example, say I have 15 Dates already extracted by a p
Perhaps to solve the larger problem I have in a different post, I have a
question on retrieving records.
How do you request a fixed number of records starting from a location in the
data based searched for, all in one SQL statement?
"SELECT Date = '2009-01-01' ...plus the next x number of
tion
On 4/07/2009 9:01 AM, Simon Slavin wrote:
> On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote:
>
>> Suppose my 15 Dates are:
>>
>> 2009-03-03
>> 2008-11-05
>> 2008-07-10
>> ...
>> ...
>> 2007-07-23
>>
>>
>> Assuming this is
Can someone help me with this?
Suppose you have a Table/Recordset that has these columns:
Date (string)
Color (string)
Offset (long)
I want to extract from this Table/Recordset 40 contiguous records from 15
locations within the dataset, each referenced by the Date.
For example, say I have
ed, Jul 1, 2009 at 6:05 PM, Rick Ratchford<r...@amazingaccuracy.com>
wrote:
> I'm using a VB wrapper, and so I run this by...
>
> Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name =
> 'DeltaGrid'"
>
> Thing is, I don't know where to check for the r
there is a table called 'Foo' in the
sqlite_master, where does one check for that 1 or 0?
Thanks!
Rick
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
Sent: Wednesday, July 01, 2009 7:06 PM
To: 'General
DROP TABLE Foo;
-- It's okay to execute this command, even if Foo does not exist
already.
Hello David. Thanks for your reply.
The above "Drop Table" created an error when I tried to run it when no table
existed.
CREATE TABLE Foo ( ... );
Forgive my novice ignorance. Although I
Language: VB6
In my project, I create a Table that holds specific information based on a
User's selection.
When the user runs a new selection, my procedure that creates this table is
run again to recreate the table but with new information.
However, the problem I have is that since the
Hello.
I'm using SQLite with a VB wrapper (dhSQLite) for VB6.
The following SQL string works fine for putting together a recordset where
the DATE field contains only the date of the last day of each month.
SQLString = "SELECT date(Date,'start of month','+1 month','-1 day') as
Date, " & _
71 matches
Mail list logo
