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.
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Sayed Shakeeb Jamal
database MySQL