Generate financial summary report

I have a view, FinancialSummaryView, aggregating the total charges, total refunds, and net total (charges - refunds) by year, month, and location. The resulting data looks like this:

SchoolId	LocationId	Year	Month	TotalCharges	NetTotal
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2023	7	90.00	90.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2023	8	180.00	180.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2023	9	90.00	90.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2023	10	90.00	90.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2023	11	90.00	90.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2023	12	210.00	210.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	1	210.00	210.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	2	210.00	210.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	3	210.00	210.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	4	210.00	210.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	5	455.00	455.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	6	215.00	215.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	7	485.00	485.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	8	520.00	520.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	9	820.00	820.00
3FA85F64-5717-4562-B3FC-2C963F66AFA6	3FA85F64-5717-4562-B3FC-2C963F66AFA6	2024	10	610.00	610.00

For reference, FinancialSummaryView looks like this:

SELECT 
    [SchoolId],
    [LocationId],
    DATEPART(YEAR, [Date]) AS [Year],
    DATEPART(MONTH, [Date]) AS [Month],
    COALESCE(SUM([Amount]),0) AS [TotalCharges],
    COALESCE(SUM([RefundAmount]),0) AS [TotalRefunds],
    COALESCE(SUM([Amount]),0) - COALESCE(SUM([RefundAmount]), 0) as [NetTotal]
FROM 
    [Transactions]
GROUP BY 
    [SchoolId],
    [LocationId],
    DATEPART(YEAR, [Date]),
    DATEPART(MONTH, [Date]);

I'm trying to generate additional aggregate financial data from this view, such as

Gross for the current month from this year (TY)
Gross for the current month from last year (LY)
Difference between the two gross amounts (TY - LY)
Year-to-date of this year (TYTD)
Year-to-date of last year (LYTD)
Difference between the two year to date amounts (TYTD - LYTD)

For the year-to-date values, they should each only look at their respective year amounts for all months up to and including the current month.

The results should look similar to this:

	TY	LY	TY v. LY	TYTD	LYTD	TYTD V. LYTD
January	$210.00	$0.00	$210.00	$210.00	$0.00	$210.00
February	$210.00	$0.00	$210.00	$420.00	$0.00	$420.00
March	$210.00	$0.00	$210.00	$630.00	$0.00	$630.00
April	$210.00	$0.00	$210.00	$840.00	$0.00	$840.00
May	$455.00	$0.00	$455.00	$1295.00	$0.00	$1295.00
June	$215.00	$0.00	$215.00	$1510.00	$0.00	$1510.00
July	$485.00	$90.00	$395.00	$1995.00	$90.00	$1905.00
August	$520.00	$180.00	$340.00	$2515.00	$270.00	$2245.00
September	$820.00	$90.00	$730.00	$3335.00	$360.00	$2975.00
October	$610.00	$90.00	$520.00	$3945.00	$450.00	$3495.00

I have this working for a single month, using the following SQL

declare @schoolid uniqueidentifier = '3FA85F64-5717-4562-B3FC-2C963F66AFA6'
declare @currentDate datetimeoffset = '2024-10-24'
declare @previousyear int = Year(DateAdd(year,-1,@currentDate))
declare @month int = Month(@currentDate)
declare @year int = Year(@currentDate)
declare @currentYearMonthGross money
declare @previousYearMonthGross money
declare @currentYearToDateGross money
declare @previousYearToDateGross money

select @currentYearMonthGross = [NetTotal] from dbo.[FinancialSummaryView] where [SchoolId] = @schoolid and [Month] = @month and [Year] = @year
select @currentYearToDateGross = SUM([NetTotal]) from dbo.[FinancialSummaryView] where [SchoolId] = @schoolid and [Month] <= @month and [Year] = @year

select @previousYearMonthGross = [NetTotal] from dbo.[FinancialSummaryView] where [SchoolId] = @schoolid and [Month] = @month and [Year] = @previousyear
select @previousYearToDateGross = SUM([NetTotal]) from dbo.[FinancialSummaryView] where [SchoolId] = @schoolid and [Month] <= @month and [Year] = @previousyear

select @currentYearMonthGross as [TY], @previousYearMonthGross as [LY], @currentYearMonthGross-@previousYearMonthGross as [TY v LY],
@currentYearToDateGross as [TYTD], @previousYearToDateGross as [LYTD], @currentYearToDateGross-@previousYearToDateGross as [TYTD v LYTD]

I can write this as a stored procedure that generates the data for one month. I also know I can write this using a while loop iterate over the months, call the stored procedure n times, and store the results into a temp table/table variable to then select from.

The question is this: Are there any other options that would allow me to simplify things so that I can avoid the cursor or loop and perform this as set-based?

Solution

I think you can start with a unique SQL query like this one. In this case it only calculates the Current month gross and the year-to-date amount using the appropriate window frames. For previous year calculations, you can use LEFT JOIN to pair previous year's data. For other calculations you can use this example as a base.

WITH TransactionsSummary AS (
SELECT 
 [SchoolId],
 [LocationId],
 DATEPART(YEAR, [Date]) AS [Year],
 DATEPART(MONTH, [Date]) AS [Month],
 COALESCE(SUM([Amount]), 0) AS [TotalCharges],
 COALESCE(SUM([RefundAmount]), 0) AS [TotalRefunds],
 COALESCE(SUM([Amount]), 0) - COALESCE(SUM([RefundAmount]), 0) AS [NetTotal]
FROM 
 [Transactions]
GROUP BY 
 [SchoolId],
 [LocationId],
 DATEPART(YEAR, [Date]),
 DATEPART(MONTH, [Date])
)
SELECT 
 T1.[Year],
 T1.[Month],
 T1.[NetTotal] AS [TY],  
 COALESCE(T2.[NetTotal], 0) AS [LY],
 SUM(T1.[NetTotal]) OVER (PARTITION BY T1.[Year] ORDER BY T1.[Month] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [TYTD],
 SUM(T2.[NetTotal]) OVER (PARTITION BY T2.[Year] ORDER BY T2.[Month] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [LYTD]
FROM 
 TransactionsSummary T1
LEFT JOIN 
 TransactionsSummary T2
  ON T1.[SchoolId] = T2.[SchoolId]
  AND T1.[LocationId] = T2.[LocationId]
  AND T1.[Month] = T2.[Month]
  AND T1.[Year] = T2.[Year] + 1  -- same month / previous year
ORDER BY 
 T1.[Year], 
 T1.[Month];

As you can see, it uses your query as a base data source to create a summary by year,month. TYTD is the sum of the partition of the current Year between all preceeding rows (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Previous year calculations uses T2 which is the paired result of the same month but previous year.