Generate Year to Date Data in SQL
In this blog we will learn on how to put together a SQL statement that returns the YTD value.
Year-to-date (YTD) is a period, starting from the beginning of the current year (either the calendar year or fiscal year) and continuing up to the present day.
Year-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 year.
Its basically the SUM of a value by month but on a year to date basis. In other words, for the month of April, I am looking to get the sum of a value for the months of January, February, March and April.
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 YTD
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 all the months leading upto the current 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 SQL