CODEWITHSUNDEEP
mysqlsql
Primoz Rome
Primoz Rome

Reputation: 11041

SQL select ordered product quantities based on delivery date sorted by week

I am not sure if my question is clear but what I need is quite complex query. I am unable to put it together myself.

We have tables orders, order_items, products, deliveries and delivery_items. Orders is the main order table. Order_items table holds the list of ordered products in certain order. Products is the main products table and deliveries/delivery_items tables hold the list of delivered order_items (we can deliver entire order or only partially).

This is the 'stripped down' table structure:

ORDERS:

ID    ORDER_NUMBER     DELIVERY_DATE    STATUS
-------------------------------------------------
1     2013-00001       Unixtimestamp    Closed
2     2013-00002       Unixtimestamp    Open
...

PRODUCTS:

ID    CODE
-----------------------
1     Product 1
2     Product 2
3     Product 3
...

ORDER ITEMS:

ID    ORDER_ID    PRODUCT_ID    QTY
-----------------------------------------
1     1           1             2
2     1           2             5
3     1           3             1
4     2           3             10

DELIVERIES:

ID    ORDER_ID    DELIVERY_NUMBER     TYPE
---------------------------------------------
1     1           2013-00001          Full
2     2           2013-00002          Partial
...

DELIVERY_ITEMS:

ID    DELIVERY_ID     ORDER_ITEM_ID    QTY
------------------------------------------
1     1               1                2
2     1               2                5
3     1               3                1
4     2               4                5
...

Our production demands view where all non-delivered order_items(products) are listed by quantities in the upcoming week schedule. What I need is something like this (quantities here are random):

Product     Overdue    W0    W1    W2    W3    W4    W5    Later    Total
-------------------------------------------------------------------------
Product 1   1          2     0     0     0     0     0     0        3
Product 2   0          3     5     1     0     0     0     4        13
Product 3   2          4     0     7     5     0     0     0        18
...

The entire view is based on current time and order's delivery_date field. The query would need to get ordered product quantities from all open orders, check if some of there products were maybe already delivered and subtract delivered quantities, and on the end sort the result quantities as shown above.

UPDATE: here is SQL Fiddle with above structure with some data http://sqlfiddle.com/#!2/88891/4

Upvotes: 0

Views: 3838

Answers (2)

xQbert
xQbert

Reputation: 35333

---Using Fiddle provided (http://sqlfiddle.com/#!2/88891/34/0)

SELECT P.Name,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())<=0 then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) AS Overdue,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())<=7 and DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())>0  then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) as W0,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())<=14 and DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())>7  then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) as W1,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())<=21 and DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())>14 then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) as W2,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())<=28 and DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())>21 then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) as W3,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())<=35 and DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())>28 then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) as W4,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())<=42 and DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())>35 then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) as W5,
sum(case when DATEDIFF(FROM_UNIXTIME(Delivery_date),curDate())>42 then coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0) else 0 END) as Later,
sum(coalesce(OI.Quantity,0)-coalesce(DI.Quantity,0)) as Total

FROM ORDERS O
INNER JOIN ORDER_ITEMS OI
  ON OI.Order_ID = O.ID
INNER JOIN PRODUCTS P on 
  P.ID = OI.Product_ID
LEFT JOIN DELIVERIES D
  ON D.Order_ID = O.ID
LEFT JOIN DELIVERY_ITEMS DI
  ON DI.Delivery_ID = D.ID
  AND OI.ID = DI.Order_Item_ID
WHERE coalesce(DI.Quantity,0) < OI.Quantity 
GROUP BY P.Name

Thanks for the fiddle. This takes care of all the remaining syntax errors. and formats to include 0 in the results.

  • Coalesce, takes first non-null value in an unlimited series.
  • FROM_UNIXTIME converts into to valid date time for comparison using datediff.
  • else statments handle situations when no data matches critiera thus 0 is evaluated.
  • where clause eliminates orders with all items delivered or more than all items ordered delivered. (Thus items closed will still be included since they are not fully shipped!)

Upvotes: 4

criticalfix
criticalfix

Reputation: 2870

To get you started, here are a couple of joins:

SELECT o.ORDER_NUMBER, p.CODE, oi.QTY
FROM orders o
JOIN order_items oi ON oi.ORDER_ID = o.ID
JOIN products p ON p.ID = oi.PRODUCT_ID

And:

SELECT d.DELIVERY_NUMBER, d.ORDER_ID, di.ORDER_ITEM_ID, di.QTY
FROM deliveries d
JOIN delivery_items di ON di.DELIVERY_ID = d.ID

What would you have to do to tie those together?

Upvotes: 0

Related Questions