user total present count
user total absent count
i wan excat data as shown in this picturce
i wan same data in this code (attced)
USE [BiometricDB]
GO
/****** Object: StoredProcedure [dbo].[sp_GetAttendanceReport] Script Date: 02/07/2023 05:22:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetAttendanceReport]
@month INT, @year INT
AS
BEGIN
-- Attendance Report
SELECT
[present].[Month Name], [present].[Year], [present].[Total Present], [absent].[Total Absent]
FROM
(
SELECT
SUM(duration) AS [Total Present], MONTH(dutydatetime) AS [Month], YEAR(dutydatetime) AS [Year], DATENAME(month,dutydatetime) AS [Month Name]
FROM
tblattENDancelog
WHERE
[type] = 'Present' AND YEAR(dutydatetime) = @year AND MONTH(dutydatetime) = @month
GROUP by
DATENAME(month,dutydatetime), MONTH(dutydatetime), YEAR(dutydatetime)
) [present] INNER JOIN
(
SELECT
SUM(duration) AS [Total Absent], MONTH(dutydatetime) AS [Month], YEAR(dutydatetime) AS [Year]
FROM
tblattENDancelog
WHERE
[type] = 'Absent' AND YEAR(dutydatetime) = @year AND MONTH(dutydatetime) = @month
GROUP by
DATENAME(month,dutydatetime), MONTH(dutydatetime), YEAR(dutydatetime)
) [absent] ON
[present].Month = [absent].Month and [present].Year = [absent].Year
ORDER by
[present].Month
END