Implementing Date Calculations in SQL

,

Problem

A few weeks back, I was working on a report for one of my clients who were using a desktop application as a client tool with SQL as the database. The module on which I was working had a reporting functionality that displayed the Sales Amount by categories that had taken place during a specific date as selected by the user.

If you see the screen print below (Fig 1), you can see that there are two dropdowns in the application - Period Start Date and Period End Date. The user has to fill in these two fields in order to render any report as these were mandatory. These date values were passed on to the SQL as parameters to a Stored Procedure, which in turn returned the result set based on the values passed.

Fig 1 - Original Application Screen

 

However, after few years of using the app, the management decided to remove the date filters from the application and use some Descriptive Labels in order to generate the reports instead of providing manual date values all the time. These descriptive labels could be something like

  • This Year - Generate the report from the current year's sales data.
  • Previous Quarter - Generate the report from the previous quarter's sales data.
  • and so on ...
Fig 2 - Updated Application Screen

In the figure above (Fig 2), you can see that the Date fields have been removed and now the report needs to include a descriptive date label filter in it.

 

Solution

After a few hours of brainstorming with my colleagues, we decided this approach which had the minimum impact on the system as well as does the job very easily and with lesser efforts.

The very first thing that I did is to create a table that will store these Descriptive Labels along with the Actual Start and End Dates for that period. I have provided the script to create this table below.

CREATE TABLE [dbo].[TimePeriods](
	[TimePeriodKey] [int] IDENTITY(1,1) NOT NULL,
	[PeriodName] [varchar](50) NOT NULL,
	[PeriodStartDateTime] [datetime] NOT NULL,
	[PeriodEndDateTime] [datetime] NOT NULL
)
GO

Once the table was created, I quickly created a Stored Procedure that will populate values into this table so that our base for the reports will be ready. The script for the stored procedure is provided below.

CREATE PROCEDURE [dbo].[usp_PopulateTimePeriods]
	-- Add the parameters for the stored procedure here
	@Today DATETIME = '1900-01-01 00:00:00.000'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	TRUNCATE TABLE [dbo].[TimePeriods]
	-- Set Today's value as GETDATE if set empty
	SET @Today = CASE WHEN @Today = '1900-01-01 00:00:00.000' THEN GETDATE() ELSE @Today END;
	DECLARE @TodayStart DATETIME = DATEADD(dd, DATEDIFF(dd, 0, @Today),0)
	DECLARE @TodayEnd	DATETIME = DATEADD(MS, -3, DATEADD(dd, DATEDIFF(dd, -1, @Today),0))
	-- Set Yesterday
	DECLARE @YesterdayStart DATETIME = DATEADD(dd, DATEDIFF(dd, 1, @Today),0)
	DECLARE @YesterdayEnd	DATETIME = DATEADD(MS, -3, DATEADD(dd, DATEDIFF(dd, 0, @Today),0))
	-- Set This Month
	DECLARE @ThisMonthStart DATETIME = DATEADD(M, DATEDIFF(M, 0, @Today),0)
	DECLARE @ThisMonthEnd	DATETIME = DATEADD(MS, -3, DATEADD(M, DATEDIFF(M, -1, @Today),0))
	-- Set Previous Month
	DECLARE @PrevMonthStart	DATETIME = DATEADD(M, DATEDIFF(M, 1, @Today)-1,0)
	DECLARE @PrevMonthEnd	DATETIME = DATEADD(MS, -3, DATEADD(M, DATEDIFF(M, 0, @Today),0))
	-- Set This Year
	DECLARE @ThisYearStart	DATETIME = DATEADD(YY, DATEDIFF(YY, 0, @Today),0)
	DECLARE @ThisYearEnd	DATETIME = DATEADD(MS, -3, DATEADD(YY, DATEDIFF(YY, -1, @Today),0))
	-- Set Previous Year
	DECLARE @PrevYearStart	DATETIME = DATEADD(YY, DATEDIFF(YY, 1, @Today)-1,0)
	DECLARE @PrevYearEnd	DATETIME = DATEADD(MS, -3, DATEADD(YY, DATEDIFF(YY, 0, @Today),0))
	-- Set This YTD
	DECLARE @ThisYTDStart	DATETIME = DATEADD(YY, DATEDIFF(YY, 0, @Today),0)
	DECLARE @ThisYTDEnd		DATETIME = DATEADD(MS, -3, DATEADD(dd, DATEDIFF(dd, -1, @Today),0))
	-- Set Previous YTD
	DECLARE @PrevYTDStart	DATETIME = DATEADD(YY, DATEDIFF(YY, 1, @Today)-1,0)
	DECLARE @PrevYTDEnd		DATETIME = DATEADD(YY,-1,DATEADD(MS, -3, DATEADD(dd, DATEDIFF(dd, -1, @Today),0)))
	---- Insert values
	INSERT INTO [dbo].[TimePeriods] (PeriodName, PeriodStartDateTime, PeriodEndDateTime) VALUES 
	('Today'			,@TodayStart		,@TodayEnd),
	('Yesterday'		,@YesterdayStart	,@YesterdayEnd),
	('This Month'		,@ThisMonthStart	,@ThisMonthEnd),
	('Previous Month'	,@PrevMonthStart	,@PrevMonthEnd),
	('This Year'		,@ThisYearStart		,@ThisYearEnd),
	('Previous Year'	,@PrevYearStart		,@PrevYearEnd),
	('This YTD'			,@ThisYTDStart		,@ThisYTDEnd),
	('Previous YTD'		,@PrevYTDStart		,@PrevYTDEnd)
	
END

Once this Stored Procedure is created, let's execute it and see how the data is populated in the TimePeriods table.

EXEC [usp_PopulateTimePeriods]
GO

SELECT * FROM TimePeriods
GO
Fig 3 - TimePeriods Table

As you can see in the screen print above (Fig 3), we now have all the descriptive period labels in the field - "PeriodName" as well as the Start and End Dates for each period. Now, when the user selects any specific label on the application we have the corresponding Date values for that period which can be fed into the Stored Procedure that we were using previously.

 

Prepare the Sales Table with Dummy Data

Now that our Period Labels are ready, let's take it into action and get some data into our Sales table. For the sake of this article, I'll be creating a Sales table and dump some dummy values into it. Please note, as I'm drafting this article today on 01-Feb-2020, most of the calculations will take reference from this date only.

Let's create the Sales table now.

IF OBJECT_ID('dbo.Sales') IS NOT NULL
	DROP TABLE dbo.Sales
GO
CREATE TABLE Sales(
	SalesKey INT NOT NULL IDENTITY(1,1)
	,SalesDate DATE	NOT NULL
	,SalesAmount INT NOT NULL
)
GO
INSERT INTO Sales (SalesDate, SalesAmount) VALUES 
('2019-01-10',1500),
('2019-08-31',900),	
('2019-10-30',1000),
('2019-11-15',1200),
('2019-12-14',1000),
('2019-12-25',600),	
('2020-01-03',500),	
('2020-01-08',400),	
('2020-01-09',300),	
('2020-01-13',200),	
('2020-01-14',100),	
('2020-01-28',400),	
('2020-02-01',200)
GO

Once the table is created, let's check the data in the Sales table.

Fig 4 - Sales Table

As you can see in the table  above (Fig 4), we have some values in it starting from Jan-2019 through Feb-2020.

 

Modifying Original Stored Procedure

Well, now its turn for us to modify the existing stored procedure and include the values obtained from the PeriodName label in it. In the figure below, I've mentioned the code changes that have been implemented to achieve the desired results. The script for this procedure is provided below.

Fig 5 - Modifying Original Stored Procedure

 

CREATE PROCEDURE [dbo].[usp_GetSalesByPeriod] 
	-- Add the parameters for the stored procedure here
	@PeriodName VARCHAR(50)			-- Today, Previous Week....
	--,@PeriodStartDateTime	DATETIME	-- OLD QUERY
	--,@PeriodEndDateTime	DATETIME	-- OLD QUERY
AS
BEGIN
	SET NOCOUNT ON;
	-- Declaring the local variables here
	DECLARE @PeriodStartDateTime	DATETIME
	DECLARE @PeriodEndDateTime		DATETIME
    -- Get Start and End Dates for selected filter
	SELECT 
		@PeriodStartDateTime	= PeriodStartDateTime
		,@PeriodEndDateTime		= PeriodEndDateTime
	FROM [dbo].[TimePeriods]
	WHERE [PeriodName] = @PeriodName
	-- Fetch the total sales for the selected period
	SELECT ISNULL(SUM([SalesAmount]),0) SalesAmount
	FROM [dbo].[Sales]
	WHERE [SalesDate] BETWEEN @PeriodStartDateTime AND @PeriodEndDateTime

END

 

Final Results

So, now that everything is ready,  we are good to execute the stored procedure, by providing the PeriodName labels instead of the Date values. Let's execute the procedure and see the results.

 

 

If we break down the Sales Amount from the Sales table, we can verify the calculations as well. Please refer to the next screen-print for this. As it can be seen, the filters are working properly and the sum of Sales Amount is also correct.

 

Updating the TimePeriods table

Since the TimePeriods table has data for each date, it needs to be refreshed every day, so that the latest values are always in the tables. In order to do that, we can set up an SQL Agent Job and execute the stored procedure on a nightly schedule. The steps to create a job in SQL Server agent and scheduling are beyond the scope of this article and won't be covered here. You can refer to the official documentation from Microsoft about SQL Server Agent Jobs.

 

Takeaway

In this article, I have demonstrated how can we use Time Period labels instead of using direct Date-time values while generating reports from SQL Database.

The next steps would be to learn more about the various DATETIME functions in SQL and modify the TimePeriods table according to the needs.

Rate

2.9 (10)

Share

Share

Rate

2.9 (10)