Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    12

    Question Unanswered: Wash Sale Query Help

    I've been struggling with trying to build this report for weeks, and I'm at my wits end. Maybe what I'm trying to do isn't possible (I doubt that), or I'm just not looking at it correctly. So I'm hoping perhaps you experts here could provide a fresh set of eyes:

    I have a product sales report that, among other things, returns either a 1 or 0 for each set of products on an order. At first glance, the report I originally built worked just fine, until we discovered that Wash Sales were being counted as an actual sale. A wash sale is one that happens when a customer adds another product, and during the course of that transaction, an existing product gets changed - new price point, etc. - but the existing product itself is not truly added or removed. We use product codes to for each product and price point (not my design).

    This first sub-select looks for any customer orders that have a product (a customer can have more than one product per order) that has a reporting center of 1, and returns a 1 if they do, and a 0 if they don't:

    Code:
    NVL((SELECT 1 FROM IDST_RATE_REPORTING_CENTER RC INNER JOIN IDST_WIP_CUST_RATE WR ON RC.ACCTCORP=WR.ACCTCORP AND RC.DRATECODE=WR.RATECD
                   WHERE RC.ACCTCORP = 99999 AND WR.HOUSE=C.HOUSE AND WR.CUST=C.CUST AND WR.WPCNT=W.WPCNT AND WR.RATESIGN IN (' ','A')
                   AND (RCTR01 IN (' 1') OR RCTR02 IN (' 1') OR RCTR03 IN (' 1') OR RCTR04 IN (' 1') OR RCTR05 IN (' 1') OR RCTR06 IN (' 1')
                    OR RCTR07 IN (' 1') OR RCTR08 IN (' 1') OR RCTR09 IN (' 1') OR RCTR10 IN (' 1') OR RCTR11 IN (' 1') OR RCTR12 IN (' 1')
                    OR RCTR13 IN (' 1') OR RCTR14 IN (' 1') OR RCTR15 IN (' 1') OR RCTR16 IN (' 1') OR RCTR17 IN (' 1') OR RCTR18 IN (' 1')
                    OR RCTR19 IN (' 1') OR RCTR20 IN (' 1'))
                   AND ROWNUM = 1), 0) AS V

    The "WR.RATESIGN IN (' ','A')" specifically looks for the product being added. If it's being removed, it has a different sign (either a '-' or 'J' depending on the type).

    The problem for this particular select is that because it's looking at products (being the only way I can make this work at the moment), for orders where a customer is swapping one type of product for another (one V for another V; both having reporting center 1), it still counts the order - but we don't want that.

    If the order has a net zero change in product, then we want to return a 0, because the sales person didn't actually sell a new product.

    This sub select will give a -1 for order where the product is being removed (much like the one above where it's being added) - the only difference is the WR.RATESIGN argument:

    Code:
    NVL((SELECT -1 FROM IDST_RATE_REPORTING_CENTER RC INNER JOIN IDST_WIP_CUST_RATE WR ON RC.ACCTCORP=WR.ACCTCORP AND RC.DRATECODE=WR.RATECD
                   WHERE RC.ACCTCORP = 99999 AND WR.HOUSE=C.HOUSE AND WR.CUST=C.CUST AND WR.WPCNT=W.WPCNT AND WR.RATESIGN IN ('-','J')
                   AND (RCTR01 IN (' 1') OR RCTR02 IN (' 1') OR RCTR03 IN (' 1') OR RCTR04 IN (' 1') OR RCTR05 IN (' 1') OR RCTR06 IN (' 1')
                    OR RCTR07 IN (' 1') OR RCTR08 IN (' 1') OR RCTR09 IN (' 1') OR RCTR10 IN (' 1') OR RCTR11 IN (' 1') OR RCTR12 IN (' 1')
                    OR RCTR13 IN (' 1') OR RCTR14 IN (' 1') OR RCTR15 IN (' 1') OR RCTR16 IN (' 1') OR RCTR17 IN (' 1') OR RCTR18 IN (' 1')
                    OR RCTR19 IN (' 1') OR RCTR20 IN (' 1'))
                   AND ROWNUM = 1), 0) AS V

    What I've been struggling to do is find a way to combine both of these so that they return a 1 when it's a true add, and a 0 when either there's no add at all, or there's an add & remove (a "wash").

    Any ideas?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What I've been struggling to do is find a way to combine both of these so that they return a 1 when it's a true add, and a 0 when either there's no add at all, or there's an add & remove (a "wash").
    I couldn't understand throughly how these requirements related to your two expressions.
    Because
    (1) Although each of your expressions returned 1, -1 or null,
    but you requested to retuern 1 or 0.
    (2) There was a phrase "add & remove (a "wash")" in your requirements,
    what means "add & remove" and "wash"?

    Note: I thought that the codes which you showed were not sub-selects.
    Code:
     NVL( (SELECT ...
          )
         , 0
        ) AS V
    They must be simple expressions.


    Anyway,
    apart from my questions, it would be easy to combine your two expressions into one, like...
    Code:
    NVL( (SELECT CASE
                 WHEN WR.RATESIGN IN (' ' , 'A') THEN
                       1
                 WHEN WR.RATESIGN IN ('-' , 'J') THEN
                      -1
                 END
           FROM  IDST_RATE_REPORTING_CENTER RC
           INNER JOIN
                 IDST_WIP_CUST_RATE         WR
            ON   RC.ACCTCORP  = WR.ACCTCORP
             AND RC.DRATECODE = WR.RATECD
           WHERE RC.ACCTCORP  = 99999
             AND WR.HOUSE     = C.HOUSE
             AND WR.CUST      = C.CUST
             AND WR.WPCNT     = W.WPCNT
             AND WR.RATESIGN  IN ('-' , 'J' , ' ' , 'A')
             AND
                 (' 1') IN (  RCTR01 , RCTR02 , RCTR03 , RCTR04 , RCTR05
                            , RCTR06 , RCTR07 , RCTR08 , RCTR09 , RCTR10
                            , RCTR11 , RCTR12 , RCTR13 , RCTR14 , RCTR15
                            , RCTR16 , RCTR17 , RCTR18 , RCTR19 , RCTR20
                           )
             AND ROWNUM = 1
         )
        , 0
       ) AS V
    But again my another question, from where came the aliases "C." and "W."?
    It would be better to publish you whole query.
    Last edited by tonkuma; 03-16-12 at 17:35. Reason: Add Note.

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by tonkuma View Post
    I couldn't understand throughly how these requirements related to your two expressions.
    Because
    (1) Althou each of your expressions returned 1, -1 or null,
    but you requested to retuern 1 or 0.
    (2) There was a phrase "add & remove (a "wash")" in your requirements,
    what means "add & remove" and a "wash".
    I'm sorry, what I'm trying to do is test for the following conditions:

    1) If the product is truly being added - return 1
    2) If the product is being removed - return 0
    3) If the product is being removed & added in the same transaction - return 0

    For the purposes of this report, we don't care about counting any that fall under condition #2.

    Quote Originally Posted by tonkuma View Post
    Anyway,
    apart from my questions, it would be easy to combine your two expressions into one, like...
    Code:
    NVL( (SELECT CASE
                 WHEN WR.RATESIGN IN (' ' , 'A') THEN
                       1
                 WHEN WR.RATESIGN IN ('-' , 'J') THEN
                      -1
                 END
           FROM  IDST_RATE_REPORTING_CENTER RC
           INNER JOIN
                 IDST_WIP_CUST_RATE         WR
            ON   RC.ACCTCORP  = WR.ACCTCORP
             AND RC.DRATECODE = WR.RATECD
           WHERE RC.ACCTCORP  = 99999
             AND WR.HOUSE     = C.HOUSE
             AND WR.CUST      = C.CUST
             AND WR.WPCNT     = W.WPCNT
             AND WR.RATESIGN IN ('-' , 'J' , ' ' , 'A')
             AND
                 (' 1') IN (  RCTR01 , RCTR02 , RCTR03 , RCTR04 , RCTR05
                            , RCTR06 , RCTR07 , RCTR08 , RCTR09 , RCTR10
                            , RCTR11 , RCTR12 , RCTR13 , RCTR14 , RCTR15
                            , RCTR16 , RCTR17 , RCTR18 , RCTR19 , RCTR20
                           )
             AND ROWNUM = 1
         )
        , 0
       ) AS V
    But again my another question, from where came the aliases "C." and "W."?
    It would be better to publish you whole query.
    I had experimented with a similar CASE statement, but not quite like that. I'll have to try it and see. I have a list of orders that I know shouldn't return a 1, so I can compare the results.

    C & W are two tables not directly related to this part of the query - they're joined here to ensure that the results being returned are specifically for the same customer (alias C) and order (alias W). Posting the entire query could potentially cause trouble with my employer so I'm somewhat hesitant to do so, but I may have to.

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by N.B. View Post
    I'm sorry, what I'm trying to do is test for the following conditions:

    1) If the product is truly being added - return 1
    2) If the product is being removed - return 0
    3) If the product is being removed & added in the same transaction - return 0
    What about this?
    Code:
    SELECT CASE 
              WHEN EXISTS (
                   SELECT 1 FROM ...................
                   WHERE ..............................   
                      AND ratesign IN ( ' ', 'A' )
                      AND NOT EXISTS (
                          SELECT 1 FROM ...........................
                          WHERE .................................. 
                                 AND ratesign IN ( '-', 'J' )
                         )
                  )
             THEN 1
             ELSE 0
          END result
    FROM dual;

  5. #5
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by kordirko View Post
    What about this?
    Code:
    SELECT CASE 
              WHEN EXISTS (
                   SELECT 1 FROM ...................
                   WHERE ..............................   
                      AND ratesign IN ( ' ', 'A' )
                      AND NOT EXISTS (
                          SELECT 1 FROM ...........................
                          WHERE .................................. 
                                 AND ratesign IN ( '-', 'J' )
                         )
                  )
             THEN 1
             ELSE 0
          END result
    FROM dual;
    Sir... you just solved a problem that's been vexing me for months. Thank you; this worked perfectly and passed all test cases.

Posting Permissions

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