Search code examples
sqlsql-servermin

SQL Server : returning customers with minimum dates

I'm trying to run a query that will return all customer IDs that had their FIRST requisition created yesterday. I keep getting hung up on how to use the MIN function coupled with HAVING.

The statement in the WHERE clause works to compare the date to yesterday, but I keep getting stuck on how to write it showing that the company had the FIRST/MINIMUM start date yesterday.

Sample is below to add some color:

SELECT
    P.CUSTOMERID,
    M.CREATEDDATE
FROM 
    DBO.MATCH M
INNER JOIN 
    DBO.POSITION P ON M.REQUISIIONID = P.REQUISITIONID
WHERE
    DATEADD(d, (DATEDIFF(d, 0, m.CREATEDDATE)), 0) = DATEADD(d, (DATEDIFF(d, 0, GETDATE() - 1)), 0)
Solution

  • I think you want to group by customer, and filter with a having clause:

    select p.customerid, min(m.createddate) min_createddate
from dbo.match m
inner join dbo.position p on m.requisiionid = p.requisitionid
group by p.customerid
having min(m.createddate) = dateadd(day, -1, convert(date, getdate())