Re: [sqlite] Converting Daily to Weekly

[Igor Tandetnik]

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

[rixtertrader]

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

[Igor Tandetnik]

rixtertrader  wrote:
> 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

[rixtertrader]

Igor Tandetnik wrote:
> 
> rixtertrader  wrote:
>> 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

[Igor Tandetnik]

rixtertrader  wrote:
> 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

[Petite Abeille]

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

[sqlite] Converting Daily to Weekly

[rixtertrader]

I have a TABLE that contains the following:

Date  (Monday to Friday only)
Year (from Date, ie. )
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. )
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/)
1974 ()
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
.33
.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