sql - 将从当前月份和年份的日期中提取 MINDATE 的 SQL 语句

Question

我需要一个 sql 语句来提取当前月份和年份（2021 年 5 月）的以下 MINIMUM 访问日期

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

结果：

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]
    );

谢谢您的帮助！

标签： sqlsql-server-2012yearmonth

Answer 1

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