SQL Statement that will pull MINDATE from dates for current month and year

42 views Asked by At

I need a sql statement that will pull the following the MINIMUM visit date for the current month and year (May 2021)

emp_id  visit_date  
1       5/3/2021
1       5/4/2021
1       5/10/2021
1       6/5/2021
2       5/2/2021
2       5/6/2021
2       6/7/2021
3       5/9/2021
3       7/8/2021

Result:

1   5/3/2021
2   5/2/2021
3   5/9/2021



SELECT emp_id, visit_date
FROM
THS_ClientVisit 
WHERE 
   THS_ClientVisit.visit_date =
    (
        SELECT MIN(THS_ClientVisit.visit_date)
        FROM dbo.THS_ClientVisit
        WHERE MONTH YEAR from current month[i.e. 05-2021] = MONTH YEAR from visit_date {i.e. 05-2021]
    );

Thank you for your assistance!

1

There are 1 answers

0
dnelson On
SELECT
    emp_id,
    MIN(CONVERT(date,rev_timein)) as MinVisitDate
FROM
    THS_ClientVisit
WHERE
   DATEPART(mm, THS_ClientVisit.rev_timein)= MONTH(CURRENT_TIMESTAMP) AND DATEPART(yy, THS_ClientVisit.rev_timein)=YEAR(CURRENT_TIMESTAMP)
GROUP BY
    THS_ClientVisit.emp_id