>Hello,
>
>I am having a lot of trouble trying to find the best
>way to write the SQL to do the following:
>
>I have a table called "referrals." In this table I
>have a column called "ref." The data in the ref column
>
>are of the form "p/$promo_id/$date". An example would
>be "p/26/20010629" which says promotion number 29
>occuring on June 29th 2001.
>
>My task is to find a way to do a group by of all the
>promotion IDs within a certain date range. The
>following code is INCORRECT (BETWEEN 'p/%/2001...' IS
>NOT VALID), but gives a good picture of what I am
>trying to do:
>
>SELECT ref,SUM(clicks) AS num_clicks FROM referrals
>WHERE ref BETWEEN 'p/%/20010601' AND 'p/%/20010630'
>GROUP BY ref
>
>The code above is meant to do a group by of all the
>clicks generated by each promotion occuring between
>June 1 and June 30
>
>I realize that I could hypothetically loop over each
>promotional ID and do ref BETWEEN 'p/3/XXX' AND
>'p/3/YYY',,p/888/XXX AND p/888/YYY', but this
>method is very bad in performance when the promotion
>IDS goes into the tens of thousands, so the GROUP BY
>is important to keep for me.
>
>Is there a way to account for the date range WITHOUT
>having to create a new date column which copies the
>date over from ref codes in the column ref?
>
>My Best Wishes.
Sir, the best way to solve the problem is to apply the standard rules
of table design, the first of which says that you don't combine
several types of data into one column. Have a column for promotion
IDs, and another for the date.
A far inferior solution would be to use
WHERE Right(ref, 8) BETWEEN ...
I haven't run this on my computer, but you'll be comparing text
strings, so my guess is you'll have to put the begin and end dates
inside quotes, or force the date to a number with
Right(ref, 8) + 0
[EMAIL PROTECTED]
Know thyself? Absurd direction!
Command 'know' not recognized.
Drat. I wanted a list server and got an epistemological skeptic instead.
MySQL secret passwords: sql query database
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
