Re: [sqlite] Converting Daily to Weekly
On 2/3/2012 12:45 PM, rixtertrader wrote: Igor Tandetnik wrote: It could be written more explicitly: GROUP BY Year + (case WeekNum when 53 then 1 else 0 end), (case WeekNum when 53 then 1 else WeekNum end); Okay, I get it. GROUP by Year and if week 53 it becomes part of the next Year. GROUP by WeekNum and if 53 it becomes 1. Does this effect the Max(High) and Max(Low) values as well? In other words, will the values of week 53 be part of the equation with week 1 when returning Max(High) and Max(Low) for the week? Yes. All rows for which expressions in the GROUP BY clause evalulate to the same values end up in the same group. Aggregate functions then work on that group. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Converting Daily to Weekly
Igor Tandetnik wrote: > > It's simple, really. (WeekNum=53) is a boolean expression, which has a > value of 1 (for true) or 0 (for false). So Year + (WeekNum=53) is equal to > Year most of the time, but Year + 1 when WeekNum is 53. Similarly WeekNum > - (WeekNum=53)*52 is equal to WeekNum most of the time, but to 1 (53 - 1 * > 52) when WeekNum is 53. > > It could be written more explicitly: > > GROUP BY Year + (case WeekNum when 53 then 1 else 0 end), > (case WeekNum when 53 then 1 else WeekNum end); > > Okay, I get it. GROUP by Year and if week 53 it becomes part of the next Year. GROUP by WeekNum and if 53 it becomes 1. Does this effect the Max(High) and Max(Low) values as well? In other words, will the values of week 53 be part of the equation with week 1 when returning Max(High) and Max(Low) for the week? Thanks for your replies. Very educational! :) -- View this message in context: http://old.nabble.com/Converting-Daily-to-Weekly-tp33252969p33258355.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
Re: [sqlite] Converting Daily to Weekly
rixtertraderwrote: > Igor Tandetnik wrote: >> GROUP BY Year + (WeekNum=53), WeekNum - (WeekNum=53)*52 >> >> This should effectively merge week 53 with week 1 of next year. > > I don't understand how it works. It's simple, really. (WeekNum=53) is a boolean expression, which has a value of 1 (for true) or 0 (for false). So Year + (WeekNum=53) is equal to Year most of the time, but Year + 1 when WeekNum is 53. Similarly WeekNum - (WeekNum=53)*52 is equal to WeekNum most of the time, but to 1 (53 - 1 * 52) when WeekNum is 53. It could be written more explicitly: GROUP BY Year + (case WeekNum when 53 then 1 else 0 end), (case WeekNum when 53 then 1 else WeekNum end); Perhaps I was being too clever. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Converting Daily to Weekly
Igor Tandetnik wrote: > > rixtertraderwrote: >> 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 > > Try > > GROUP BY Year + (WeekNum=53), WeekNum - (WeekNum=53)*52 > > This should effectively merge week 53 with week 1 of next year. > -- > Igor Tandetnik > > ___ > That was absolutely brilliant. Thank you. I don't understand how it works. I'm sure some of that has to do with the fact that I'm a 1 out of 10 when it comes to understanding SQL, and I wrote this so long ago. I don't suppose this can be explained to me in a dumbed-down sort of way. :-/ -- View this message in context: http://old.nabble.com/Converting-Daily-to-Weekly-tp33252969p33255272.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
Re: [sqlite] Converting Daily to Weekly
rixtertraderwrote: > 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 Try GROUP BY Year + (WeekNum=53), WeekNum - (WeekNum=53)*52 This should effectively merge week 53 with week 1 of next year. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Converting Daily to Weekly
On Feb 2, 2012, at 9:30 PM, rixtertrader wrote: > This is because the year started in the middle of the first week. Perhaps you might be interested in using the ISO week instead: http://en.wikipedia.org/wiki/ISO_week_date ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users