Need YTD and MTD calculations in SQL

Question

Date       Amt  ytd mtd
01-Jan-21   1   2   2
01-Jan-21   1   2   2
02-Jan-21   1   3   3
03-Jan-21   1   4   4
01-Feb-21   1   5   1
02-Feb-21   1   6   2
03-Feb-21   1   7   3
04-Feb-21   1   8   4
05-Feb-21   1   9   5
01-Mar-21   1   10  1
02-Mar-21   1   11  2
03-Mar-21   1   12  3
04-Mar-21   1   13  4
01-Apr-21   1   14  1
02-Apr-21   1   15  2
03-Apr-21   1   16  3
01-May-21   1   17  1
02-May-21   1   18  2
03-May-21   1   19  3
04-May-21   1   20  4
05-May-21   1   21  5
06-May-21   1   22  6

I have the first two columns (Date, Amt) and i need the YTD and MTD columns in MS SQL so that i can show the above table.

If you are using `SQL Server`, why also tagged `MySQL` and `Postgresql` ? — Squirrel, Jan 06 '22 at 06:40
You need to use `SUM () OVER ( PARTITION BY ... ORDER BY ...)`. Refer to [documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15) — Squirrel, Jan 06 '22 at 06:47
@crimson589 ... The date is same for both the rows. So for that date, i should show me data for that date — Anirban Patra, Jan 06 '22 at 06:54
Why MTD for 01-Jan-21 is equal to 2, not 1? why MTD for 03-Apr-21 is 2, not 3? — Akina, Jan 06 '22 at 07:48
Take a look at [how to ask a good question](https://stackoverflow.com/help/how-to-ask). — Chris Albert, Jan 13 '22 at 14:22

LukStorms · Answer 1 · 2022-01-06T11:04:04.947

1

Seems like a rolling COUNT OVER was used to calculate the ytd & mtd in the Oracle source.
(Personally, I would prefere RANK or DENSE_RANK)

And since Oracle datestamps can be casted to a DATE as-is.

SELECT [Date], Amt
, ytd = COUNT(*) OVER (ORDER BY CAST([Date] AS DATE)) 
, mtd = COUNT(*) OVER (PARTITION BY EOMONTH(CAST([Date] AS DATE)) ORDER BY CAST([Date] AS DATE)) 
FROM your_table
ORDER BY CAST([Date] AS DATE)

Date	Amt	ytd	mtd
01-Jan-21	1	2	2
01-Jan-21	1	2	2
02-Jan-21	1	3	3
03-Jan-21	1	4	4
01-Feb-21	1	5	1
02-Feb-21	1	6	2
03-Feb-21	1	7	3
04-Feb-21	1	8	4
05-Feb-21	1	9	5

db<>fiddle here

edited Jan 06 '22 at 11:04

answered Jan 06 '22 at 10:44

LukStorms

28,916
5
31
45

`ROWS UNBOUNDED PRECEDING` would be wise, see fiddle https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=495d370855ec81c76076aec2235844c6 – Charlieface Jan 06 '22 at 20:13
@Charlieface But the OP's expected results also have 2 for 01-JAN-21, so it's fine without the ROWS. – LukStorms Jan 06 '22 at 20:18
Hum good point if that's what OP wants, about the only time I've ever seen that somebody *wanted* `RANGE UNBOUNDED PRECEDING`, it really is a stupid default. Do you see how strange those results look? Especially in a large dataset with only a few duplicates in the ordering key, suddenly your cumulative sum isn't actually cumulative. – Charlieface Jan 06 '22 at 20:21
@Charlieface That default only really bothers me for the LAST_VALUE function. It's basically useless with the default, so you'll always end up using FIRST_VALUE with descending order. – LukStorms Jan 06 '22 at 21:40

Need YTD and MTD calculations in SQL

1 Answers1