How to Create Daily, Weekly, and Monthly Report in SQL Server?

SQL Server is a versatile database and a most used database throughout the world. In this article, let us see SQL queries how to get Daily, Weekly, and Monthly reports from SQL Server.

Let us start in creating a database and sample details

Step 1: Database creation 

Command to create the database.  Here GEEKSFORGEEKS is the db name.

Query:

CREATE DATABASE GEEKSFORGEEKS;

Step 2: Make the database active

Query:

USE GEEKSFORGEEKS;

Output:

Step 3: Table Creation 

Query:

CREATE TABLE [dbo].[ArticleDetails](
    [ArticleID] [int] IDENTITY(1,1) NOT NULL,
    [AuthorID] [int],
    [ArticleDate] [date] NULL,
    [NARRATION] [varchar](25) NULL
) ON [PRIMARY]
GO

Step 4: Inserting data into table

Query:

INSERT INTO ArticleDetails (AuthorID,ArticleDate,
NARRATION) VALUES (1,'2021-12-10','MONGODB')
INSERT INTO ArticleDetails (AuthorID,ArticleDate,
NARRATION) VALUES (1,'2021-12-10','JAVA')
INSERT INTO ArticleDetails (AuthorID,ArticleDate,
NARRATION) VALUES (1,'2021-12-05','PYTHON')
INSERT INTO ArticleDetails (AuthorID,ArticleDate,
NARRATION) VALUES (1,'2021-11-10','WEB DEVELOPMENT')
INSERT INTO ArticleDetails (AuthorID,ArticleDate,
NARRATION) VALUES (1,'2021-11-08','DATABASE')
INSERT INTO ArticleDetails (AuthorID,ArticleDate,
NARRATION) VALUES (2,'2021-12-10','MONGODB')
INSERT INTO ArticleDetails (AuthorID,ArticleDate,
NARRATION) VALUES (2,'2021-12-01','PYTHON')

Step 5: Let us query the sample data first.

Query:

SELECT  * FROM ArticleDetails;

Output: 

Step 6: Get the data of the article published/created on a daily basis way.

Query:

SELECT GETDATE() AS CURRENTDATE
SELECT CONVERT(nvarchar(10),
GETDATE(),101) AS FORMATTEDCURRENTDATE

Query:

SELECT AuthorID,ArticleDate,NARRATION AS
 'Daily' FROM ArticleDetails
WHERE ArticleDate = CONVERT(nvarchar(10),
GETDATE(),101);

Query:

-- To get the count of articles created/published
 by the author on daily basis
SELECT AuthorID, COUNT(AuthorID) AS 
'Daily Writeups' FROM ArticleDetails
WHERE ArticleDate = CONVERT(nvarchar(10),
GETDATE(),101) GROUP BY AuthorID

Here we are checking the ‘articleDate’ column  matching with the currentDate value. CurrentDate can be obtained by means of ‘GETDATE()’ function. Using Convert, we are checking against the ‘articleDate’ value.

Step 7: Get the data of the article published/created on a WEEKLY basis way :

Query:

-- To get weekly
-- As we need to get the data between 7 days 
from current date, we are using BETWEEN clause

-- start date should be 7 days earlier 
from currentdate and hence it is provided in the below way

SELECT AuthorID,ArticleDate,NARRATION AS
 'Weekly' FROM ArticleDetails
WHERE ArticleDate BETWEEN DATEADD(d,-7,
CONVERT(nvarchar(10),GETDATE(),101)) 
AND CONVERT(nvarchar(10),GETDATE(),101)

A Weekly groupwise data can be obtained in below way.

Query:

SELECT AuthorID, COUNT(AuthorID) AS 
'Weekly Writeups' FROM ArticleDetails
WHERE ArticleDate BETWEEN DATEADD(d,-7,
CONVERT(nvarchar(10),GETDATE(),101)) 
AND CONVERT(nvarchar(10),GETDATE(),101)
GROUP BY AuthorID

Step 8: Get the data of the article published/created on a MONTHLY basis way. Here we are using BETWEEN clause. A start date should be 30 days earlier from currentdate and hence it is provided in the below way.

Query:

SELECT AuthorID,ArticleDate,NARRATION AS 
'Monthly Writeups' FROM ArticleDetails
WHERE ArticleDate BETWEEN DATEADD(d,-30,
CONVERT(nvarchar(10),GETDATE(),101)) 
AND CONVERT(nvarchar(10),GETDATE(),101)

Monthly group wise data :

Query:

SELECT AuthorID, COUNT(AuthorID) AS 
'Monthly Writeups' FROM ArticleDetails
WHERE ArticleDate BETWEEN DATEADD(d,-30,
CONVERT(nvarchar(10),GETDATE(),101)) 
AND CONVERT(nvarchar(10),GETDATE(),101)
GROUP BY AuthorID

In the above ways, we can able to get daily/weekly/monthly data.