Sheridan Wendt
  • Home
  • Technology
  • Business
  • Music
  • Adventures
  • Resume
  • Contact

Technology

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

2/27/2018

Comments

 
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        
Picture

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:
Picture
Comments

    Repositories

    PowerShell
    SQL

    Author

    Sheridan's interests are in technology, business, music, and adventures

    View my profile on LinkedIn

    RSS Feed

    Categories

    All
    Alerts
    Azure
    Business Intelligence
    Data Visualization
    Notifications
    Photo Frame
    PowerShell
    Raspberry Pi
    Scripting
    SMS
    SQL
    Technology
    Virtualization
    VMWare

    Business

    Archives

    June 2019
    May 2019
    September 2018
    May 2018
    April 2018
    March 2018
    February 2018
    December 2017
    September 2017
    July 2003

Powered by Create your own unique website with customizable templates.
  • Home
  • Technology
  • Business
  • Music
  • Adventures
  • Resume
  • Contact