Hashir107 Hashir107 - 3 months ago 52
SQL Question

SQL query for calculating mtd, ytd values

I have a table with columns

ID
,
Title
,
Date
,
Amount
.

I need to get MTD, YTD amount values against each transaction based on
Title
,
Date
.

Is there anyone who had done this before?

Answer
Select t.title, t.Date, 
  Sum(y.Amount) YTD,
  Sum(m.Amount) MTD
From table t
   join table y
      on y.Title = t.Title
         and datediff(year, y.Date, t.Date) = 0 
         and y.Date <= t.Date
   join table m
      on m.Title = t.Title
         and datediff(month, m.Date, t.Date) = 0 
         and m.Date <= t.Date
Group by t.title, t.Date
Comments