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 the promotions occuring between June 1 and June 30
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.
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 the promotions occuring between June 1 and June 30
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.