MTD,YTD QueryRSS

• • Reply
  JagjitSingh

  Member

  504 Points

  1777 Posts

  MTD,YTD Query

  Dec 07, 2017 02:22 PM|JagjitSingh|LINK

  Hi

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

  Thanks

• • Reply
  Deepak Panch...

  Contributor

  2930 Points

  1210 Posts

  Re: MTD,YTD Query

  Dec 08, 2017 01:20 AM|Deepak Panchal|LINK

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• • Reply
  JagjitSingh

  Member

  504 Points

  1777 Posts

  Re: MTD,YTD Query

  Dec 17, 2017 04:27 PM|JagjitSingh|LINK

  Hi

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

  Thanks

• • Reply
  Deepak Panch...

  Contributor

  2930 Points

  1210 Posts

  Re: MTD,YTD Query

  Dec 19, 2017 09:07 AM|Deepak Panchal|LINK

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.