CODEWITHSUNDEEP
mysqlsqldatecalendar
tim peterson
tim peterson

Reputation: 24315

MySQL query to count items by week for the current 52-weeks?

I have a query that I'd like to change so that it gives me the counts for the current 52 weeks. This query makes use of a calendar table I've made which contains a list of dates in a fixed range. The query as it stands is selecting max and min dates and not necessarily the last 52 weeks.

I'm wondering how to keep my calendar table current such that I can get the last 52-weeks (i.e, from right now to one year ago). Or is there another way to make the query independent of using a calendar table?

Here's the query:

SELECT calendar.datefield AS date, IFNULL(SUM(purchaseyesno),0) AS item_sales
FROM items_purchased join items on items_purchased.item_id=items.item_id 
RIGHT JOIN calendar ON (DATE(items_purchased.purchase_date) = calendar.datefield)
WHERE (calendar.datefield BETWEEN (SELECT MIN(DATE(purchase_date)) 
FROM items_purchased) AND (SELECT MAX(DATE(purchase_date)) FROM items_purchased)) 
GROUP BY week(date)

thoughts?

Upvotes: 1

Views: 3256

Answers (2)

user1191247
user1191247

Reputation: 12973

Some people dislike this approach but I tend to use a dummy table that contains values from 0 - 1000 and then use a derived table to produce the ranges that are needed -

CREATE TABLE dummy (`num` INT NOT NULL);
INSERT INTO dummy VALUES (0), (1), (2), (3), (4), (5), .... (999), (1000);

If you have a table with an auto-incrementing id and plenty of rows you could generate it from that -

CREATE TABLE `dummy`
SELECT id AS `num` FROM `some_table` WHERE `id` <= 1000;

Just remember to insert the 0 value.

SELECT CURRENT_DATE - INTERVAL num DAY
FROM dummy
WHERE num < 365

So, applying this approach to your query you could do something like this -

SELECT WEEK(calendar.datefield) AS `week`, IFNULL(SUM(purchaseyesno),0) AS item_sales
FROM items_purchased join items on items_purchased.item_id=items.item_id 
RIGHT JOIN (
    SELECT (CURRENT_DATE - INTERVAL num DAY) AS datefield
    FROM dummy
    WHERE num < 365
) AS calendar ON (DATE(items_purchased.purchase_date) = calendar.datefield)
WHERE calendar.datefield >= (CURRENT_DATE - INTERVAL 1 YEAR)
GROUP BY week(datefield) -- shouldn't this be datefield instead of date?

Upvotes: 1

DRapp
DRapp

Reputation: 48169

I too typically "simulate" a table on the fly by using @sql variables and just join to ANY table in your system that has AT least as many weeks as you want. NOTE... when dealing with dates, I like to typically use the date-part only which implies a 12:00:00 am. Also, by advancing the start date by 7 days for the "EndOfWeek", you can now apply a BETWEEN clause for records within a given time period... such as your weekly needs.

I've applied such a sample to coordinate the join based on date association to the per week basis... Since your 

select 
      DynamicCalendar.StartOfWeek,
      COALESCE( SUM( IP.PurchaseYesNo ), 0 ) as Item_Sales
   from 
      ( select
              @weekNum := @weekNum +1 as WeekNum,
              @startDate as StartOfWeek,
              @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
           from 
              ( select @weekNum := 0,
                       @startDate := date(date_sub(now(), interval 1 year ))) sqlv,
              AnyTableThatHasAtLeast52Records,
           limit
             52 ) DynamicCalendar
      LEFT JOIN items_purchased IP
         on IP.Purchase_Date bewteen DynamicCalendar.StartOfWeek 
                            AND DynamicCalendar.EndOfWeek
   group by
      DynamicCalendar.StartOfWeek

This is under the premise that your "PurchaseYesNo" value is in your purchased table directly. If so, no need to join to the ITEMS table. If the field IS in the items table, then I would just tack on a LEFT JOIN for your items table and get value from that.

However you could use the dynamicCalendar context in MANY conditions.

Upvotes: 1

Related Questions