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.

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 ...

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 @TodayEndDATETIME = DATEADD(MS, -3, DATEADD(dd, DATEDIFF(dd, -1, @Today),0))
-- Set Yesterday
DECLARE @YesterdayStart DATETIME = DATEADD(dd, DATEDIFF(dd, 1, @Today),0)
DECLARE @YesterdayEndDATETIME = DATEADD(MS, -3, DATEADD(dd, DATEDIFF(dd, 0, @Today),0))
-- Set This Month
DECLARE @ThisMonthStart DATETIME = DATEADD(M, DATEDIFF(M, 0, @Today),0)
DECLARE @ThisMonthEndDATETIME = DATEADD(MS, -3, DATEADD(M, DATEDIFF(M, -1, @Today),0))
-- Set Previous Month
DECLARE @PrevMonthStartDATETIME = DATEADD(M, DATEDIFF(M, 1, @Today)-1,0)
DECLARE @PrevMonthEndDATETIME = DATEADD(MS, -3, DATEADD(M, DATEDIFF(M, 0, @Today),0))
-- Set This Year
DECLARE @ThisYearStartDATETIME = DATEADD(YY, DATEDIFF(YY, 0, @Today),0)
DECLARE @ThisYearEndDATETIME = DATEADD(MS, -3, DATEADD(YY, DATEDIFF(YY, -1, @Today),0))
-- Set Previous Year
DECLARE @PrevYearStartDATETIME = DATEADD(YY, DATEDIFF(YY, 1, @Today)-1,0)
DECLARE @PrevYearEndDATETIME = DATEADD(MS, -3, DATEADD(YY, DATEDIFF(YY, 0, @Today),0))
-- Set This YTD
DECLARE @ThisYTDStartDATETIME = DATEADD(YY, DATEDIFF(YY, 0, @Today),0)
DECLARE @ThisYTDEndDATETIME = DATEADD(MS, -3, DATEADD(dd, DATEDIFF(dd, -1, @Today),0))
-- Set Previous YTD
DECLARE @PrevYTDStartDATETIME = DATEADD(YY, DATEDIFF(YY, 1, @Today)-1,0)
DECLARE @PrevYTDEndDATETIME = 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

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 DATENOT 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.

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.

CREATE PROCEDURE [dbo].[usp_GetSalesByPeriod] 
-- Add the parameters for the stored procedure here
@PeriodName VARCHAR(50)-- Today, Previous Week....
--,@PeriodStartDateTimeDATETIME-- OLD QUERY
--,@PeriodEndDateTimeDATETIME-- OLD QUERY
AS
BEGIN
SET NOCOUNT ON;
-- Declaring the local variables here
DECLARE @PeriodStartDateTimeDATETIME
DECLARE @PeriodEndDateTimeDATETIME
    -- 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.