Generate Month to Date Report using SQL
In this blog we will learn on how to put together a SQL statement that returns the MTD value.
Month-to-date (MTD) is a period, starting from the beginning of the current month and continuing up to the present day.
Month-to-date is used in many contexts, mainly for recording results of an activity in the time between a date (exclusive, since this day may not yet be “complete”) and the beginning of the month.
MTD describes the return so far this month. For example: the month to date return for the stock is 8%. This means from the beginning of the current month until the current date, stock has appreciated by 8%.
So the query would go something like the following:
select ds.Sales_Region,ds.Sales_Territory_Branch,
sum(case when year(full_date)=year(getdate()) and month(full_date)=month(getdate())
then InvoiceAmt else 0 end) as MTD
from DailySales ds
GROUP BY ds.Sales_Region,ds.Sales_Territory_Branch
So basically we’ve put in a case statement here where we’ve made use of Sum aggregator function and the condition states that when the year is current year which is fetched by the GetDate() function and the month is = the current month i.e the current running month then it will show up the summed up value of the InvoiceAmt grouped by the suggested columns.
Sayed Shakeeb Jamal
Free Open Source BI Platform
- AI-Powered Analytics
- Embedded Analytics
- Single Sign-On (SSO)
- Multi-Tenancy
- Paginated Canned Reporting
- Interactive Dashboard
- White Labeling