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
