Need help with UNIQUE values in query
We are trying to return a single list of unique our departments with a "status" if all reports were not received for a given quarter. Eg.
However if previous quarters status was "red", then it duplicates the department/country as shown below:
Below is the query:
- SELECT UNIQUE
- t1."Country" "Country",
- CASE
- WHEN month(t1."Report Date") BETWEEN 10 AND 12
- THEN 'red'
- END AS "Q1",
-
- CASE
- WHEN month(t1."Report Date") BETWEEN 1 AND 3
- THEN 'red'
- END AS "Q2",
-
- CASE
- WHEN month(t1."Report Date") BETWEEN 4 AND 6
- THEN 'red'
- END AS "Q3",
-
- CASE
- WHEN month(t1."Report Date") BETWEEN 7 AND 9
- THEN 'red'
- END AS "Q4"
-
- FROM "CRS" t1, "FY" t5
-
- WHERE t1."Ignore" = 'false'
- AND t1."Received" = 'false'
- AND t1."Report Date" BETWEEN t5."FYBegin" AND t5."FYEnd"