Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to simplify SQL - 90 SQLs into simpler one.

    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
    Last edited by grofaty; 07-07-08 at 04:51.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    ..........
    Last edited by grofaty; 07-07-08 at 10:21.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    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

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
               )
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 07-08-08 at 05:04.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 07-08-08 at 05:11.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Today is 8.7.2008 and it is missing today's date. There is also no row for "5.7.2008 - B" combination.
    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.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    tonkuma, thank you very much. This is exactly what my application developer needs. You saved me a lot of time and effort.
    Thanks,
    Grofaty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •