I have a list of order dates for various vendors on specific items. I want to be able to pull the MIN orderdate but also have the min order date be within a specific date range (i.e. first day of last month thru end of next month).
Dataset:
| Item | Vendor | Order Date | Notes |
|---|---|---|---|
| 1 | ABC | 1/1/2020 | <-MIN ORDERDATE for item and vendor |
| 1 | ABC | 4/6/2022 | |
| 1 | ABC | 6/6/2023 | |
| 1 | ABC | 6/6/2024 | |
| 1 | ABC | 8/8/2024 | |
| 1 | ABC | 8/20/2024 | |
| 1 | DEF | 8/4/2024 | |
| 1 | DEF | 9/1/2024 | |
| 1 | DEF | 9/14/2024 | |
| 1 | DEF | 9/20/2024 | |
| 1 | DEF | 10/9/2024 | |
| 1 | DEF | 12/12/2024 | |
| 2 | ABC | 11/11/2021 | <=MIN ORDERDATE for item and vendor |
| 2 | ABC | 3/3/2023 | |
| 2 | ABC | 10/10/2023 | |
| 2 | ABC | 8/1/2024 | |
| 2 | DEF | 8/2/2024 | <-MIN ORDERDATE for item and vendor |
| 2 | DEF | 8/7/2024 | |
| 2 | DEF | 9/4/2024 | |
| 2 | DEF | 10/1/2024 |
I want the query, that is using the date range of (8/1/2024-10/30/2024), to produce the folllowing results below. Even thouhgh there are orderdates that fall within the range, i dont want them included in the results unless the minimum order dates for that vendor and item fall with the date range).
| Item | Vendor | Earliest Order |
|---|---|---|
| 1 | DEF | 8/4/2024 |
| 2 | DEF | 8/2/2024 |
SELECT ITEM, VENDOR, MIN(ORDERDATE::timestamp) AS "Earliest Order"
FROM ORDERS
WHERE
ORDERDATE between date_trunc('month', CURRENT_DATE) - interval '1 month' and date_trunc('month', CURRENT_DATE) + interval '2 month' -interval '1 day'
Group by 1,2
Filter by min(orderdate).
where filters row-by-row and does not allow aggregate functions, use having instead. having filters on the grouped (aggregated) data.
select
item,
vendor,
min(orderdate) AS "Earliest Order"
from orders
group by 1, 2
having min(orderdate) between
date_trunc('month', CURRENT_DATE) - interval '1 month' and
date_trunc('month', CURRENT_DATE) + interval '2 month' - interval '1 day';