我如何... creae查询报告以显示每个员工的工作时间 [英] How do I...creae query for report for display every employee working hours

问题描述

表架构
（
 DeviceLogId int 
 DeviceId int 
 UserId int 
 LogDate datetime 
）

 表 < span class =code-leadattribute> data  
  DeviceLogId   DeviceId   UserId   LogDate  
  112   25   66   2015-07-22   11:02:15 。 000  
  332   25   66   2015-07-22  < span class = code-leadattribute> 17:29:25 。 000  
  555  < span class =code-leadattribute> 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  

 我 想要 输出 喜欢 
 
 银泰 < span class =code-leadattribute> 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  < span class =code-leadattribute> 20:25:25 。 000   2015-01-02   2   2  

我想要显示用户

办公室银行和办公室外出时间，打卡机数量...

 选择 e.Intime，e.Outtime，e.logdate，e.Incount，e.OutCount 来自 
（
 选择 a.intime 为银泰，b.outtime  as  Outtime，c.logdate，c.InCount  as  Incount，d.OutCount  as  OutCount 来自 
 
 
（
 选择 min（logdate）intime，cast（LogDate  as   date ）logdate，userid 
 来自 DeviceLogs 其中 deviceid   （ 26 ， 31 ）和 cast（ LogDate  as   date ）  '  2015-01-01' 和 '  2015-01-02' 和 UserId = 7 
  group   by  c ast（LogDate  as   date ），UserId 
）a 内部  join  
（
 选择 max（logdate） outtime，cast（LogDate  as   date ）logdate，userid 来自 DeviceLogs 
 其中 deviceid  in （ 25 ， 30 ）和 cast（LogDate  as   date ）  '  2015-01-01' 和 '  2015-01-02' 和 UserId = 7 
  group   by  cast（LogDate  as   date ），UserId 
）b  on  a.logdate = b.logdate 
 
  left   join  
（
 选择 UserId，强制转换（LogDate  as   date ）logdate，count（DeviceLogId） as  InCount  from  DeviceLogs 
 < span class =code-keyword> where  deviceid  in （ 26 ， 31 ）和 cast（LogDate  as   date ）下注ween  '  2015-01-01' 和 '  2015-01-02' 和 UserId = 7 
 组 按 UserId，强制转换（LogDate  as   date ）
）c  on  b .logdate = c.logdate 
 
  left   join  
（
 选择 UserId，强制转换（LogDate  as   date ）logdate，count（DeviceLogId） as  OutCount 来自 DeviceLogs 
 其中 
   （ 25 ， 30 ）和 cast（ LogDate  as   date ）  '  2015-01-01' 和 '  2015-01-02' 和 UserId = 7 
 组 按 UserId，强制转换（LogDate  as   date ）
）d  on  c.logdate = d.logdate 
 
）e 

我创造了查询，但它仅适用于单个用户...我希望此结果适用于系统的所有用户。





基本上我想要显示所有员工的时间，时间，机器输入数量和输出数量。



等待你的回复

提前致谢。

解决方案

试试这个：

  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 ），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)

这篇关于我如何... creae查询报告以显示每个员工的工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！