Last post Dec 19, 2017 09:07 AM by Deepak Panchal
Dec 07, 2017 02:22 PM|JagjitSingh|LINK
I have parameter Date. I want to generate query that should calculate Sales Data MTD & YTD.
Dec 08, 2017 01:20 AM|Deepak Panchal|LINK
you had asked," I want to generate query that should calculate Sales Data MTD & YTD."
you can try to refer example below.
declare @Sales table (TDate date, ProductId char(3), SalesAmount int)
select '2011-01-20', 'P01', 100 union all
select '2011-02-01', 'P02', 200 union all
select '2011-03-12', 'P01', 50 union all
select '2011-03-20', 'P02', 75 union all
select '2011-01-01', 'P02', 120 union all
select '2011-01-18', 'P01', 60 union all
select '2011-02-11', 'P01', 90;
with Sales2011 as (
select M = month(TDate), MonthName = convert(char(3), TDate, 0), * from @Sales where year(TDate) = 2011
), PeriodWise as
select M, MonthName, ProductId, MonthTotal = sum(SalesAmount) from Sales2011
group by M, MonthName, ProductId
select MonthName, ProductId, MonthTotal, YTD
from PeriodWise a cross apply (
select YTD = sum(b.MonthTotal) from PeriodWise b where a.M >= b.M and a.ProductId = b.ProductId
) x order by ProductId, M
Calculate YTD and MTD by Period
Dec 17, 2017 04:27 PM|JagjitSingh|LINK
What should be the Parameters . I have Month & Year as parameter. How then i should get MTD,YTD Data
Dec 19, 2017 09:07 AM|Deepak Panchal|LINK
please try to post the code.
so that we can see what actually you had tried and what can cause the issue.
you can try to post your data and a query.
we will try to make a test with it.
with only one line of description, it is difficult for us to reproduce and find the issue in the code.
so please try to provide a detailed information.
we will again try to provide further suggestions to solve the issue.