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