#>> 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
