How to count absence days from differents shift?

I have a problem when in the counting of the employee absences depending on different shifts, so all employees do not have the same shift, and what I should get is the group and the absences of this group ( the group contains some employees ),

Groups table

GroupId    GroupName
  1         Science
  2         Physics

Employee table

EmployeeId      GroupId
   001             1
   002             1
   003             2
   004             2
   004             2

Working_days table

DayId    DayName
  1       Monday
  2       Tuesday
  3        ...

Timetable table

TimeTableId   grouptimeId  WorkingDayId
    55eds          22           1
    e8d5s          33           2

GroupTime table

GroupTimeId    TimeTableId 
     1           55eds
     2           e8d5s

How can I get absences for every group except the holidays?

```
select groupId,(case when groupId = null then 0 else COUNT(*) end) as absence from (select groupId,
COUNT(*) as absences1 from
(select distinct MONTh,DAY,e.groupId
from employee_c c,holiday hl,employee e,groups,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, -1))
where DATENAME(DD, Weekday) IN (select WorkingdayId+1 from timetable s,groups ds,grouptime de where dd.groupId=ds.groupId and dd.groupId = de.groupId and s.timetableId=de.timetableId and de.groupId=ds.groupdId) and DATEPART(MM,hl.startDate)=@Month and
c.isActive=1
except

(select Month,Day,d.departmentId from department d,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST(DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS datetime))+@Month-1, -1)),holiday hle,employee_c c,employee e where datepart(MM,hle.startDate)=@Month and cast(Date as date) betw

It seems like you may be going about things in a much more complicated way then they need to be. Generally for figuring out things like Holidays, Weekends, and other Date-centric Facts, a DateDimensions table makes things a lot simpler. There's a lot of articles out there on how to generate one, this article being a good example. Once you have a DateDimensions table, you can join to it in a similar way to this:

SELECT E.GroupId, COUNT(*) AS Abscences
FROM employee AS E
INNER JOIN @tablevarDepDays AS D -- Assuming this is where you store absences?
    ON E.employeeId = D.employeeId
INNER JOIN DateDimensions AS DD
    ON D.SomeDateField = DD.[Date] -- D.SomeDateField would be your absence date
WHERE DD.IsHoliday = 0 -- Filters out Holidays
GROUP BY E.GroupId

Note it would be more helpful if you could please include your table schema for all your tables involved. If so, I can update my answer to be less pseudo-code like.

StackExchange Database Administrators Q#290460, answer score: 3