Re: [sqlite] Reverse Referencing Rows
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
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
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 #>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
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
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 #>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
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
#>> #>> 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
"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
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
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
[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