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?
Select t.title, t.Date,
Sum(y.Amount) YTD,
Sum(m.Amount) MTD
From table t
left join table y
on y.Title = t.Title
and datediff(year, y.Date, t.Date) = 0
and y.Date <= t.Date
left join table m
on m.Title = t.Title
and datediff(month, m.Date, t.Date) = 0
and m.Date <= t.Date
and m.Date <> y.Date
Group by t.title, t.Date
The accepted solution is incorrect. Let's say we have a following table:
ID Title Date Amount
--- ------ ---------- ------
1 Cust A 2020-01-01 2.00
2 Cust A 2020-01-05 3.00
3 Cust A 2020-02-01 5.00
The accepted answer would give us these results:
Title Date YTD MTD
------ ---------- ----- -----
Cust A 2021-01-01 2.00 2.00
Cust A 2021-01-05 10.00 10.00
Cust A 2021-02-01 10.00 15.00
This is due to the fact that each join multiplies the number of records by the number of matching records. This can be easily seen when the aggregation is removed:
Select t.title, t.Date, y.Date, m.Date,
y.Amount,
m.Amount
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
Order by t.title, t.Date, y.Date, m.Date
Results:
Title t.Date y.Date m.Date y.Amount m.Amount
----- ---------- ---------- ---------- -------- --------
Cust A 2021-01-01 2021-01-01 2021-01-01 2 2
Cust A 2021-01-05 2021-01-01 2021-01-01 2 2
Cust A 2021-01-05 2021-01-01 2021-01-05 2 3
Cust A 2021-01-05 2021-01-05 2021-01-01 3 2
Cust A 2021-01-05 2021-01-05 2021-01-05 3 3
Cust A 2021-02-01 2021-01-01 2021-02-01 2 5
Cust A 2021-02-01 2021-01-05 2021-02-01 3 5
Cust A 2021-02-01 2021-02-01 2021-02-01 5 5
Here is a modified select that produces correct results:
Select a.title, a.Date,
Sum(Case When datediff(year, b.Date, a.Date) = 0 Then b.Amount Else 0 End) YTD,
Sum(Case When datediff(month, b.Date, a.Date) = 0 Then b.Amount Else 0 End) MTD
From [table] a
join [table] b
on a.Title = b.Title
and b.Date <= a.Date
Group by a.title, a.Date
Results:
Title Date YTD MTD
------ ---------- ----- -----
Cust A 2021-01-01 2.00 2.00
Cust A 2021-01-05 5.00 5.00
Cust A 2021-02-01 10.00 5.00
Here is a SQL Fiddle with all the current answers plus new solutions.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With