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
