Guest User

Guest User

  • Tech Writer
  • 529
  • 39.8k

This Code working on this cart ?

Feb 9 2023 5:45 PM

If Not Working Edit this code 

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
			COUNT(*) 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
			COUNT(*) 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

 


Answers (1)