I am trying to retrieve data that has "Positive" result and the code set that contains any of these codes: [U071, R12, Z515, R509] as my desired goal for Table 1. I have tried using "or" but it kept giving me a mixture of negative and positive results instead of positive results only. I hope my question is clear as I am fairly new here.
Desired goal for Table 1:
| ID | START_DATE | TEST RESULT | Code | END_DATE |
|---|---|---|---|---|
| 1 | 03-31-2021 | positive | U071 | 03-31-2021 |
| 2 | 06-06-2020 | positive | R12 | 06-09-2020 |
| 2 | 06-06-2020 | positive | Z515 | 06-10-2020 |
| 3 | 06-30-2021 | positive | R509 | 07-02-2021 |
| 4 | 08-08-2020 | positive | U071 | 08-10-2020 |
| 4 | 08-09-2020 | positive | Z515 | 08-10-2020 |
Note: I would also like ID 2 to be grouped as 1.
The problem:
| ID | START_DATE | TEST RESULT | Code | END_DATE |
|---|---|---|---|---|
| 1 | 03-31-2021 | positive | U071 | 03-31-2021 |
| 2 | 06-05-2020 | negative | R12 | 06-09-2020 |
| 2 | 06-05-2020 | negative | Z515 | 06-09-2020 |
| 3 | 06-03-2021 | positive | R509 | 07-02-2021 |
| 4 | 08-10-2020 | negative | U071 | 08-10-2020 |
| 4 | 08-06-2020 | positive | Z515 | 08-10-2020 |
My code:
CREATE TABLE table1 AS
select ID
,TEST_RESULT
,START_DATE
,END_DATE
,Code
from table0
where TEST_RESULT = 'POSITIVE' AND Code = 'U071' OR Code = 'Z20828' OR DIAGNOSIS_CD = 'Z20822' OR DIAGNOSIS_CD = 'R112' OR DIAGNOSIS_CD = 'R509'
group by ID, START_DATE
ORDER BY ID;
>Solution :
I think the real issue is you did not use ( after the and. But you can use IN to make it clearer — like this:
where TEST_RESULT = 'POSITIVE' AND (Code IN ('U071','Z20828') OR DIAGNOSIS_CD IN ('Z20822','R112','R509'))