试试这个：

  SELECT  InDevice.UserId 
，MIN（InDevice.LogDate） [在 时间] 
，MAX（OutDevice.LogDate）[Out 时间] 
， CONVERT （ nvarchar （ 10 ），InDevice.LogDate， 20 ）[日志日期] 
，COUNT（InDevice.LogDate）[ IN 计数] 
，COUNT（OutDevice.LogDate）[OUT计数] 
 FROM  DeviceLogs InDevice 
 LEFT   OUTER   JOIN  DeviceLogs OutDevice  ON  InDevice.UserID = OutDevice.UserID 
& OutDevice.DeviceID  in （ 25 ， 30 ）  -   假设这些是用于打孔的唯一机器 
 WHERE  InDevice.DeviceID  （ 26 ， 31 ）  -   假设这些机器用于打孔 
 GROUP   BY  InDevice.UserId， CONVERT （ nvarchar （ 10 pan>），InDevice.LogDate， 20 ）

Table Schema
(
DeviceLogId int
DeviceId    int
UserId      int
LogDate    datetime
)

Table data
DeviceLogId DeviceId UserId LogDate            
112            25    66     2015-07-22 11:02:15.000
332            25    66     2015-07-22 17:29:25.000
555            25    88     2015-07-23 19:09:35.000
779            25    67     2015-07-24 16:23:49.000
1003    29  17  2015-07-18 13:03:04.000

I want output like

Intime                  Outtime                logdate   Incount    OutCount
2015-01-01 10:22:29.000 2015-01-01 19:58:43.000 2015-01-01  7       7
2015-01-02 09:52:26.000 2015-01-02 20:25:25.000 2015-01-02  2       2

I want to display user
office intime and office outtime, number of time punch machine count...

select e.Intime,e.Outtime,e.logdate,e.Incount,e.OutCount from
    (
     select a.intime as Intime,b.outtime as Outtime,c.logdate,c.InCount as Incount,d.OutCount as OutCount from


      (
          select min(logdate) intime,cast(LogDate as date) logdate,userid
          from DeviceLogs  where deviceid in (26,31) and  cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
          group by cast(LogDate as date), UserId
      )a  inner join
      (
          select max(logdate) outtime,cast(LogDate as date) logdate,userid from DeviceLogs
          where deviceid in (25,30) and   cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
          group by cast(LogDate as date), UserId
      ) b on a.logdate = b.logdate

      left join
      (
       select UserId, cast(LogDate as date) logdate ,count(DeviceLogId) as InCount from DeviceLogs
        where deviceid in (26,31) and  cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
        Group by UserId, cast(LogDate as date)
       )c on b.logdate = c.logdate

       left join
       (
       select UserId, cast(LogDate as date) logdate ,count(DeviceLogId) as OutCount from DeviceLogs
        where deviceid in (25,30) and  cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
        Group by UserId, cast(LogDate as date)
        )d on c.logdate = d.logdate

    )e

I have create query but its working for single user only... I want this result for all user of the system.


Basically I want to dispaly all employee intime,outime,number of machine input , and out put count.

Waiting for your reply
Thanks in advance.

Try this:

SELECT InDevice.UserId
    , MIN(InDevice.LogDate) [In Time]
    , MAX(OutDevice.LogDate) [Out Time]
    , CONVERT(nvarchar(10), InDevice.LogDate,20) [Log Date]
    , COUNT(InDevice.LogDate) [IN Count]
    , COUNT(OutDevice.LogDate) [OUT Count]
FROM DeviceLogs InDevice
LEFT OUTER JOIN DeviceLogs OutDevice ON InDevice.UserID = OutDevice.UserID
    AND OutDevice.DeviceID in (25,30) --given that these are the only machine that for Punching Out
WHERE InDevice.DeviceID in (26,31) --given that these machine for Punching In
GROUP BY InDevice.UserId, CONVERT(nvarchar(10), InDevice.LogDate,20)