SQL Query to Make Month Wise Report
In this article, we will learn SQL Query how to extract data on monthly basis from a table or generate a report Month wise and we will implement it with the help of an example for better understanding. We will follow step by step process.
Here we will discuss how to extract data on monthly basis from a table or generate a report Month wise with the help of an SQL query using the DATENAME( ) function.
DATENAME( ):
This function is a defined function of SQL. It is used to extract a specific part of data. This function extracts the results as a string type value.
Syntax:
DATENAME( required part of date,date)
Here required parameters of the date will be:
- year,yyyy,yy: Year
- month,mm,m : Month
- day,dy,y: Day. and another part of DateTime.
For the purpose of implementing the query to find the month-wise reports. We will create a database “SAMPLE” and we will create a table “NEWJOINEE” in the database called “SAMPLE“. We will follow the below steps to implement this Query
Step 1: Create Database
The SQL server statement for creating a database called SAMPLE is as follows
Query:
Create database SAMPLE;
Step 2: Use Database
SQL statement to switch the database context SAMPLE as follows:
Query:
Use SAMPLE;
Step 3: Creation table in Database
For creation a table in a database. We need to execute a query in Microsoft SQL Server. Like Mysql, Oracle, etc. We will use this query:
Syntax:
create table table_name( column1 type(size), column2 type(size), . . . columnN type(size) );
Query:
DECLARE @date DATE; DECLARE @start_date DATE; DECLARE @end_date DATE; DECLARE @loop_date DATE; -- declaring a table variable DECLARE @dates TABLE (date DATE); -- setting the first and the last date in the month given by date SET @date = '2021/08/1'; SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), '01'); SET @end_date = EOMONTH(@date); -- check dates SELECT @date AS cur_date, @start_date AS first_date, @end_date AS last_date; -- populating a table (variable) with all dates in a given month SET @loop_date = @start_date; WHILE @loop_date <= @end_date BEGIN INSERT INTO @dates(date) VALUES (@loop_date); SET @loop_date = DATEADD(DAY, 1, @loop_date); END; CREATE TABLE NEWJOINEE ( EMPNAME VARCHAR(25) NOT NULL, EMPCONTACT BIGINT, DATEOFJOIN DATE NOT NULL, DESIGNATION VARCHAR(20) );
Step 4: Insert Data into Table
To insert data into a table there is the query we will use here in SQL Server.
Syntax:
insert into table_name( value1, value2, value3,...valueN);
Query:
INSERT INTO NEWJOINEE VALUES ('VISHAL',9193458625,'12-JAN-2021','SYSTEM ENGINEER'), ('RAM',7856958725,'22-JAN-2021','MANAGER'), ('VIPIN',91458458625,'25-JAN-2021','SYSTEM ENGINEER'), ('VINOD',9759554664,'28-JAN-2021','MANAGER'), ('AKASH',7500554664,'18-JAN-2021','EXECUTIVE'), ('RAJ',7856958625,'02-FEB-2021','MANAGER'), ('AJAY',9756644159,'20-FEB-2021','SYSTEM ENGINEER'), ('SHYAM',919347625,'12-FEB-2021','EXECUTIVE'), ('VIPIN',91458458625,'25-FEB-2021','SYSTEM ENGINEER'), ('VIJAY',7858458625,'25-FEB-2021','EXECUTIVE'), ('VIKASH',9759554664,'28-FEB-2021','SYSTEM ENGINEER'), ('VIVEK',9193458625,'12-MAR-2021','MANAGER'), ('ANUJ',91458458625,'25-MAR-2021','EXECUTIVE'), ('AKASH',7500554664,'18-MAR-2021','MANAGER'), ('RAKESH',7845758725,'22-MAR-2021','EXECUTIVE');
Output:
Step 5: Month-wise report query
Query:
SELECT MAX(DATENAME(MM,DATEOFJOIN)) AS JOININGMONTH, COUNT(1) AS "TOTALEMP. JOIN" FROM NEWJOINEE GROUP BY MONTH(DATEOFJOIN);
Output:
Contact Us