Month-to-Date (MTD) Sales vs. Sales Goal with SQL

This is a quick article about comparing month-to-date (MTD) sales from a sales database to a sales goal for use on it's own or with data visualization tools. This query assigns a goal of $1,000,000 as the sales goal for the month, adds up all sales for the month so far, and then compares it to where your sales should be at right now by showing 'Today's Target' so that that one can see at a glance if sales are on schedule. The results look like this:

Sales Goal  |  Today's Target  |  Gross Sales
​------------------------------------------------------
   1000000  |          515000          |  503000        

The Variables

First we'll need to declare some variables. We don't necessary have to, but it makes the query more readable. More easily understandable by readers or by ourselves if we need to come back to it later. The variables seems self explanatory for me, but if you're reading this, chances are that you're still learning. We started there. Anyways here are the variables:

• @SalesGoal is declared as an integer (INT) type variable and assigned the value 1,000,000
• @Today is a DATE type variable and is assigned today's date. Technically the date shown on the system / server is the date that will populate here, so make sure your system date and time is accurate to get precise results. 
• @MonthStart is a DATE type variable that uses native SQL functions to determine the start of the month based on the @Today variable
• @DayOfMonth in another integer (INT) type variable that determines how many days into the month @Today is. This could also be accomplished by converting the DAY element of @Today into an integer.
• @TotalDaysInMonth is another integer (INT) since we're doing some math to determine what percentage into the month we are based on the day. 
• Then we declare the @PercentofMonth variable as a FLOAT since it will likely have a decimal place. After declaring this variable we set it's value by dividing @DayOfMonth by @TotalDaysInMonth, giving us a percentage of how far into the month we are.
• @TodaysTarget is declared by multiplying the @PercentOfMonth determined above with our @SalesGoal to determine where sales should be at today to be considered on track to meet the @SalesGoal for the month.

Here are the variables as they appear in the query:

DECLARE @SalesGoal INT = '1000000'
DECLARE @Today DATE = GETDATE()
DECLARE @MonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0)
DECLARE @DayOfMonth INT = datediff(day, @Today, dateadd(month, 1, @Today)) - (DATEDIFF(DAY,GETDATE(),DATEADD(MONTH,1,@MonthStart)) - 1);
DECLARE @TotalDaysInMonth INT = datediff(day, @Today, dateadd(month, 1, @Today));
DECLARE @PercentofMonth FLOAT = @DayOfMonth
SET @PercentofMonth /= @TotalDaysInMonth;
DECLARE @TodaysTarget FLOAT = @PercentofMonth * @SalesGoal;

The Query

Next we put all the variables together to be used in the query. Keep in mind every database is unique, so elements such as the column names or database name will have to be changed on the schema in your environment. 

​The select statement here selects the @SalesGoal variable we set, @TodaysTarget, and adds up all of the integers in the Gross Sales column as long as the DATE of the sale was after the start of the month and today. This assumes that your database has a date column and a Gross Sales column. 

SELECT @SalesGoal AS "Sales Goal", @TodaysTarget AS "Todays Target", SUM([Gross Sales]) as "Gross Sales"
FROM dbo.SalesDB
WHERE DATE between @MonthStart AND @Today;

Again, here is the final result of the query:

Sales Goal  |  Today's Target  |  Gross Sales
​------------------------------------------------------
   1000000  |          515000          |  503000        ​

And here is an example chart created in Excel to add some visualization to the data: