How can I get the absence days of all employee? SQL Server

Question

How can I get the absence days and the present days of all employees , I would like an output like this:

employeeId      DATE      Status

    1        2021/03/04     Absent
    1        2021/03/05     Present
    2        2021/03/04     Present
    2        2021/03/05     Present

What I can get now only the present days :

select distinct DATEPART(dw,er.AddAt) as dayId,
DATENAME(dw,er.AddAt) as dayname,
DATEPART(DAY,er.AddAt) as monthday,
er.employeeId,firstName as Name
from records er,employee
where er.employeeId=employee.employeeId

Update:

After Testing the solution of @Gordon Linoff , that's what I got:

and I have two problems :

I have duplicate data ( 3 times duplicate )
When I select a other month than February , I got no data.

 select Day, WeekdayName, e.employeeid,
       (case when r.employeeid is not null then 'present' else 'absent' end)
from Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)) c  cross join
     employee e left join
     records r
     on c.Day = DATEPART(DAY,r.AddAt) and e.employeeid = r.employeeid
     where Month=1

This is set logic - you have two sets, the sets of all days, and the set of days where the employee was present. The second set is present, the first set minus the second set is absent - you can get that minus set with a LEFT OUTER join. — Chris B. Behrens, Mar 04 '21 at 16:56
The problem is I don't know how can I get all days? and the problem is not how can I get the presence days but how can I create a new column and add the status of every employee?? — , Mar 04 '21 at 16:57
see: [Get all dates between two dates in SQL Server](https://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server) — Luuk, Mar 04 '21 at 16:59
@Luuk , not exactly , please suppose that I have a month as parameter , how can I get the a result like what I have wrote on the question? ( employeeId, date, status ) — , Mar 04 '21 at 17:01
SELECT 'Present' as PresentStatus UNION SELECT 'Absent' As PresentStatus. Combine that with the logic to create the sets, and you're done. Break the question into pieces, focus on the pieces, and then put all the pieces together. — Chris B. Behrens, Mar 04 '21 at 17:33
[THIS](https://stackoverflow.com/a/65267184/724039) comment is an example of how to do 1 month, without a (fixed) calendar table. You only have to change the second parameter with the code found [here](https://stackoverflow.com/questions/1051488/get-the-last-day-of-the-month-in-sql) to calculate the end of the month. — Luuk, Mar 04 '21 at 18:19

Gordon Linoff · Answer 1 · 2021-03-04T18:06:28.533

1

Let me assume that you have a calendar table and a table of all employees. The idea is then to do a cross join to generate the rows and a left join to bring in the existing data:

select c.dayid, c.dayname, e.employeeid,
       (case when r.employeeid is not null then 'present' else 'absent' end)
from calendar c cross join
     employees e left join
     records r
     on c.dayid = r.dayid and e.employeeid = r.employeeid;

I assume you have a calendar table because you have a column called dayid. If you don't have one, you can construct one using a numbers table, a recursive CTE, a subquery on records or some other method.

You can probably just use existing dates for the calendar:

from (select distinct dayid from records) d

edited Mar 04 '21 at 18:06

answered Mar 04 '21 at 17:01

Gordon Linoff

1,242,037
58
646
786

I don't have a calendar table!! – Mar 04 '21 at 17:01
Is it necessary to have a calendar table??? I have only this function that return a temporary table ( Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)) – Mar 04 '21 at 17:02
Removing duplicate data is a solvable problem in T-SQL - Google how to do that piece. – Chris B. Behrens Mar 04 '21 at 17:35
@csharp_devloper31 Here's a good example of how to generate a calendar table on the fly https://stackoverflow.com/a/66303412/14868997 – Charlieface Mar 04 '21 at 19:26
@ChrisB.Behrens, the matter is not related only to the duplicate data , please see the updated question – Mar 04 '21 at 19:40
1

Can you answer the question of WHY you're getting each of your problems? Why you're getting duplicate data, and why nothing but February works? The seeds to the solution are in those answers. – Chris B. Behrens Mar 04 '21 at 19:52
I'm using `GET_Calendar` function, I assume that the problem from this function, but I'm wrong because I've executed it and I get only 28 days , that mean there is no duplicate , the duplication appears only using cross join and left join – Mar 05 '21 at 07:54

How can I get the absence days of all employee? SQL Server

1 Answers1