How to check if the employee is absent for 3 continuous days?

In some cases, you may want to fetch rows where an employee has been absent for a specific number of consecutive days. This can be achieved using the custom query mode in Bold BI using LAG function.

Using the LAG function:

The LAG function is used to access previous row values in the current row. You can find more information on how to use the LAG function in Bold BI in this article:

How to use LAG function in query to create dashboard.

Sample Query for Fetching Consecutive Absences:

Here's a sample query that will help you fetch all the information about employees who have been absent for three consecutive days:

SELECT *, CASE WHEN [Absent] = 1 AND [PreviousDay] = 1 AND [DayBeforePreviousDay] =1 THEN 'Absent' ELSE 'Not Absent' END AS 'Absent for 3 days'

FROM (SELECT TOP 1000 
           [Employee].[Emp_Id] AS [Emp_Id],
           [Employee].[Date] AS [Date],
           [Employee].[Absent] AS [Absent],
LAG([Absent], 1) OVER (PARTITION BY [Emp_Id] ORDER BY [Date] ) AS [PreviousDay],
LAG ([System_Generated_Absent], 2) OVER (PARTITION BY [Emp_Id] ORDER BY [Date]) AS [DayBeforePreviousDay]
          FROM [dbo].[Employee] AS [Employee])t

Related Links: