calculate attendance and mark absentees for each day MS SQL

Question

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.

You've tagged c#, have you attempted the calculation in c#? If so, please attach your [c# code](http://stackoverflow.com/help/how-to-ask). — lloyd, May 30 '15 at 11:24

score 1 · Answer 1 · answered May 30 '15 at 11:43

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

score 0 · Answer 2 · answered May 30 '15 at 12:42

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 :)

Java Main · Answer 3 · 2015-05-30T15:00:15.383

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

calculate attendance and mark absentees for each day MS SQL

3 Answers3