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: