sql server语言 - 计算出勤和标记缺勤，每天MS sql

Question

这是一个考勤日志表:

CREATE TABLE [dbo].[DeviceLogs] (
    [DeviceLogId]  INT            NOT NULL,
    [UserId]       NVARCHAR (50)  NOT NULL,
    [LogDate]      DATETIME       NOT NULL,
);

这是我的员工表:

CREATE TABLE [dbo].[Employees] (
    [EmployeeId]             INT            IDENTITY (1, 1) NOT NULL,
    [EmployeeName]           NVARCHAR (50)  NULL,
    [UserId]           NVARCHAR (50)  NOT NULL,
    [Gender]                 NVARCHAR (255) NULL,
    [DepartmentId]           NVARCHAR (255) NULL,
    [Designation]            NVARCHAR (255) NULL,
    [CategoryId]             INT            NULL,
    [DOJ]                    DATETIME       NULL,
    [DOR]                    DATETIME       NULL,
    [Status]                 NVARCHAR (255) NULL,
    [DOB]                    DATETIME       NULL,
);

我们将在第一个表中插入考勤日志。

现在我想计算每个员工每天的出勤率。

SELECT [userid]                                           AS identit, 
       CONVERT(VARCHAR, userid), 
       Min(logdate)                                       AS lowtime, 
       Max(logdate)                                       AS hightime, 
       CONVERT(VARCHAR, Max(logdate) - Min(logdate), 108) AS dur, 
       CASE 
         WHEN CONVERT(VARCHAR, Max(logdate) - Min(logdate), 108) IS NOT NULL 
       THEN 
         'present' 
         ELSE 'Absent' 
       END                                                AS Status 
FROM   [dbo].[devicelogs] 
GROUP  BY [userid] 
ORDER  BY userid DESC 

这是我正在使用的MS SQl查询。但是只有当这个人有考勤记录时，它才会给我结果。

即使有人缺席，我也要计算出勤率。我想让它每天都重复。

Answer 1

如果我没有错，应该有一个User表，其中包含所有用户的列表。

你必须用devicelogs表来Left/Right outer join User表

就像这样。

SELECT U.[userid]                                         AS identit, 
       CONVERT(VARCHAR, U.userid), 
       Min(logdate)                                       AS lowtime, 
       Max(logdate)                                       AS hightime, 
       CONVERT(VARCHAR, Max(logdate) - Min(logdate), 108) AS dur, 
       CASE 
         WHEN d.userid IS NOT NULL THEN 'present' 
         ELSE 'Absent' 
       END                                                AS Status 
FROM   [users] U -- Replace with the table that contains all the users
       LEFT OUTER JOIN [dbo].[devicelogs] D 
                    ON U.userid = D.userid 
GROUP  BY [userid] 
ORDER  BY U.userid DESC 

Answer 2

要获得每日报告/列表，通常最好有一个包含所有可能需要的日期的日期表，例如1.1.2000 - 31.12.2099。使用它，您可以轻松地创建按日期分组的查询，如下所示:

SELECT 
    e.userid,
    d.[date],
    l.first,
    l.last,
    case when l.first is null then 'Absent' else 'Present' end as status
FROM   
    [dbo].[Employees] e
    cross join [dbo].[Dates] d
    outer apply (
        select min(logdate) as first, max(logdate) as last
        from [dbo].[devicelogs] l
        where l.userid = e.userid and
        l.logdate >= d.[date] and l.logdate < dateadd(day, 1, d.[date]
    ) l
where d.[date] >= '20150501' and d.[date] < getdate()
GROUP BY e.userid, d.[date] 
ORDER BY e.userid desc, d.[date] desc

你当然也可以用devicelog来做，但是如果有一个没有任何日志的日期，你就会完全错过它。

没有尝试这个，希望没有错误:)

Answer 3

首先，您必须澄清为什么日志表中可能有一些没有日志日期的用户，因为logdate被设置为NOT NULL，而您正在查询日志表并期望得到一些没有logdate的用户。

算法为:当员工不在日志表中时，将其设置为缺席，并将持续时间设置为0。否则，计算持续时间并将他设置为在场。再加上他的第一个日志日期和最后一个日志日期。

1)获取一个表，显示具有空日期的用户

 select E.UserID as UserID,L.LogDate as logdate from  employee E
 full outer join
 DeviceLogs L
 on E.UserID=L.UserID