Re: [sqlite] Date Cutoff Statement
#>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 in #> #>SELECT date(max(Date), 'weekday 5') FROM MyTable GROUP BY Year, Week; # It works. The last time I tried it I got nada. So I must have typed it in wrong. #>> #>> Is there an answer to my problem somewhere in the above output? #>> #> #>> #>No, not directly. Teach a man to fish, and all that. #>> #>> Completely understand. Just wish my fishing pole was a little more #>> forgiving. :) #> #>An interesting specimen of a double entendre here. #> #>Igor Tandetnik "double-entendre"?. I had to look that up. Was a reference to "Teach a man to fish", where one needs good working tools to get the job done. My 'tools' (mental-my mind) needs a bit of work. That's the only part of the anatomy I was referring to. LOL! I had written a loop and had it check each date to make sure it was Friday date. When it was not, I directly changed it in the recordset. This worked also, but is not as clean and efficient as using this that function in the SQL. Thanks for your help Igor. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
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 in SELECT date(max(Date), 'weekday 5') FROM MyTable GROUP BY Year, Week; > #>> Is there an answer to my problem somewhere in the above output? > #> > #>No, not directly. Teach a man to fish, and all that. > > Completely understand. Just wish my fishing pole was a little more > forgiving. :) An interesting specimen of a double entendre here. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
#>> 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 2009-07-02 #>> 5 2009-07-10 #>> 5 2009-07-17 #>> 5 2009-07-24 #>> 5 2009-07-31 #>> 1 2009-08-03 #>> #>> Note that each of these dates belongs to the FRIDAY of that week, #>> except for 2009-07-02 and 2009-08-03. #> #>Is this surprising? Nope. :) #> #>> This is the problem. While week 2009-08-007 is not yet #>complete (it is #>> in progress), the prior dates need to be FRIDAY dates even if the #>> count < 5. #> #>Try #> #>date(max(Date), 'weekday 5') It's likely I'm not using it correctly, because it returns nothing. :( #> #>For more details, see http://www.sqlite.org/lang_datefunc.html Read it. Unfortunately it doesn't help dummies who aren't sure where in the statement the darn thing should go. LOL! #>> I was aware of this when the data was displayed earlier. #>I'm not sure #>> what is 'revealed' other than what is stated above. #> #>Well, you appeared surprised that you weren't getting all #>Friday dates. Surprised? Mixed reviews. Grouping my daily data into weekly groups was a big deal for me when Olaf showed that. I used to do this through lots of code loops, converting daily data into weekly data. Then here comes this SQL statement and viola! You might say I was a bit disappointed to find it didn't account for weeks missing data for Friday. #>You claimed you couldn't understand why an addition of a #>WHERE clause changed the output the way it did. I hoped the #>demonstration of a "truncated" group would help you "put your #>finger on your error". I didn't understand why the WHERE didn't allow me to stop creating 'weekly' rows up to a certain date (and not including). It turned out that my head was still not wrapped around the GROUP thing. #>In any case, you do seem to possess a clearer understanding #>of the issue at this time, whether due to, in spite of, or #>independently of my efforts. You left off "with the addition of my efforts". It's been a GROUP help thing (pun intended). And I really do appreciate your comments, help and time. :) #> #>> Is there an answer to my problem somewhere in the above output? #> #>No, not directly. Teach a man to fish, and all that. Completely understand. Just wish my fishing pole was a little more forgiving. :) #>> Is there perhaps some SQL command that based on 'count' if less than #>> 5 the #>> difference can be added to the date before returning it in the #>> recordset? #> #>Well, you could do something like #> #>date(max(Date), (5 - count(*)) || ' days') #> #>but that won't work right if you are missing, say, Tuesday data. #> #>Igor Tandetnik And that wouldn't work for me since there are days prior to Friday that may not have data due to Holidays. It's starting to look like I'm going to have to loop through my recordset after it has been created, check each date, and if not a Friday, change it there. Thanks! Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
Rick Ratchford wrote: > 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? > Here are the last few rows. > > count(*) max(Date) > = > 5 2009-06-26 > 4 2009-07-02 > 5 2009-07-10 > 5 2009-07-17 > 5 2009-07-24 > 5 2009-07-31 > 1 2009-08-03 > > Note that each of these dates belongs to the FRIDAY of that week, > except for 2009-07-02 and 2009-08-03. Is this surprising? > This is the problem. While week 2009-08-007 is not yet complete (it > is in > progress), the prior dates need to be FRIDAY dates even if the count > < 5. Try date(max(Date), 'weekday 5') For more details, see http://www.sqlite.org/lang_datefunc.html > I was aware of this when the data was displayed earlier. I'm not sure > what > is 'revealed' other than what is stated above. Well, you appeared surprised that you weren't getting all Friday dates. You claimed you couldn't understand why an addition of a WHERE clause changed the output the way it did. I hoped the demonstration of a "truncated" group would help you "put your finger on your error". In any case, you do seem to possess a clearer understanding of the issue at this time, whether due to, in spite of, or independently of my efforts. > Is there an answer to my problem somewhere in the above output? No, not directly. Teach a man to fish, and all that. > Is there perhaps some SQL command that based on 'count' if less than > 5 the > difference can be added to the date before returning it in the > recordset? Well, you could do something like date(max(Date), (5 - count(*)) || ' days') but that won't work right if you are missing, say, Tuesday data. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
"Igor Tandetnik" schrieb im Newsbeitrag news:h584q5$jo...@ger.gmane.org... > You've truncated the last group short, so a different row from that > "incomplete" group accidentally happened to be chosen. Yep - therefore the recommendation in the VB-newsgroup, to better rely on the Having-clause (performance is not that much an issue in Ricks case). > Try > > SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week Ah yes - good catch (the Max(Date) term). Although the SQLite-engine behaves relative "stable", regarding "which value is filled into a Column that is part of an Group By-construct", when such a column is specified without an aggregate-expression. Currently! (yes, nothing to rely on) it is the last value, that "reaches the group", probably because the (temporarily used) sorter, which stores the incoming Rows "sorting whilst adding" (according to the Group By-clause), shows a "stable" sort- behaviour, not changing the "first-level, incoming order" which is determined by the RowID of the underlying table itself, as I see it. And Ricks table is built with increasing (auto) RowIDs, "in sync" to the (already sorted) incoming Trading- days/dates - and their "derived" Year, Month, Week, etc. Columns. So I think Rick will not see a difference regarding the reported Date between: SELECT count(*), max(Date) As Date FROM MyTable GROUP BY Year, Week and SELECT count(*), Date FROM MyTable GROUP BY Year, Week or for better comparison: SELECT count(*), max(Date) As MaxDate, Date FROM MyTable GROUP BY Year, Week But without doubt, he should change his current query to the Max(Date) aggregate to be on the safe side. That leads me to a different (somewhat OT-question in the context of *this* thread)... I recently noticed this entry in the SQLite-tktview: http://www.sqlite.org/cvstrac/tktview?tn=3979 And want to implement a fast sorter for SQLite, to become more familiar with the C-language. My question to you experts (including David, who already looked after that ticket) - is there already "work in progress" or were there already attempts, to write at least a "temporary only sorter", which handles all these "throw-away after delivery" Distinct - and Group By, ... etc. cases? Is it possible at all, to write such a thing without tight integration into SQLites VDBE? If yes, is there already an interface-spec available, to handle such temporary sorts with different (pluggable) sort-modules, in a similar way as the interface for e.g. the vfs' was designed and works? If no such interface-spec exists, would it be possible to design such a thing (at least "roughly", by an sqlite-expert) and publish it here or on the sqlite-site, before I start working on that, or do you say: "just be creative!". Any input on that is appreciated - links to already existing attempts or code-snippets too (not meaning the sorting- approach itself, more regarding the integration into SQLite). Regards, Olaf Schmidt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
Hello Igor. What I ended up with are the number of days per each week (row) and the last date for that week that had data. Here are the last few rows. count(*)max(Date) = 5 2009-06-26 4 2009-07-02 5 2009-07-10 5 2009-07-17 5 2009-07-24 5 2009-07-31 1 2009-08-03 Note that each of these dates belongs to the FRIDAY of that week, except for 2009-07-02 and 2009-08-03. This is because week ending 2009-07-03 had no data for Friday, so the last day that week with data was 2009-07-02. And since my data ends with today, being Monday 2009-08-03, we get that last date/row. This is the problem. While week 2009-08-007 is not yet complete (it is in progress), the prior dates need to be FRIDAY dates even if the count < 5. I was aware of this when the data was displayed earlier. I'm not sure what is 'revealed' other than what is stated above. Is there an answer to my problem somewhere in the above output? Is there perhaps some SQL command that based on 'count' if less than 5 the difference can be added to the date before returning it in the recordset? I'm a real greenie on this, so you might say I'm baffled. :-b Thanks. Rick #>-Original Message- #>From: sqlite-users-boun...@sqlite.org #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik #>Sent: Monday, August 03, 2009 9:01 PM #>To: sqlite-users@sqlite.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, Low, Close #>> prices for each week, and the date is the FRIDAY DATE of that week. #> #>If this happens, then only by accident. The value of Date #>reported for each group comes from an arbitrary row belonging #>to the group. There is no guarantee which row will be so chosen. #> #>> However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... #>> (and yes, assume Date and dStopDate are same format), my #>last record #>> returned is actually 07/27/2009 (the day before my #>dStopDate) rather #>> than my 'weekly' record of 07/24/2009. #> #>You've truncated the last group short, so a different row #>from that "incomplete" group accidentally happened to be chosen. #> #>Try #> #>SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week #> #>with and without WHERE clause. This might prove illuminating. #> #>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] 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, Low, Close > prices for each week, and the date is the FRIDAY DATE of that week. If this happens, then only by accident. The value of Date reported for each group comes from an arbitrary row belonging to the group. There is no guarantee which row will be so chosen. > However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... > (and yes, assume Date and dStopDate are same format), my last record > returned is actually 07/27/2009 (the day before my dStopDate) rather > than my 'weekly' record of 07/24/2009. You've truncated the last group short, so a different row from that "incomplete" group accidentally happened to be chosen. Try SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week with and without WHERE clause. This might prove illuminating. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
#>> #>> 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 #>07/24/2009, which #>> is the last week prior to my dStopDate. #>> #>> However, by adding WHERE Date < '" dStopDate "' prior to #>GROUP BY... #>> (and yes, assume Date and dStopDate are same format), my #>last record #>> returned is actually 07/27/2009 (the day before my #>dStopDate) rather than my 'weekly' #>> record of 07/24/2009. #> #>Did you mean "<=" instead of "<" ? No. "<" is correct. I do not want to include the dStopDate. Just the dates 'up to' the dStopDate. Anyway, Olaf answered this for me on a newsgroup. Appears that what I must do is to do my test on the GROUPED data. Therefore, the solution is to remove the WHERE and replace it with HAVING after the GROUP BY in my statement. This solved that problem (and now exposed a whole new one!). Seems my GROUPED data has a slight flaw. While most of the rows contain a FRIDAY date, there are some that have THURSDAYS date because no data was available for that particular Friday. This is a problem, since I must have nothing but FRIDAY dates for each row no matter if the data stopped on Thursday. LOL!! Thanks. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
On 4/08/2009 8:52 AM, Rick Ratchford wrote: > > 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' ? > for example, then the last row I want to > return is 07/24/2009, which is the last week prior to my dStopDate. > > However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... (and > yes, assume Date and dStopDate are same format), my last record returned is > actually 07/27/2009 (the day before my dStopDate) rather than my 'weekly' > record of 07/24/2009. Did you mean "<=" instead of "<" ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date Cutoff Statement
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 column references to keep this question as clear as possible. 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, Low, Close prices for each week, and the date is the FRIDAY DATE of that week. So the above statement might return the following (last 5 only): ... 07/03/2009 07/10/2009 07/17/2009 07/24/2009 07/31/2009 Note that each row in the Date column is a date that represents the last date of the week, Friday's date. 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', for example, then the last row I want to return is 07/24/2009, which is the last week prior to my dStopDate. However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... (and yes, assume Date and dStopDate are same format), my last record returned is actually 07/27/2009 (the day before my dStopDate) rather than my 'weekly' record of 07/24/2009. I can't put my finger on my error. Anyone have a suggestion? Thanks. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
