Sql-server – How to count absence days from differents shift

countsql server

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) between hle.startDate and hle.endDate and c.isActive=1 and d.groupId =e.groupId and c.employeeId=e.employeeId and c.isActive=1 )) sc group by Month,Day,groupId) s group by groupId

Get_Calendar function:

ALTER FUNCTION [dbo].[Get_Calendar_Date]
(
    @StartDate DATETIME
,   @EndDate DATETIME
)
RETURNS TABLE
AS
 
RETURN
(
    SELECT  Tbl_Obj.RNo
        ,   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) AS [Date]
        ,   DATEPART(quarter,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Quarter]
        ,   DATEPART(dayofyear,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [DayofYear]
        ,   DATEPART(WEEK,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekofYear]
        ,   DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Year]
        ,   DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Month]
        ,   DATEPART(DAY,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Day]
        ,   DATEPART(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Weekday]
        ,   DATENAME(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [MonthName]
        ,   DATENAME(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekdayName]
        ,   (RIGHT( REPLICATE('0',(4)) +
                CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(4)
             )+
             RIGHT( REPLICATE('0',(2)) +
                CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0)
                ,(2)
             )
            ) AS [Vintage]
 
    FROM    ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RNo]
              FROM sys.all_objects WITH (NOLOCK)
            ) Tbl_Obj
          
    WHERE   DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) <= @EndDate
)

Best Answer

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.

Related Question