SQL Select for Wash Sales

Toad Data Point
1

I have a report that I’m developing that I need to exclude wash sales from. A wash sale is one in which a customer has an existing service, and during the course of adding another, that service is swapped for the same thing at a different price, etc. It’s considered a wash because no net gain for that service was achieved.

This is the select as it stands to count the sales:

  NVL((SELECT 1 FROM I_R_R_C RC INNER JOIN I_W_C_R WR ON RC.AC=WR.AC AND RC.DRC=WR.RC
  	           WHERE RC.AC = 99999 AND WR.H=C.H AND WR.C=C.C AND WR.WPCNT=W.WPCNT AND WR.RS 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

I apologize for the somewhat cryptic formatting - the table names could reveal my employer :frowning: Currently this select works perfectly to count the adds for any service with the RCTR of 1 - including those that would technically be a wash. I’ve been trying to figure out how to add a condition to this that would look for any services being removed in the same order that have a RCTR of 1, and result in a 0, but I’ve been stumped.

The WR.RS determines whether it’s an add or remove (blank or A is adding; - or J is removing).

Anyone have any ideas?

2

I tried to understand this and I could not understand it very well. Is there a way you could show two simplified tables with sample data, and the output you would like to see?
It’s not necessary to include real table names, column names, or data for a sample test case.

3

It’s ok, I’ve spent all weekend trying to come up with an example set of tables that would make sense, but there are so many involved here I’m not sure how to do so.

So I’m just going to use the full query with the table names.

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:

  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:

  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”).

4

As it turns out, there was a fairly simple solution to this problem. I ended up using a CASE statement with an EXISTS and NOT EXISTS conditions.