如何在SQL中获取当前日期之后的最早日期？

Got it, let's adjust your query to pull the earliest due date that falls after the current date, instead of just the smallest date overall. Here's the modified version, plus a breakdown of the key changes:

Modified Query #

SELECT DISTINCT 
    StyleItem.STYLE, 
    StyleItem.COLOR, 
    StyleItem.SIZE, 
    StyleItem.UPC, 
    INVENTORY.ITEMNO, 
    SUM(QOHQTY) AS QOHQTY, 
    dates.Dates, 
    ProdOrderDetail.PRODLINEQTY 
FROM (
    SELECT 
        ProdOrderDetail.ITEMNO, 
        MIN(DUEDATE) AS Dates 
    FROM ProdOrderDetail 
    -- Filter to only dates after today
    WHERE DUEDATE > CURRENT_DATE
    GROUP BY ProdOrderDetail.ITEMNO
) AS dates 
RIGHT OUTER JOIN Inventory ON Inventory.ITEMNO = dates.ITEMNO 
LEFT OUTER JOIN ProdOrderDetail ON ProdOrderDetail.DUEDATE = dates.dates AND ProdOrderDetail.ITEMNO = dates.ITEMNO 
INNER JOIN StyleItem ON styleitem.itemno = Inventory.ITEMNO 
WHERE 
    StyleItem.DIVISION = 'HH' 
    AND StyleItem.MERCHGROUPA = 'NATL' 
    AND StyleItem.MERCHGROUPB <> 'MARKETING'
    -- Optional: Uncomment below to exclude items with no future due dates
    -- AND dates.Dates IS NOT NULL
GROUP BY 
    Inventory.ITEMNO, 
    dates.dates, 
    ProdOrderDetail.PRODLINEQTY, 
    StyleItem.STYLE, 
    StyleItem.COLOR, 
    StyleItem.SIZE, 
    StyleItem.UPC 
ORDER BY 
    styleitem.style, 
    styleitem.color

Key Changes Explained #

• Filter future dates first: Added WHERE DUEDATE > CURRENT_DATE to the dates subquery. This ensures we only consider due dates that are after today before calculating the minimum.
• Handle empty future dates: If an item has no upcoming due dates, dates.Dates will be NULL. If you want to exclude these items entirely, uncomment the AND dates.Dates IS NOT NULL line in the main WHERE clause.
• Database-specific adjustments: Depending on your SQL dialect, you might need to replace CURRENT_DATE with the appropriate function:
  • SQL Server: CAST(GETDATE() AS DATE) (to get just the date without time)
  • Oracle: SYSDATE
  • MySQL: CURDATE()
  • PostgreSQL: CURRENT_DATE

内容的提问来源于stack exchange，提问作者Srini