1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Any SQL guys want to make a quick $20?

Discussion in 'BlackHat Lounge' started by Carepolice, Jan 8, 2012.

  1. Carepolice

    Carepolice Power Member

    Joined:
    Mar 25, 2009
    Messages:
    576
    Likes Received:
    618
    Location:
    ZonSidekick.com
    I've got a problem with a query I'm putting together. Currently have a thread open in the T-SQL section on MSDN if any of you are a member there and want the points as well (thread title is
    Incorrect COUNT results when using DISTINCT)


    Current Query:

    SELECT COUNT(DISTINCT ocr_web_order) AS ocr_web_order_cnt,
    COUNT(o_ord_nuM) AS order_cnt,
    COUNT(DISTINCT CASE WHEN ol_qty_shp_td = 1
    THEN ocr_web_order ELSE NULL END)
    AS shipped_order_cnt,
    COUNT(DISTINCT CASE WHEN ol_qty_shp_td = 1
    THEN ocr_web_order ELSE NULL END)
    AS p_shipped_order_cnt,
    COUNT(CASE WHEN ol_qty_shp_td = 1
    THEN o_ord_num ELSE NULL END)
    AS shipped_item_cnt,
    COUNT(DISTINCT CASE WHEN ol_qty_shp_td = 0
    AND ol_can_rsn_cd = 'NL'
    THEN ocr_web_order ELSE NULL END)
    AS nla_order_cnt,
    COUNT(CASE WHEN ol_qty_shp_td = 0
    AND ol_can_rsn_cd = 'NL'
    THEN o_ord_num ELSE NULL END)
    AS nla_item_cnt,
    COUNT(DISTINCT CASE WHEN ol_qty_shp_td = 0
    AND ol_can_rsn_cd <> 'NL'
    THEN ocr_web_order ELSE NULL END)
    AS open_order_cnt,
    COUNT (CASE WHEN ol_qty_shp_td = 0
    AND ol_can_rsn_cd <> 'NL'
    THEN o_ord_num ELSE NULL END) AS open_item_cnt,
    SUM(ol_price) AS order_price_tot,
    SUM(CASE WHEN ol_qty_shp_td = 0
    AND ol_can_rsn_cd = 'NL'
    THEN ol_price ELSE NULL END) AS nla_price_tot
    FROM Orders;

    The results are:


    ocr_web_order_cnt

    2071



    order_cnt

    5387



    shipped_order_cnt

    1870



    shipped_item_cnt

    4680



    nla_order_cnt

    116



    nla_item_cnt

    134



    open_order_cnt

    178



    open_item_cnt

    573



    order_price_tot

    11



    nla_price_tot

    1



    The trouble is that for each order, there are multiple items, which create multiple DB records. This means that a ocr_web_order value of 12345 could appear on four different records with four different o_ord_num values. Three of the four records could have an ol_qty_shp_td value of 1, while one of the four could have a value of 0 which means three of the four items shipped.

    When selecting the number of unique orders that shipped, and the number of unique orders that didn't ship, the sum of the two is larger than the total number of unique orders due to the fact that some orders are counted twice through both statements.

    I know we've got some talented SQL guys here on BHW, if anyone is up to it, quick $20.

    Thanks!
     
    Last edited: Jan 8, 2012