Time summaries from multi-entity database

Question

I have the following Database Schema (re image),

And I'm trying to write a query which would return results something like this... (Column Headings): Product_Group - Product_Name - Week1 - Week2 - Week3 - Week4 - QTY_Order

Week1 are all the sales of a particular product between the current Date and the previous 7 days. Week2 are all the sales of a particular product from 2 weeks ago (thus between currentdate -7days AND currentdate -14 days)
Week3 are all the sales of a particular product from 3 weeks ago... etc;
QTY_Order is to be the average of a particular products sales of Week1, Week2, Week3 & Week4.

The following SQL will produce: Product_Group - Product_Name - Week1 - QTY_Order (but quantity_order is incorrect).

SELECT Product.ProductGroupId, Product.Name, SUM(SaleLine.Quantity) AS Week1, SUM(SaleLine.Quantity) AS OrderQty 
FROM Sale 
JOIN SaleLine ON Sale.ID = SaleLine.SaleId 
JOIN Product ON SaleLine.ProductId = Product.Id 
WHERE Sale.SaleDateTime > (CURDATE() - INTERVAL 7 DAY ) 
GROUP BY Product.ProductGroupId, Product.Name;

I've also tried the following SQL to get: Product_Name - Week1 - Week2 - QTY_Order (again, qty_order is incorrect), but week2 returns all null's.

SELECT Product.ProductGroupId, Product.Name, SUM(SaleLine.Quantity) AS Week1, (SELECT SUM(SaleLine.Quantity) 
    FROM SaleLine 
    JOIN Sale ON SaleLine.SaleID = Sale.ID 
    JOIN Product on SaleLine.ProductId = Product.Id 
    WHERE Sale.SaleDateTime BETWEEN (CURDATE() - INTERVAL 14 DAY) AND (CURDATE() - INTERVAL 7 DAY)) AS Week2, SUM(SaleLine.Quantity) AS QTY_TO_ORDER 
FROM Sale 
JOIN SaleLine ON Sale.ID = SaleLine.SaleId 
JOIN Product ON SaleLine.ProductId = Product.Id 
WHERE Sale.SaleDateTime > (CURDATE() - INTERVAL 7 DAY ) 
GROUP BY Product.ProductGroupId, Product.Name;

Can anyone point out where im going wrong with Week2 (that its returning Null).
Can anyone think of any easier/cleaner way to code this?
Can anyone provide some pointers for calculating QTY_Order?

Thanks in advance for any help people could provide :-)

**Edit - Added by Request (customer table not included):
Create Table Statements (there are also various triggers acting as constraints not included)

CREATE TABLE ProductGroup( Id int PRIMARY KEY AUTO_INCREMENT, Name varchar(50) NOT NULL );

CREATE TABLE Product( Id int PRIMARY KEY AUTO_INCREMENT, ProductGroupId int, Name varchar(100) NOT NULL, Price decimal(8,2), QuantityOnHand int NOT NULL, QuantitySold int NOT NULL, QuantityToOrder int NOT NULL, QuantityRequested int NOT NULL, CONSTRAINT FK_Product_ProductGroup FOREIGN KEY (ProductGroupId) REFERENCES ProductGroup(Id) );

CREATE TABLE Sale( Id int PRIMARY KEY AUTO_INCREMENT, CustomerId int, SaleDateTime datetime NOT NULL, CONSTRAINT Sale_Customer FOREIGN KEY (CustomerId) REFERENCES Customer(Id) );

CREATE TABLE SaleLine( Id int PRIMARY KEY AUTO_INCREMENT, ProductId int NOT NULL, SaleId int NOT NULL, Quantity int NOT NULL, CONSTRAINT FK_SaleLine_Product FOREIGN KEY (ProductId) REFERENCES Product(Id), CONSTRAINT FK_SaleLine_Sale FOREIGN KEY (SaleId) REFERENCES Sale(Id) );

While your diagrams sure look pretty, a few show create table statements and a few lines of sample data would have been a lot more usefull — e4c5, Sep 23 '16 at 11:41
This is called a pivot table. Have a look for example at [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table). You will have to replace `count` by `sum`. For your order quantity, you would need a `max(SaleLine.Quantity) AS OrderQty`, because you only need it once. — Solarflare, Sep 23 '16 at 11:53

score 1 · Accepted Answer · answered Sep 23 '16 at 12:58

You are seeking five summaries from your information: Week1, Week2, Week3, Week4, and QTY_Order. That makes for a complex GROUP BY query.

It's best to approach this sort of thing step by step. Your first two steps should be to write separate queries which generate correct Week1 and QTY_Order queries. That will help you get your business logic right. Do you want to count orders? Do you want to sum units sold? Do you want to sum gross revenue?

(I'd rather not attempt to write such queries for you; it's your data, not mine.)

Once you have that sort of thing right, you can do something like this pseudocode to get your weekly summaries.

  SELECT group, name,
         SUM( CASE WHEN saledate < CURDATE() - INTERVAL 0 DAY
                    AND saledate >= CURDATE() - INTERVAL 7 DAY THEN Quantity
                   ELSE 0 END) Week1,
         SUM( CASE WHEN saledate < CURDATE() - INTERVAL 7 DAY
                    AND saledate >= CURDATE() - INTERVAL 14 DAY THEN Quantity
                   ELSE 0 END) Week2,
         SUM( CASE WHEN saledate <  CURDATE() - INTERVAL 14 DAY
                    AND saledate >= CURDATE() - INTERVAL 21 DAY THEN Quantity
                   ELSE 0 END) Week3
    FROM (some subquery)
   GROUP BY group,name

The trick is using case statements inside your sum functions to select transactions for the appropriate week.

It's a hairball of a query. But it will work.

score 0 · Answer 2 · answered Sep 23 '16 at 13:53

So I've got this code working:...

SELECT Product.ProductGroupId, Product.Name, SUM(CASE WHEN Sale.SaleDateTime BETWEEN CURDATE() AND Sale.SaleDateTime <= (CURDATE() - INTERVAL 7 DAY) THEN SaleLine.Quantity ELSE 0 END) AS Week1, SUM(CASE WHEN Sale.SaleDateTime < (CURDATE() - INTERVAL 7 DAY) AND Sale.SaleDateTime >= (CURDATE() - INTERVAL 14 DAY) THEN SaleLine.Quantity ELSE 0 END) AS Week2, SUM(CASE WHEN Sale.SaleDateTime < (CURDATE() - INTERVAL 14 DAY) AND Sale.SaleDateTime >= (CURDATE() - INTERVAL 21 DAY) THEN SaleLine.Quantity ELSE 0 END) AS Week3, SUM(CASE WHEN Sale.SaleDateTime < (CURDATE() - INTERVAL 21 DAY) AND Sale.SaleDateTime >= (CURDATE() - INTERVAL 28 DAY) THEN SaleLine.Quantity ELSE 0 END) AS Week4 FROM Sale JOIN SaleLine ON Sale.ID = SaleLine.SaleId JOIN Product ON SaleLine.ProductId = Product.Id GROUP BY Product.ProductGroupId, Product.Name;

But I'm just not sure about how to do another Case statement, to get the Average between Week1, Week2, Week3, Week4

Any suggestions?

Time summaries from multi-entity database

2 Answers2