How to simplify SQL - 90 SQLs into simpler one.

grofaty · #1 (**permalink**) 07-07-08, 03:36

Hi,
on DB2 v8.2 fp 11 on Linux I have to write SQL. Just to simplify my problem. I have a store which has some products. In table there are data about stock for products at specific date. Table looks like this:

Code:

STOCK_DATE PRODUCT    QUANTITY
---------- ---------- -----------
2008-07-05 A                  100
2008-07-06 B                  200
2008-07-07 B                  210

So product 'A' had quantity 100 at date 2008-07-05. But because there are no other dates for this product (there was no in and out for this product so stock remains the same for other dates) this is also today's stock quantity for product A. Product 'B' has two dates, to look what is today's stock quantity I have to look to the maximum date of product B.

To write what is today stock for product A and B I have written the following SQL:

Code:

SELECT
     CURRENT DATE,
     PRODUCT,
     QUANTITY
FROM
     ADMIN.TAB
WHERE
     (STOCK_DATE, PRODUCT) IN
                  (
                  SELECT MAX(STOCK_DATE), PRODUCT
                  FROM ADMIN.TAB
                  WHERE STOCK_DATE <= CURRENT DATE
                  GROUP BY PRODUCT
                  )
;

To write what was yesterday stock I just added "- 1 day" in above SQL (see bolded text in following SQL)

Code:

SELECT
     CURRENT DATE - 1 DAY,
     PRODUCT,
     QUANTITY
FROM
     ADMIN.TAB
WHERE
     (STOCK_DATE, PRODUCT) IN
                  (
                  SELECT MAX(STOCK_DATE), PRODUCT
                  FROM ADMIN.TAB
                  WHERE STOCK_DATE <= CURRENT DATE - 1 DAY
                  GROUP BY PRODUCT
                  )
;

But now I have to get stock quantity for last 90 days. So one way is to write 90 SQLs with union all, but I don't like this idea. Is there any way to simplify SQL?

Thanks,
Grofaty

grofaty · #2 (**permalink**) 07-07-08, 03:36

..........

tonkuma · #3 (**permalink**) 07-07-08, 13:07

Not tested.

Code:

WITH last_n_days_stock_date AS (
SELECT
       last_n_day
     , product
     , MAX(stock_date) AS stock_date
  FROM
       (SELECT CURRENT DATE - n DAYS
          FROM (SELECT ROWNUBER() OVER()
                  FROM (VALUES 0,1,2,3,4,5,6,7,8,9) n1(n)
                     , (VALUES 0,1,2,3,4,5,6,7,8)   n2(n)
               ) n(n)
       ) last_90_days(last_n_day)
  LEFT JOIN
       ADMIN.TAB
    ON stock_date <= last_n_days
 GROUP BY
       last_n_day
     , product
)
SELECT
       lns.last_n_day
     , lns.product
     , atb.quantity
  FROM
       last_n_days_stock_date lns
  LEFT JOIN
       ADMIN.TAB              atb
   ON  atb.product    = lns.product
   AND atb.stock_date = lns.stock_date
 ORDER BY
       lns.last_n_day
     , lns.product
;

grofaty · #4 (**permalink**) 07-08-08, 02:19

Hi,
two little bugs in you code (see bold text)...

Code:

WITH LAST_N_DAYS_STOCK_DATE AS (
SELECT
       LAST_N_DAY
     , PRODUCT
     , MAX(STOCK_DATE) AS STOCK_DATE
  FROM
       (SELECT CURRENT DATE - N DAYS
          FROM (SELECT ROWNUMBER() OVER()
                  FROM (VALUES 0,1,2,3,4,5,6,7,8,9) N1(N)
                     , (VALUES 0,1,2,3,4,5,6,7,8)   N2(N)
               ) N(N)
       ) LAST_90_DAYS(LAST_N_DAY)
  LEFT JOIN
       ADMIN.TAB
    ON STOCK_DATE <= LAST_N_DAY
 GROUP BY
       LAST_N_DAY
     , PRODUCT
)
SELECT
       LNS.LAST_N_DAY
     , LNS.PRODUCT
     , ATB.QUANTITY
  FROM
       LAST_N_DAYS_STOCK_DATE LNS
  LEFT JOIN
       ADMIN.TAB              ATB
   ON  ATB.PRODUCT    = LNS.PRODUCT
   AND ATB.STOCK_DATE = LNS.STOCK_DATE
 ORDER BY

       LNS.LAST_N_DAY
     , LNS.PRODUCT
;

But this SQL does not return exactly what I want. It returns the following (I have ommit the null values rows that are not needed):

Code:

LAST_N_DAY PRODUCT    QUANTITY
---------- ---------- -----------
05.07.2008 A                  100
06.07.2008 A                  100
06.07.2008 B                  200
07.07.2008 A                  100
07.07.2008 B                  210

Today is 8.7.2008 and it is missing today's date. There is also no row for "5.7.2008 - B" combination.

Any idea how to fix this?
Thanks,
Grofaty

Peter.Vanroose · #5 (**permalink**) 07-08-08, 03:23

Quote:

Originally Posted by grofaty

...
But now I have to get stock quantity for last 90 days. So one way is to write 90 SQLs with union all, but I don't like this idea. Is there any way to simplify SQL?

This is kind of an "iterative union"; did you try with a recursive CTE?
Roughly something like (not tested):

Code:

WITH days (d, n) AS
(VALUES(current date, 0)
 UNION ALL
 SELECT d - 1 DAY, n+1 FROM days
 WHERE n < 90
)
SELECT d, product, quantity
FROM   admin.tab CROSS JOIN days
WHERE  (STOCK_DATE, PRODUCT) IN
           (SELECT MAX(STOCK_DATE), PRODUCT
            FROM ADMIN.TAB
            WHERE STOCK_DATE <= days.d
            GROUP BY PRODUCT
           )

grofaty · #6 (**permalink**) 07-08-08, 03:50

Peter,
it looks "cross join" sintax does not work in my db, so I just used without this two words. So SQL is:

Code:

WITH DAYS (D, N) AS
(VALUES(CURRENT DATE, 0)
 UNION ALL
 SELECT D - 1 DAY, N+1 FROM DAYS
 WHERE N < 90
)
SELECT D, PRODUCT, QUANTITY
FROM   ADMIN.TAB, DAYS
WHERE  (STOCK_DATE, PRODUCT) IN
           (SELECT MAX(STOCK_DATE), PRODUCT
            FROM ADMIN.TAB
            WHERE STOCK_DATE <= DAYS.D
            GROUP BY PRODUCT
           )
ORDER BY 1,2
;

Result is:

Code:

D          PRODUCT    QUANTITY
---------- ---------- -----------
05.07.2008 A                  100
06.07.2008 A                  100
06.07.2008 B                  200
07.07.2008 A                  100
07.07.2008 B                  210
08.07.2008 A                  100
08.07.2008 B                  210

grofaty · #7 (**permalink**) 07-08-08, 04:06

Hi,
thanks for help... but now application developer said he need data organized for each day product A quantity and product B quantity. So like this:

Code:

STOCK_DAY  PRODUCT_A_QUANTITY  PRODUCT_B_QUANTITY
---------  ------------------  ------------------
05.07.2008                100                   0
06.07.2008                100                 200
07.07.2008                100                 210
08.07.2008                100                 210

Thanks,
Grofaty

tonkuma · #8 (**permalink**) 07-08-08, 05:11

Quote:

Today is 8.7.2008 and it is missing today's date. There is also no row for "5.7.2008 - B" combination.

Quote:

So like this:

Code:

STOCK_DAY  PRODUCT_A_QUANTITY  PRODUCT_B_QUANTITY
---------  ------------------  ------------------
05.07.2008                100                   0
06.07.2008                100                 200
07.07.2008                100                 210
08.07.2008                100                 210

Code:

WITH last_n_days_stock_date AS (
SELECT d.stock_day
     , product
     , MAX(atb.stock_date) AS stock_date
  FROM
       (SELECT CURRENT DATE - n DAYS
          FROM (SELECT ROW_NUMBER() OVER() - 1
                  FROM (VALUES 0,1,2,3,4,5,6,7,8,9) n1(n)
                     , (VALUES 0,1,2,3,4,5,6,7,8)   n2(n)
               ) n(n)
       ) D(stock_day)
  LEFT JOIN
       ADMIN.TAB ATB
    ON atb.stock_date <= d.stock_day
 GROUP BY
       d.stock_day
     , product
)
SELECT lns.stock_day
     , MAX(CASE atb.product WHEN 'A' THEN quantity ELSE 0 END) product_A_quantity
     , MAX(CASE atb.product WHEN 'B' THEN quantity ELSE 0 END) product_B_quantity
  FROM
       last_n_days_stock_date lns
  LEFT JOIN
       ADMIN.TAB              atb
   ON  atb.product    = lns.product
   AND atb.stock_date = lns.stock_date
 GROUP BY
       lns.stock_day
HAVING MAX(quantity) >= 0
 ORDER BY
       lns.stock_day
;
------------------------------------------------------------------------------

STOCK_DAY  PRODUCT_A_QUANTITY PRODUCT_B_QUANTITY
---------- ------------------ ------------------
2008-07-05                100                  0
2008-07-06                100                200
2008-07-07                100                210
2008-07-08                100                210

  4 record(s) selected.

tonkuma · #9 (**permalink**) 07-08-08, 05:57

Quote:

But this SQL does not return exactly what I want. It returns the following (I have ommit the null values rows that are not needed):

So, it is not neccesary to use outer join instead of inner join, and having clause is also not neccesary.

Code:

WITH last_n_days_stock_date AS (
SELECT d.stock_day
     , product
     , MAX(atb.stock_date) AS stock_date
  FROM
       (SELECT CURRENT DATE - n DAYS
          FROM (SELECT ROW_NUMBER() OVER() - 1
                  FROM (VALUES 0,1,2,3,4,5,6,7,8,9) n1(n)
                     , (VALUES 0,1,2,3,4,5,6,7,8)   n2(n)
               ) n(n)
       ) D(stock_day)
 INNER JOIN
       ADMIN.TAB ATB
    ON atb.stock_date <= d.stock_day
 GROUP BY
       d.stock_day
     , product
)
SELECT lns.stock_day
     , MAX(CASE atb.product WHEN 'A' THEN quantity ELSE 0 END) product_A_quantity
     , MAX(CASE atb.product WHEN 'B' THEN quantity ELSE 0 END) product_B_quantity
  FROM
       last_n_days_stock_date lns
 INNER JOIN
       ADMIN.TAB              atb
   ON  atb.product    = lns.product
   AND atb.stock_date = lns.stock_date
 GROUP BY
       lns.stock_day
 ORDER BY
       lns.stock_day
;

grofaty · #10 (**permalink**) 07-08-08, 09:50

tonkuma, thank you very much. This is exactly what my application developer needs. You saved me a lot of time and effort.
Thanks,
Grofaty

Internet Services

Web Development

Digital Marketing

Consumer Tech