How to write SQL query to Calculate monthly report

by

How to write SQL query to Calculate monthly report, Here is the steps which you need to follow to get the result.

Step1) Create table in your database.

USE [EmployeeDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
	[Emp_Code] [int] NULL,
	[Date] [datetime] NULL,
	[Attendance] [bit] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1001, CAST(N'2023-01-02T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1001, CAST(N'2023-01-03T00:00:00.000' AS DateTime), 0)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1001, CAST(N'2023-02-01T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1001, CAST(N'2023-01-01T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1002, CAST(N'2023-01-01T00:00:00.000' AS DateTime), 0)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1002, CAST(N'2023-02-01T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1002, CAST(N'2023-03-01T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1003, CAST(N'2023-01-01T00:00:00.000' AS DateTime), 0)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1003, CAST(N'2023-01-02T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1003, CAST(N'2023-01-03T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1003, CAST(N'2023-01-04T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1003, CAST(N'2023-01-05T00:00:00.000' AS DateTime), 1)
GO
INSERT [dbo].[Employee] ([Emp_Code], [Date], [Attendance]) VALUES (1003, CAST(N'2023-01-06T00:00:00.000' AS DateTime), 0)
GO

Step 2) Attendance is bit column (0,1) 0 – Means employee is absent, 1 Means employee is present on that day.

I am interested to create the report, how many days employee is present and absent using SQL Query. 

SELECT Emp_Code,MAX(DATENAME(MM,Date)) AS MonthWisePresentOrAbsent,
       count(*)-count(nullif(Attendance, 0)) AS AbsentDays , count(nullif(Attendance, 0)) as PresentDays
FROM Employee where Emp_Code=1003 GROUP BY MONTH(Date),Emp_Code

Step 3) Here is the output FOR Current Year only


1003	January	        2	4

Step 4) If you are planning to display Past year, Current Year and Next year then use below line of code. 

SELECT Emp_Code,
       MAX(DATENAME(MM,Date)) AS MonthWisePresentOrAbsent,
       count(*)-count(nullif(Attendance, 0)) AS AbsentDays , 
	   count(nullif(Attendance, 0)) as PresentDays
FROM Soft_Employee 
WHERE year(Date) = year(DATEADD(year,0,getdate()))  
GROUP BY MONTH(Date),Emp_Code

Last Year :- DATEADD(year,-1,getdate())

Present Year :- DATEADD(year,0,getdate())

Next Year: – DATEADD(year,1,getdate()) //Hypothetically

Leave a Comment