I want a query to display the number of containers that only contain products so the date is less than 01/01/2019.

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	01/01/2022
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.

CodePudding user response：

Try:

select Num_Container
from test_tbl
group by Num_Container
having max(Date_Product) <= '01/01/2019' and min(Date_Product)<= '01/01/2019';

Result:

NUM_CONTAINER
2

Demo

Or in your case only MAX date is sufficient

select Num_Container
from test_tbl
group by Num_Container
having max(Date_Product) <= '01/01/2019' ; 

Demo

CodePudding user response：

This SQL statement can be a solution:

select num_container
  from your_table
 group by num_container
 having max(date_product) <= date '2019-01-01'
 order by num_container;

CodePudding user response：

You can use the min and max functions as a between and use the having as a where