日期:2014-05-17 浏览次数:20501 次
SELECT NAME ,COUNT(ATD_DATE ) FROM EMPLOYEE A JOIN
(select ATD_SN,MIN(ATD_DATE)ATD_DATE FROM ATD GROUP BY ATD_SN,convert(char(11),atd_Date,120))B ON A.SN=B.ATD_SN GROUP BY NAME
SELECT E.SN, E.Name, SUM(D.DAYCOUNT) AS Total
FROM Employee E LEFT JOIN
(SELECT ATD_SN, CONVERT(varchar(8),ATD_DATE,112), 1 AS DAYCOUNT
FROM ATD
GROUP BY ATD_SN, CONVERT(varchar(8),ATD_DATE,112)
HAVING COUNT(1) > 1 ) D
ON E.SN=D.ATD_SN
GROUP BY E.SN, E.Name
SELECT E.NAME,COUNT(A.atd_Date) --A.ATD_SN
FROM
(
SELECT ATD_SN, CAST(convert(char(10),atd_Date,120) AS DATE) AS MYDATE, MAX(atd_Date) AS atd_Date
GROUP BY ATD_SN, CAST(convert(char(10),atd_Date,120) AS DATE)
) A INNER JOIN EMPLOYEE e on A.ATD_SN=e.SN