Re: [sqlite] Reverse Referencing Rows

[Rick Ratchford]

This is really not as difficult as you might think.

If the database contains 10 years of price data, where trading only happens
Monday-Friday excluding Holidays, and you were able to extract DATE RANGES
as SETS from this data, it would not be difficult to determine if the SET is
complete or not.

For example, if the DATE RANGE is NOV 05 to FEB 05, you would know you had
the complete set of data if you had data leading UP TO NOV 05 and also data
following FEB 05.

So for example, if the first DATA SET based on the NOV 05 to FEB 05
requirement actually started on NOV 07 because no trading actually occurred
on NOV 05 and 06, you'd know this was the case and not that your data simply
started in the middle of the SET itself if you had data leading up to NOV 07
to begin with, say NOV 02, 03, 04.

And in any case, this issue really is only at the BEGINNING and END of the
data where you may get PARTIAL SETS. If the data started somewhere between
the DATE RANGE, and the data ended also somewhere in the middle, it would
not be difficult to determine this. One other very important fact exists,
and that is that stock data, excluding weekends, never stops trading for 3
straight days. So if you are missing more than say 5 days in front of the
first DATA SET, as that is where the data actually starts, you'd know it was
missing a complete DATA SET for your first SET from this data.

Keep in mind that the whole exercise is to extract ONLY the data that falls
between two dates selected by the user from all the data available. Each SET
(from start date to end date) will eventually be numbered as SET 1, 2, 3,


Then one can compare all odd sets, even sets, all sets, first 5 sets, last
10 sets, etc. Apples to apples. The 'year' isn't the reference, because if
you went from NOV 05 to FEB 05, you'd be going from one year into the next.
The data must be in sets from NOV 05 to FEB 05, not the other way around, if
that is what the user selects.

Hope this makes it clearer.

Cheers!
 
Rick
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-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 if the set is complete, there would clearly be 
#>data rows 
#>> PRIOR to the start date and data rows that FOLLOW the end 
#>date. This 
#>> is how I'd determine that a set is complete with all available data 
#>> for those 'sample date windows'.
#>
#>This makes no sense to me.  To determine if I have data for 
#>each workday within a period I need a definition of which 
#>days within the period are workdays.  Either a table of all 
#>workdays, or a list of all non-workdays, or some other way of 
#>determination which is in a form SQL can access.  In the 
#>financial systems I used to work with you'd usually find a 
#>TABLE which listed each day and it's workday number.
#>
#>So if the daynumber of today last year was, say, 88,000 the 
#>daynumber of today might be 88,250.  To determine if I had 
#>data for every day in the last year I'd subtract 88,000 from 
#>88,250 and then check to see whether I had data for 250 
#>different days within the period.
#>
#>Simon.
#>___
#>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] Reverse Referencing Rows

[Simon Slavin]

On 11 Nov 2009, at 7:12pm, Rick Ratchford wrote:

> To determine if the set is complete, there would clearly be data rows PRIOR
> to the start date and data rows that FOLLOW the end date. This is how I'd
> determine that a set is complete with all available data for those 'sample
> date windows'.

This makes no sense to me.  To determine if I have data for each workday within 
a period I need a definition of which days within the period are workdays.  
Either a table of all workdays, or a list of all non-workdays, or some other 
way of determination which is in a form SQL can access.  In the financial 
systems I used to work with you'd usually find a TABLE which listed each day 
and it's workday number.

So if the daynumber of today last year was, say, 88,000 the daynumber of today 
might be 88,250.  To determine if I had data for every day in the last year I'd 
subtract 88,000 from 88,250 and then check to see whether I had data for 250 
different days within the period.

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

Re: [sqlite] Reverse Referencing Rows

[Rick Ratchford]

Stock market data.

Stock market only trades on Business days and not weekends and holidays.

The database contains stock data. One row per trading day.

What I want to do is to get all the data from a start date to end date only
into individual sets.

To determine if the set is complete, there would clearly be data rows PRIOR
to the start date and data rows that FOLLOW the end date. This is how I'd
determine that a set is complete with all available data for those 'sample
date windows'.

I want them in SETS for comparison purposes, to later compare one SET to
another without any regard to what year (or dual years) the sets are derived
from.

In my code I intend to number each set from 1 to whatever number of total
sets there are.

That's why I'm trying to finalize the SQL statement to grab the SETS. From
there, I can do the rest with code.

I'm thinking that due to the limitations of SQL that it may be better that I
use what I have in SQL, shown below (with your help) with the addition of
ORDER BY Date..., and then strip off the partial set at either end using
code.

'This SQL statement will pull out the data in SETS. Ex: lngStartMth/Day
to lngEndMth/Day, even cross year divide.
sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" &
gsTableName & "] " & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
lngStartDay & ") + 1300) % 1300 <= ((" & _
lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
lngStartDay & ") + 1300) % 1300 " & _
"ORDER BY Date, Year, ((Month - " & lngStartMth & ")*100 + (Day
- " & lngStartDay & ") + 1300) % 1300"

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 Referencing Rows
#>
#>Rick Ratchford <r...@amazingaccuracy.com>
#>wrote: 
#>> Only business days are in the data sets. No weekends or Holidays.
#>> 
#>> So I was happy with the other sets because they ARE full sets.
#>> 
#>> A full set is ALL THE BUSINESS DAYS from the Start to End Date.
#>
#>Do you have a table that lists all the business days in a 
#>year, or something? Otherwise, I don't see how you can 
#>formally define the notion of a "complete set". And without 
#>formally defining it, you won't be able to produce a query to 
#>implement it.
#>
#>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] Reverse Referencing Rows

[Igor Tandetnik]

Rick Ratchford 
wrote: 
> Only business days are in the data sets. No weekends or Holidays.
> 
> So I was happy with the other sets because they ARE full sets.
> 
> A full set is ALL THE BUSINESS DAYS from the Start to End Date.

Do you have a table that lists all the business days in a year, or something? 
Otherwise, I don't see how you can formally define the notion of a "complete 
set". And without formally defining it, you won't be able to produce a query to 
implement it.

Igor Tandetnik


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

Re: [sqlite] Reverse Referencing Rows

[Rick Ratchford]

Only business days are in the data sets. No weekends or Holidays.

So I was happy with the other sets because they ARE full sets. 

A full set is ALL THE BUSINESS DAYS from the Start to End Date.

Cheers!
 
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 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   03
#>> #>> 01/04/1989   1989   01   04
#>> #>> 01/05/1989   1989   01   05
#>> #>>
#>> #>> As you can see, the first set has a problem It goes from 
#>> #>December 28, #>> 1988 to January 05, 1988, rather than 
#>January 05, 
#>> 1989 #>like it should #>> for the first SET.
#>> #>
#>> #>Actually, it only seems this way due to the sorting 
#>order. If #>you 
#>> just do "ORDER BY Year, Month, Day" you'll see what's 
#>#>going on. You 
#>> have one set going from 12/28/87 to 01/05/88 #>(which just 
#>happens to 
#>> be incomplete as you have no records #>in 1987), and 
#>another unrelated 
#>> set going from 12/28/88 to #>01/05/89. Your overcomplicated 
#>ORDER BY 
#>> clause causes these #>two sets to interleave.
#>> 
#>> This would then bring up another issue. Only COMPLETE SETS 
#>are needed, 
#>> not partial ones.
#>
#>Define "complete set". You seem to be happy with 1988-1989 
#>one, even though it's missing 3 days out of 9. Sounds pretty 
#>partial to me.
#>
#>> #>> I fugure the way to correct this issue is to make sure 
#>that each 
#>> ROW #>> (record) has a DATE that is greater than the last ROW.
#>> #>
#>> #>So, just say that in ORDER BY.
#>> 
#>> Is that what I did when I added "Date" to my ORDER BY?
#>
#>Which part of "ORDER BY Year, Month, Day" do you find unclear?
#>
#>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] Reverse Referencing Rows

[Igor Tandetnik]

Rick Ratchford 
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   03
> #>> 01/04/1989   1989   01   04
> #>> 01/05/1989   1989   01   05
> #>>
> #>> As you can see, the first set has a problem It goes from
> #>December 28,
> #>> 1988 to January 05, 1988, rather than January 05, 1989
> #>like it should
> #>> for the first SET.
> #>
> #>Actually, it only seems this way due to the sorting order. If
> #>you just do "ORDER BY Year, Month, Day" you'll see what's
> #>going on. You have one set going from 12/28/87 to 01/05/88
> #>(which just happens to be incomplete as you have no records
> #>in 1987), and another unrelated set going from 12/28/88 to
> #>01/05/89. Your overcomplicated ORDER BY clause causes these
> #>two sets to interleave.
> 
> This would then bring up another issue. Only COMPLETE SETS are
> needed, not partial ones.

Define "complete set". You seem to be happy with 1988-1989 one, even though 
it's missing 3 days out of 9. Sounds pretty partial to me.

> #>> I fugure the way to correct this issue is to make sure that each
> ROW #>> (record) has a DATE that is greater than the last ROW.
> #>
> #>So, just say that in ORDER BY.
> 
> Is that what I did when I added "Date" to my ORDER BY?

Which part of "ORDER BY Year, Month, Day" do you find unclear?

Igor Tandetnik

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

Re: [sqlite] Reverse Referencing Rows

[Rick Ratchford]

#>> 
#>> 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   03
#>> 01/04/1989   1989   01   04
#>> 01/05/1989   1989   01   05
#>> 
#>> As you can see, the first set has a problem It goes from 
#>December 28, 
#>> 1988 to January 05, 1988, rather than January 05, 1989  
#>like it should 
#>> for the first SET.
#>
#>Actually, it only seems this way due to the sorting order. If 
#>you just do "ORDER BY Year, Month, Day" you'll see what's 
#>going on. You have one set going from 12/28/87 to 01/05/88 
#>(which just happens to be incomplete as you have no records 
#>in 1987), and another unrelated set going from 12/28/88 to 
#>01/05/89. Your overcomplicated ORDER BY clause causes these 
#>two sets to interleave.

This would then bring up another issue. Only COMPLETE SETS are needed, not
partial ones. In another post, I stated adding "Date" to my ORDER BY so that
at least the ORDER would be chronological. However, it still leaves the
issue of the partial unwanted SET.

>
#>> I fugure the way to correct this issue is to make sure that each ROW
#>> (record) has a DATE that is greater than the last ROW.
#>
#>So, just say that in ORDER BY.

Is that what I did when I added "Date" to my ORDER BY? I'm assuming it is.
But correct me if not.

#>
#>> Is it possible to have the SQL statement above do this as well?
#>
#>Yes.

But then, lies the mystery. :-)

Thanks.
Rick


#>
#>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] Reverse Referencing Rows

[Olaf Schmidt]

"Rick Ratchford" 
schrieb im Newsbeitrag news:dbfb2606d0c1448b930064474496a...@dolphin...
> 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 - " &
> lngStartDay & ") + 1300) % 1300 <= ((" & _
> lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
> lngStartDay & ") + 1300) % 1300 " & _
> "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - "
&
> lngStartDay & ") + 1300) % 1300"
>
> The only problem I have is that there are some stray rows that are not
> wanted.

Did you already tried the small function I've posted
into the VB-database-group (which was based on
Igors original suggestion)?

This also shows, how to work with a CommandObject -
(code is included again at the end of this post).

It enhances the *Where* clause (not the Order By),
to get rid of your "stray-rows" in the very first year
of a "year-crossing-set").

Function GetMonthDayRange(TableName As String, _
  ByVal MStart&, ByVal DStart&, _
  ByVal MEnd&, ByVal DEnd&, _
  ByVal InclYearSort As Boolean) As cRecordset
Dim SQL As String

  'a Cmd-SQL does *not* contain any direct "VarConcats", only @Placeholders
  SQL = "SELECT Date,Month,Day,Open,High,Low,Close FROM @TblName" & _
" WHERE (((mon...@mstart)*100 +  (d...@dstart)+ 1300)% 1300" & _
" <=(( @me...@mstart)*100 +(@de...@dstart)+ 1300)% 1300)" & _
" AND (Year > (Select Min(Year) From @TblName) " & _
" OR (Month*100+Day >= @mstart*1...@dstart)) "

  'enhance the above SQL about the appropriate, different Order By Clauses
  If InclYearSort Then
SQL = SQL & "ORDER BY Year, Month, Day"
  Else
SQL = SQL & "ORDER BY ((mon...@mstart)*100+(d...@dstart)+ 1300)% 1300"
  End If


  With Cnn.CreateSelectCommand(SQL) 'create a Select-Command-Object
'now we replace the @PlaceHolders with the current Var-Content,
'which we've got passed in our Function-Parameters
.ReplColumnOrTableName !TblName, TableName
.SetInt32 !MStart, MStart
.SetInt32 !DStart, DStart
.SetInt32 !MEnd, MEnd
.SetInt32 !DEnd, DEnd

Set GetMonthDayRange = .Execute 'the Cmd.Execute returns a Recordset
  End With
End Function

Olaf



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

Re: [sqlite] Reverse Referencing Rows

[Igor Tandetnik]

Rick Ratchford wrote:
> For example, say that I want to run this SQL statement to pull out SETS that
> start with a MM/DD of 12/28 to 01/05. That means, each 'set' would be from
> December 25 to January 05, which means that each 'set' will cross a year end
> date (where the year value increments by 1).
> 
> While the above SQL statement will do this, to a point, the first set is
> usually not correct. It will look like this:
> 
> 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   03
> 01/04/1989   1989   01   04
> 01/05/1989   1989   01   05
> 
> As you can see, the first set has a problem It goes from December 28, 1988
> to January 05, 1988, rather than January 05, 1989  like it should for the
> first SET.

Actually, it only seems this way due to the sorting order. If you just do 
"ORDER BY Year, Month, Day" you'll see what's going on. You have one set going 
from 12/28/87 to 01/05/88 (which just happens to be incomplete as you have no 
records in 1987), and another unrelated set going from 12/28/88 to 01/05/89. 
Your overcomplicated ORDER BY clause causes these two sets to interleave.

> I fugure the way to correct this issue is to make sure that each ROW
> (record) has a DATE that is greater than the last ROW.

So, just say that in ORDER BY.

> Is it possible to have the SQL statement above do this as well?

Yes.

Igor Tandetnik


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

Re: [sqlite] Reverse Referencing Rows

[Rick Ratchford]

To add to my last post shown below, what I've done is added the "Date" to
the ORDER BY, thus putting the unwanted rows at the very top. It doesn't
remove it, but it does allow for stripping it off easier when transferring
to an array if that is the best way to go.

sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" &
gsTableName & "] " & _
"WHERE ((Month - " & lngStartMth & ")*100 + (Day - " &
lngStartDay & ") + 1300) % 1300 <= ((" & _
lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay -
lngStartDay & ") + 1300) % 1300 " & _
"ORDER BY Date, Year, ((Month - " & lngStartMth & ")*100 + (Day
- " & lngStartDay & ") + 1300) % 1300"

Cheers!
 
Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
#>Sent: Tuesday, November 10, 2009 10:59 PM
#>To: 'General Discussion of SQLite Database'
#>Subject: [sqlite] Reverse Referencing Rows
#>
#>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 
#>- " & lngStartDay & ") + 1300) % 1300 <= ((" & _
#>lngEndMth - lngStartMth & ") * 100 + (" & 
#>lngEndDay - lngStartDay & ") + 1300) % 1300 " & _
#>"ORDER BY Year, ((Month - " & lngStartMth & 
#>")*100 + (Day - " & lngStartDay & ") + 1300) % 1300"
#>
#>The only problem I have is that there are some stray rows 
#>that are not wanted.
#>
#>For example, say that I want to run this SQL statement to 
#>pull out SETS that start with a MM/DD of 12/28 to 01/05. That 
#>means, each 'set' would be from December 25 to January 05, 
#>which means that each 'set' will cross a year end date (where 
#>the year value increments by 1).
#>
#>While the above SQL statement will do this, to a point, the 
#>first set is usually not correct. It will look like this:
#>
#>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   03
#>01/04/1989   1989   01   04
#>01/05/1989   1989   01   05
#>
#>As you can see, the first set has a problem It goes from 
#>December 28, 1988 to January 05, 1988, rather than January 
#>05, 1989  like it should for the first SET.
#>
#>I fugure the way to correct this issue is to make sure that each ROW
#>(record) has a DATE that is greater than the last ROW.
#>
#>Is it possible to have the SQL statement above do this as well? 
#>
#>I know I can loop through this recordset, transferring each 
#>record to an array that has a date greater than the last 
#>record (row) date value. It's an extra step and easy to program.
#>
#>I'm just curious if it was possible within the above SQL 
#>statement, and whether it would be worth doing it via the SQL 
#>or would perform better if I added the looping into array step.
#>
#>Thank you.
#>
#>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