CODEWITHSUNDEEP
mysqlsqlzen-cart
bcsteeve
bcsteeve

Reputation: 1001

SQL sales report by calendar week

I've been Googling for a few hours... thought this would be easy, but clearly not for me :)

I've got sales data in two tables and I want to generate a weekly sales report for a specific item. For this purpose, I don't care about dollar values, just number of units. An a "week" is either a calendar week (whatever start day, I don't care) or just 7-day chunks back from current (so week 1 is the last 7 days, week 2 is 8 - 15 days ago, etc) - whichever is easier. I'm simply trying to monitor sales trends over time. Preferably it would span back over years so that if its the first week of January, for example, it wouldn't show just one record.

The data comes from ZenCart. The relevant tables/column structure is here:

Table "orders" has columns: orders_id, date_purchased

Table "orders_products" has columns: orders_id, products_id, product_quantity

Where I'm having trouble is with the joins and syntax.

Upvotes: 1

Views: 5146

Answers (3)

Jessica
Jessica

Reputation: 1

In order to take care of the year end wrap. for instance, week(12/30/2018)=52 and week(12/31/2018)=52 both are considered week 52 of 2018. the first day of 2019 starts on a Tuesday. you can write a case statement as follows to move 12/30/2018 and 12/31/2018 to the first week of 2019. so that you will have a complete 7 days week to compare:

case when order_date in ( '2018-12-30', '2018-12-31')
     then 0
     else week(order_date)
end as order_week

Upvotes: 0

bcsteeve
bcsteeve

Reputation: 1001

This worked for my needs:

SELECT o.date_purchased, CONCAT(YEAR(o.date_purchased), LPAD(WEEK(o.date_purchased), 2, '0')) as weekyear, op.products_id, SUM( op.products_quantity )
FROM orders_products op
LEFT JOIN orders o ON op.orders_id = o.orders_id
WHERE op.products_id = 331
GROUP BY weekyear
ORDER BY weekyear

Upvotes: 3

Olaf Dietsche
Olaf Dietsche

Reputation: 74078

If you have some date/datetime/timestamp column, you can use the week function in your where clause 

select week(now()) as week, sum(units) as total
from sales
where week(sales_date) = week(now());

or the previous week 

select week(now()) - 1 as week, sum(units) as total
from sales
where week(sales_date) = week(now()) - 1;

You must take care for the year wrap around from week 52/53 to week 0/1.

SQLFiddle for testing.

Upvotes: 0

Related Questions