Search code examples
sqlfilteringteradataunionwhere-clause

Union results either Blank or Unfiltered

We have data that separates Paid and Rejected claims. I need to see results of both and therefore have to do a . (Our data is a mess. I am also aliasing for confidentiality/HIPPA compliance. Please try not to get hung up on those parts because I can't change it.)

SELECT CustID, code, date, 'Paid' AS Srce
FROM Paid.Claims
INNER JOIN Paid.Medical
    ON Paid.Claims.id = Paid.Medical.id
    AND Paid.Claims.blind = Paid.Medical.blind
WHERE Paid.Claims.date BETWEEN '2022-01-01' AND '2022-06-07'
    AND Paid.Medical.code IN ('88521','88522','88523','88524','88525')
    AND Paid.Claims.custID IN ('N065468','N095843','N001086')
UNION
SELECT CustID, code, date, 'Filter' AS Srce
FROM Rejected.Claims
INNER JOIN Rejected.Medical
    ON Rejected.Claims.id = Rejected.Medical.id
    AND Rejected.Claims.blind = Rejected.Medical.blind
WHERE Rejected.Claims.date BETWEEN '2022-01-01' AND '2022-06-07'
    AND Rejected.Medical.code IN '88521','88522','88523','88524','88525')
    AND Rejected.Claims.custID IN ('N065468','N095843','N001086')

It's based on a query that the person before me made, and that one works but it's also much simpler because it pulls less from less places. My outcomes so far have been:

  1. Leave the out of the Paid data but still in the Rejected data and get EVERY RESULT. None of the seems to be working.
  2. Include the in both and get no results. not working, but in the opposite direction.

I have also tried

SELECT *
FROM (
    everything above with and without filters
    ) AS results
WHERE <filters same as above>

And results set is empty.

I have tried with and without aliasing with no changes in what's returned.

I'm expecting about 200 results that SHOULD look something like this:

| CustID  | code  | date       | Srce   |
| ------- | ----- | ---------- | ------ |
| N065468 | 88522 | 2022-04-04 | Paid   |
| N095843 | 88521 | 2022-03-09 | Paid   |
| N001086 | 88524 | 2022-05-20 | Filter |

Back to troubleshooting.

Solution

  • We have many duplicate fields as a result of our flattening process. My formatting may have been correct, but it turns out I was pulling from some of the wrong places. I solved the problem by creating a franken-query using several others made by my predecessor with a similar element. Depending on how I was attempting to alias everything, the filters were likely pulling from different fields than the ones I was -ing.