How employee leave days mentioned in attendance register ?

akhter_1 0 Newbie Poster

7 Years Ago

i have made leaves managment system ,now i want to mentioned leaves days in attendance register if employee take leave from 1-1-2019 to 4-1-2019 then 5 day display in attendance register as a (L) .
kindly guide me how it will get done.
here is my attendance register query in pivot table.

SELECT DISTINCT ReportingDate INTO #Dates
FROM EmployeesAttendance
WHERE     (ReportingDate BETWEEN @StartDate AND @Enddate)
ORDER BY ReportingDate

DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112)
                + ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']')
FROM    #Dates
ORDER BY ReportingDate

-- Building the query with dynamic dates
--DECLARE @qry NVARCHAR(4000)
--SET @qry =
--'SELECT * FROM
--(SELECT EmpID, Status , ReportingDate
--FROM EmployeesAttendance)emp
--PIVOT (MAX(Status) FOR ReportingDate IN (' + @cols + ')) AS stat'

DECLARE  @qry NVARCHAR(4000) =
N'SELECT * 
FROM (SElECT EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeesAttendance.Status, 
DATEPART(DAY, EmployeesAttendance.ReportingDate)as DDate
FROM EmployeesAttendance  Inner Join EmployeeDetails on EmployeesAttendance.EmpID=EmployeeDetails.Empid )emp
PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat 
'

here is my daily attendance INTIME and OutTime CTE query

;WITH Dates (ReportingDate) 
AS (
   SELECT CONVERT(date, '2018-11-26 00:00:00.000', 120) AS ReportingDate
   UNION ALL
   SELECT DATEADD(day, 1, d.ReportingDate)
   FROM Dates d
   WHERE d.ReportingDate < CONVERT(date, '2018-12-25 00:00:00.000', 120))

,CTE As
    (
    select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
    )
    ,CTE4 As
    (
    Select MachineAttendance.EmpID,MachineAttendance.MDate,MachineAttendance.INOUT from MachineAttendance
    ) 

    ,cte1 AS
    (
    SELECT CTE4.EmpID, CAST(CTE4.MDate as Date) AS [Date], 
    CASE WHEN CTE4.INOUT = 1 THEN CTE4.MDate END AS INOUT_INTIME,
    CASE WHEN CTE4.INOUT = 2 THEN CTE4.MDate END AS INOUT_OUTTIME
    From 
     CTE4

    ), 
   cte2 
    as
    (
    select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME, 
                          MAX(INOUT_OUTTIME) AS OUTTIME
    , DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
    FROM CTE1
    GROUP BY EmpID, [Date]
    )
    select cte.EmpID,d.ReportingDate,cte2.Date,cte2.INTIME,  cte2.OUTTIME,  cte2.[Hours]
    , CASE WHEN  cte2.[Hours] >= 8 THEN 1
    WHEN  cte2.[Hours] = 0 THEN 0
    WHEN  cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
    CASE WHEN  cte2.[Hours] > CTE.Dhour then  cte2.[Hours] - CTE.Dhour else 0 End as OT,
    CASE when   
    cte.OTEntitled = 'Yes'  AND cte2.[Hours] >= CTE.Dhour 
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,  

    Convert(varchar(10), cte2.INTIME,108) as [Time],
   Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late ,
   Case when cte2.Hours >= cte.Dhour then 'P'  when cte2.Hours <= 6 then 'HD' else 'A'  end  as Status  
    from cte Cross Apply Dates d 
        Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
    order by cte.EmpID asc

1 Contributor
0 Replies
364 Views

Dani AI

Generated 9 Months Ago

Short answer: convert each leave range into one row per leave date, join that expanded set to your calendar x employee grid, and then choose the status with a simple precedence (leave wins, then attendance, then Absence). That makes the pivot trivial because every (EmpID, ReportingDate) has exactly one Status.

A compact plan:

Use a persistent calendar table or a fast tally (numbers) source to produce the full date range — avoid slow recursive CTEs for production.
Expand each leave row to daily rows (one row per EmpID + date).
LEFT JOIN Calendar x Employees to Attendance and to the expanded LeaveDays; compute Status with CASE/COALESCE so Leave maps to 'L'.
Pivot the resulting flat table (prefer full date or a day-index as pivot column — do not pivot on day-of-month alone, it collides across months).

Example of the leave-expansion + overlay (adapt names to your schema):

WITH Tally AS (
  SELECT TOP (DATEDIFF(day,@StartDate,@EndDate)+1)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
  FROM sys.all_objects
),
LeaveDays AS (
  SELECT l.EmpID, DATEADD(day, t.n, l.LeaveFrom) AS ReportingDate
  FROM Leaves l
  JOIN Tally t ON DATEADD(day, t.n, l.LeaveFrom) <= l.LeaveTo
)
SELECT e.EmpID, c.CalDate AS ReportingDate,
  CASE 
    WHEN ld.EmpID IS NOT NULL THEN 'L'
    WHEN a.Status IS NOT NULL THEN a.Status
    ELSE 'A'
  END AS Status
FROM Employees e
CROSS JOIN Calendar c
LEFT JOIN Attendance a ON a.EmpID = e.EmpID AND a.ReportingDate = c.CalDate
LEFT JOIN LeaveDays ld ON ld.EmpID = e.EmpID AND ld.ReportingDate = c.CalDate;

Notes and gotchas: make sure ReportingDate is truncated to DATE (no time part); treat LeaveTo as inclusive; decide precedence when a person both punched in and has leave (business rule). For large datasets, use a persisted calendar table, index leave ranges, or precompute LeaveDays nightly for best performance. This approach keeps the logic readable and makes the pivot step straightforward.

Thanks to for showing the pivot approach — replacing runtime range expansion with an indexed calendar/tally and an expanded LeaveDays set will make marking multi-day leaves as "L" reliable and efficient.

Be the first to reply

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.