locked
MTD,YTD Query RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I have parameter Date. I want to generate query that should calculate Sales Data MTD & YTD.

    Thanks

    Thursday, December 7, 2017 2:22 PM

All replies

  • User347430248 posted

    Hi JagjitSingh,

    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)
    insert @Sales 
    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
    

    Output:

    Reference:

    Calculate YTD and MTD by Period

    Regards

    Deepak

    Friday, December 8, 2017 1:20 AM
  • User-1499457942 posted

    Hi

       What should be the Parameters . I have Month & Year as parameter. How then i should get MTD,YTD Data

    Thanks

    Sunday, December 17, 2017 4:27 PM
  • User347430248 posted

    Hi JagjitSingh,

    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.

    Regards

    Deepak

    <sub></sub><sup></sup>

    Tuesday, December 19, 2017 9:07 AM