问题描述:

this is a table for attendance logs:

CREATE TABLE [dbo].[DeviceLogs] (

[DeviceLogId] INT NOT NULL,

[UserId] NVARCHAR (50) NOT NULL,

[LogDate] DATETIME NOT NULL,

);

this is my employee table:

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,

);

We will insert attendance logs in the first table.

Now i want to calculate attendance for each employee in each day.

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

This is the MS SQl query which i'm using.

But it is giving me result only if the person has attendance logs.

I want to calculate attendance even in the person is absent.

I want it to repeat it for everyday.

Please help me here.

网友答案:

If am not wrong there should be a User table which contains the list of all the users.

You have to Left/Right outer join the User table with devicelogs table.

Something like this.

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 
网友答案:

To get daily reports / lists it's usually best to have a date table that contains all the possible dates you'll ever need, for example 1.1.2000 - 31.12.2099. Using it you can easily create a query that groups by that date, like this:

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

You could do it of course with devicelog too, but if there's ever a single date without any logs you'll miss that completely.

Didn't try this, hopefully there's no errors :)

网友答案:

First of all you must clarify why the log table may have some users with no log date because the logdate is set to NOT NULL and you are quering log table and expecting to get some users with no logdate.

The algorithm is : When a employee is not in the log table set him as absent and put the duration equal to 0 . Otherwhise calculate the duration and set him as present . Plus get his frist date of log and last date of log.

1) Get a table showing users with null dates

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

2) apply your code to this table

相关阅读:
Top