如何在SQL Server中获取顶级记录 [英] How to fetch top records in sql server

问题描述

I have a table like this

empID     empName          logDate               Type
104	Access card 3	2014-07-01 09:37:10.000	  IN
104	Access card 3	2014-07-01 11:15:29.000	  OUT
104	Access card 3	2014-07-01 11:49:37.000	  IN
104	Access card 3	2014-07-01 19:59:43.000	  OUT
----------------------------------------------------
104	Access card 3	2014-07-02 08:37:10.000	  IN
104	Access card 3	2014-07-02 11:15:29.000	  OUT
104	Access card 3	2014-07-02 11:49:37.000	  IN
104	Access card 3	2014-07-02 18:58:26.000	  OUT

Desired Output


EID      Ename            Date             FirstIN                   LastOut  
104	Access card 3	2014-07-01    2014-07-01 09:37:10.000    2014-07-01 19:59:43.000
104	Access card 3	2014-07-02    2014-07-02 08:37:10.000    2014-07-02 18:58:26.000

 ;WITH LoginCTE AS
(
    SELECT empName,
           logDate,
           max(logTime) AS Login
    FROM   K_Master_EmpAttendanceDet
    WHERE  Type = 'IN' group by empname,logDate
),LogoutCTE AS

(
    SELECT empName,
           logDate,
           max(logTime) AS Logout
    FROM   K_Master_EmpAttendanceDet
    WHERE  Type = 'OUT'group by empname,logDate
)
SELECT  distinct T.logDate, T.empID,
       T.empName,  
       Login,
       Logout
FROM   K_Master_EmpAttendanceDet T
       JOIN LoginCTE I
           ON T.empName = I.empName and
              T.logDate = I.logDate 
       JOIN LogoutCTE O
           ON T.empName = O.empName and
              T.logDate = O.logDate where T.empid=104   group by T.logDate, T.empID, T.empName, Login,Logout order by T.empName

这是查询.不工作请给我错误的地方

Here is the query . Not working. Please give explantion where i went wrong

推荐答案

尝试一下:

Try this:

SELECT DISTINCT empid, empname,
(SELECT MIN(logdate) FROM table1 WHERE type='IN' AND
 DATEADD(dd, 0, DATEDIFF(dd, 0, logdate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, t1.logdate))),
(SELECT MAX(logdate) from table1 WHERE type='OUT' AND
 DATEADD(dd, 0, DATEDIFF(dd, 0, logdate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, t1.logdate)))
FROM table1 t1

希望这对您有帮助

Hope this helps you

SELECT empID, empName, CONVERT(VARCHAR(10),logDate,105) AS Date,
MIN(CASE WHEN Type = 'IN' THEN CONVERT(char(12), logDate, 108)  END) AS 'FirstIN',
MAX(CASE WHEN Type = 'OUT' THEN CONVERT(char(12), logDate, 108)  END) AS 'LastOut'
FROM TABLE1
GROUP BY empID,empName,CONVERT(VARCHAR(10),logDate,105)

这篇关于如何在SQL Server中获取顶级记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！