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 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?