I want a query to display the number of containers that only contain products so the date is less than 01/01/2019 and date is not null..
Example:
my table : tab
| Num_Container | Num_Product | Date_Product |
|---|---|---|
| 1 | A1 | 01/01/2020 |
| 1 | A2 | 01/01/2018 |
| 1 | A3 | 01/01/2021 |
| 2 | A4 | 01/01/2017 |
| 2 | A5 | 01/01/2018 |
| 2 | A6 | 01/01/2019 |
| 3 | A7 | Null |
| 3 | A8 | 01/01/2019 |
| 3 | A9 | 01/01/2016 |
The expected result is:
| Num_Container |
|---|
| 2 |
The containers must contain only products less than or equal to the date 01/01/2019 and date is not null.
CodePudding user response：
maybe like this
select Num_Container
from yourtable
group by Num_Container
having max(Date_Product)< DATE'2019-01-01'
CodePudding user response：
You can try to use condition aggregate function in HAVING which can use reverse logic get counting is 0
SELECT Num_Container
FROM tab
GROUP BY Num_Container
HAVING COUNT(CASE WHEN Date_Product > TO_DATE('01/01/2019', 'DD/MM/YYYY') OR Date_Product IS NULL THEN 1 END) = 0