I am unable to follow your requirement and the link for the
expected result is reported as invalid.
Assuming it is for financial year (01-Apr-2024 to 31-Mar-2025) if you want something like this:
Apr-2024 - (sum of bills from 01-apr-2024 to 30-apr-2024)
May-2024 - (sum of bills from 01-apr-2024 to 31-may-2024)
...
Mar-2025 - (sum of bills from 01-apr-2024 to 31-mar-2025)
The example is in PostgreSQL but the query should be similar in SQL Server. The monthwise totals for the random date / amount are also shown for easy validation of the YTD values:
with
months_cte as
(
select '2024-04-30'::date month_end union
select '2024-05-31'::date month_end union
select '2024-06-30'::date month_end union
select '2024-07-31'::date month_end
),
bills_cte as
(
select '2024-04-01'::date bill_date, 1125 bill_amount union
select '2024-04-19'::date, 916 union
select '2024-04-24'::date, 1632 union
select '2024-05-04'::date, 1962 union
select '2024-05-16'::date, 1468 union
select '2024-05-29'::date, 1279 union
select '2024-06-06'::date, 863 union
select '2024-06-17'::date, 1969 union
select '2024-06-26'::date, 1213 union
select '2024-07-07'::date, 1310 union
select '2024-07-18'::date, 1576 union
select '2024-07-23'::date, 1831 union
select '2024-07-27'::date, 1003
)
select to_char(month_end, 'Mon-yyyy') || '-YTD' month_end,
sum(case when bill_date <= month_end then bill_amount else 0 end) bill_sum
from months_cte inner join bills_cte on true
group by to_char(month_end, 'Mon-yyyy') || '-YTD'
union
select to_char(bill_date, 'Mon-yyyy') || '-TOTAL' , sum(bill_amount) month_sum
from bills_cte
group by to_char(bill_date, 'Mon-yyyy') || '-TOTAL'
;
Output:
Monthwise Total
Apr-2024-TOTAL 3673
May-2024-TOTAL 4709
Jun-2024-TOTAL 4045
Jul-2024-TOTAL 5720
YTD - from 01-APR-2024 to end of applicable month
Apr-2024-YTD 3673
May-2024-YTD 8382
Jun-2024-YTD 12427
Jul-2024-YTD 18147