I have a TABLE that contains the following: Date (Monday to Friday only) Year (from Date, ie. YYYY) Month (from Date, ie. 01-12) WeekNum (1 to 53) High Price Low Price
I'm trying to create a WEEKLY Recordset (RsWeekly) from this TABLE that contains the following: WeekDate (Week-Date - this would be the Friday-Date that Date in TABLE is in.) Year (From Week-Date, ie. YYYY) WeekNum (1 to 52) High Price (Highest Price for the whole week) Low Price (Lowest Price for the whole week) The problem I run into here is that some Dates in TABLE fall in week 53. This is because the year started in the middle of the first week. When trying to create the RsWeekly recordset, where you only have 52 weeks (Week # 1 to 52), I'm having trouble getting the High/Low price of week 53 calculated along with week 1 of the following year, and for the date of week 53 to not show up as a valid week (since it is not a Friday date or complete week). For example, December 31 1974 falls on a Tuesday. The last complete week (#52) for 1974 is December 27 1974. Therefore, December 30 and 31 is really part of week #1 of the following year, which has the WeekDate of January 3, 1975. So in my RsWeekly recordset, the following is expected: 12/27/1974 (format: mm/dd/yyyy) 1974 (YYYY) 52 (1 -52 as week number) 1234.56 (ex. Highest Price of the week Mon-Fri) 950.00 (ex. Lowest Price of the week Mon-Fri) 01/03/1975 1975 01 5432.11 (ex. Highest price for week (Mon-Fri), where Mon and Tues is actually last two days 1974) 978.56 (Lowest price for week (Mon-Fri), where Mon and Tue is last two days of 1974) What I get with my SQL statement is an additional week between the two you see above. 12/31/1974 (this is not a Friday date, but is last date with prices for 1974 and has it own week num 53) 1974 53 3333.33 2222.22 Here is the SQL string I'm currently using that has this problem: SELECT date(max(Date), 'weekday 5') as MaxDate, Date, Year, Month, Week, Max(High) as High, Min(Low) as Low, Week as WeekNum FROM [" & sTable & "] GROUP By Year, Week Any ideas? TIA :-) -- View this message in context: http://old.nabble.com/Converting-Daily-to-Weekly-tp33252969p33252969.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
