1

The following query does what I want, but I need to extend it, but i'm just not enough of an SQL expert to figure out what I'm doing wrong.

SELECT sd.StockNumber AS StkNm, sd.Description As Descr,
  sd.Location As Loc, SUM(sd.QtyShipped) AS d3q,
  SUM(sd.PriceAmount) AS d3pa, ti.QOH AS QOH,
  ti.MinQty as MinQTY, ti.OrderQty AS OrderQty,
  ti.OrderMultiplier, ti.OrderUnit
FROM SAL__DETAIL sd, TESTINVDB ti
WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= sd.TransDate
      AND SalesTxListid IN
       (SELECT id FROM SAL__LIST sl
        WHERE sl.StoreNum = 5)
      AND ti.StoreNum = 5
      AND ti.Number = sd.StockNumber
GROUP BY sd.StockNumber;

I cheated, and created some views that did what I want over the intervals, but I think it's ucky.

So I'm attempting to extend the above query to do 3 days and 30 days:

SELECT *
FROM
( sd.StockNumber AS StkNm, sd.Description As Descr,
  sd.Location As Loc, SUM(sd.QtyShipped) AS d3q,
  SUM(sd.PriceAmount) AS d3pa, ti.QOH AS QOH,
  ti.MinQty as MinQTY, ti.OrderQty AS OrderQty,
  ti.OrderMultiplier, ti.OrderUnit
  FROM SAL__DETAIL sd, TESTINVDB ti
  WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= sd.TransDate
        AND SalesTxListid IN
         (SELECT id FROM SAL__LIST sl
          WHERE sl.StoreNum = 5)
        AND ti.StoreNum = 5
        AND ti.Number = sd.StockNumber
  GROUP BY sd.StockNumber
  UNION
  sd.StockNumber, sd.Description, sd.Location, SUM(sd.QtyShipped) d30q,
  SUM(sd.PriceAmount) d30pa, ti.QOH, ti.MinQty, ti.OrderQty,
  ti.OrderMultiplier, ti.OrderUnit
  FROM SAL__DETAIL sd, TESTINVDB ti
  WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= sd.TransDate
        AND SalesTxListid IN
         (SELECT id FROM SAL__LIST sl
          WHERE sl.StoreNum = 5)
        AND ti.StoreNum = 5
        AND ti.Number = sd.StockNumber
  GROUP BY sd.StockNumber
);

Much Complaining from Mysql, my guess is I can't use the SUM(), but then I don't know what to do. Seems like the kind of thing that I should be able to do, I just can't quite put my finger on the magic.

Any help appreciated.

EDIT 1

I ended up going this route, which seems to work OK, I will have to play with the UNION more and see. I couldn't see any changes in the fiddle, every time I loaded it I still saw the old data, so I probably need to clear cache and do all that stuff.

In any case, using this query works, and does what I need, and runs quickly enough.

SELECT
    StockNumber,
    Description,
    Location,
    QOH,
    MinQty,
    OrderQty,
    OrderMultiplier,
    OrderUnit,
    d120qs,
    d90qs,
    d60qs,
    d30qs,
    d10qs,
    d3qs
FROM (
    SELECT 
    sd.StockNumber AS StockNumber, 
    sd.Description As Description,
    ti.Location As Location, 
    ti.QOH AS QOH,
    sum(
        CASE WHEN 
            DATEDIFF((DATE_SUB(CURDATE(), INTERVAL 120 DAY)), 
                sd.TransDate) <= 120
             THEN sd.QtyShipped
             ELSE 0.000
        END
    ) AS d120qs,
    sum(
        CASE WHEN 
            DATEDIFF((DATE_SUB(CURDATE(), INTERVAL 90 DAY)), 
                sd.TransDate) <= 90
             THEN sd.QtyShipped
             ELSE 0.000
        END
    ) AS d90qs,
    sum(
        CASE WHEN 
            DATEDIFF((DATE_SUB(CURDATE(), INTERVAL 60 DAY)), 
                sd.TransDate) <= 60
             THEN sd.QtyShipped
             ELSE 0.000
        END
    ) AS d60qs,
    sum(
        CASE WHEN 
            DATEDIFF((DATE_SUB(CURDATE(), INTERVAL 30 DAY)), 
                sd.TransDate) <= 30
             THEN sd.QtyShipped
             ELSE 0.000
        END
    ) AS d30qs,
    sum(
        CASE WHEN 
            DATEDIFF((DATE_SUB(CURDATE(), INTERVAL 10 DAY)), 
                sd.TransDate) <= 10
             THEN sd.QtyShipped
             ELSE 0.000
        END
    ) AS d10qs,
    sum(
        CASE WHEN 
            DATEDIFF((DATE_SUB(CURDATE(), INTERVAL 3 DAY)), 
                sd.TransDate) <= 3
             THEN sd.QtyShipped
             ELSE 0.000
        END
    ) AS d3qs,
    ti.MinQty as MinQTY, 
    ti.OrderQty AS OrderQty,
    ti.OrderMultiplier AS OrderMultiplier, 
    ti.OrderUnit AS OrderUnit
FROM
    SAL__DETAIL sd, TESTINVDB ti 
WHERE 
        sd.SalesTxListid IN
            (SELECT id 
        FROM SAL__LIST sl
            WHERE sl.StoreNum = 5 
        AND DATEDIFF((DATE_SUB(CURDATE(), INTERVAL 180 DAY)),
            sl.txDate) <= 180)
        AND ti.StoreNum = 5
        AND ti.Number = sd.StockNumber
GROUP BY sd.StockNumber) As A   

Gives me:

StockNumber     Description     Location        QOH     MinQty  OrderQty OrderMultiplier OrderUnit       d120qs  d90qs   d60qs   d30qs   d10qs   d3qs
0               UNASSIGN        0       0.00    0.00    1.00    each    63.000  38.000
  25.000  13.000  2.000   0.000
4       xxxxx   UNASSIGN        3       0.00    0.00    1.00
    each    8.000   8.000   8.000   6.000   2.000   0.000
37      xxxxx   UNASSIGN        4       0.00    0.00    1.00    each    11.000
  10.000  6.000   4.000   0.000   0.000

Which looks to be in the ballpark of what I need.

I appreciate your help.

  • Check the math. When doing SUM (and other aggregates) in a JOIN, the values are sometimes inflated. – Rick James Apr 8 '15 at 12:43
  • The outer ( SELECT * FROM ... ) is unnecessary. – Rick James Apr 8 '15 at 12:46
0

I'm not sure why you are using subqueries in the upper SELECT of the union.

I rewrote your query without the subquery and duplicated the upper and lower SELECTs of the union.

Only then changing the interval on the bottom SELECT to 30.

SELECT 
sd.StockNumber AS StkNm, sd.Description As Descr,
  sd.Location As Loc, SUM(sd.QtyShipped) AS d3q,
  SUM(sd.PriceAmount) AS d3pa, ti.QOH AS QOH,
  ti.MinQty as MinQTY, ti.OrderQty AS OrderQty,
  ti.OrderMultiplier, ti.OrderUnit, 'table1' as TableName
FROM
SAL__DETAIL sd, TESTINVDB ti
WHERE DATE_SUB(CURDATE(), INTERVAL 3 DAY) <= sd.TransDate
        AND SalesTxListid IN
         (SELECT id FROM SAL__LIST sl
          WHERE sl.StoreNum = 5)
        AND ti.StoreNum = 5
        AND ti.Number = sd.StockNumber
  GROUP BY sd.StockNumber
UNION
SELECT 
sd.StockNumber AS StkNm, sd.Description As Descr,
  sd.Location As Loc, SUM(sd.QtyShipped) AS d3q,
  SUM(sd.PriceAmount) AS d3pa, ti.QOH AS QOH,
  ti.MinQty as MinQTY, ti.OrderQty AS OrderQty,
  ti.OrderMultiplier, ti.OrderUnit,'table2' as TableName
FROM
SAL__DETAIL sd, TESTINVDB ti
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= sd.TransDate
        AND SalesTxListid IN
         (SELECT id FROM SAL__LIST sl
          WHERE sl.StoreNum = 5)
        AND ti.StoreNum = 5
        AND ti.Number = sd.StockNumber
  GROUP BY sd.StockNumber

Here is my work on SQL Fiddle

  • I did not realize I could union the 2 statements that way. The problem with this query (the copy from my query to your result isn't quite correct, the interval 30 day should have d30q and d30pa for the SUM() results, and the interval 3 day should be d3q, d3pa. However, even as I edit the select, I still only get the first columns named after the entries in the first select. I should end up with a stocknum...d30pa...d30q...d3pa...d3q...etc as my result columns. I tweaked your fiddle a bit for an example. Should show d3q as 1, and d30q as 2. (Amt shipped in last 3 days, last 30 days). tnx – Jaye Mathisen Apr 8 '15 at 18:35
  • Hello @JayeMathisen, with UNION ALL, you'll get two results, but I also had to modify rows in the SAL__DETAIL, so that both were within the 3 day and 30 day intervals. UNION ALL doesn't make sense here because both rows are duplicates. As for the column names, with UNION, the field names that show will be from the UPPER SELECT. I modified my answer and the fiddle to help you identify the table where the rows are coming from. – Craig Efrein Apr 8 '15 at 20:19
  • I ended up going a different route, but I appreciate your taking the time and your help. Thanks. – Jaye Mathisen Apr 8 '15 at 23:52
  • Hi @JayeMathisen, thanks for letting me know, I was about to suggest PIVOT and UNPIVOT, but looks like you got what you needed. – Craig Efrein Apr 9 '15 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.