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.
Share your thoughts in the comments
Please Login to comment...