Сгенерировать финансовый сводный отчет

Вопрос или проблема

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]);

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]

Ответ или решение

Для создания сводного финансового отчета с использованием представления FinancialSummaryView и с метриками, указанными в вашем запросе, можно использовать подход, основывающийся на множестве операций в рамках одного запроса без необходимости прибегать к курсорам или циклам. Это позволяет нам оставаться в контексте работы с наборами данных, что существенно повышает производительность системы.

Шаги по созданию сводного отчета

1. Инициализация переменных: Вместо использования курсора, мы можем использовать CTE (Common Table Expressions) или вложенные запросы для агрегации данных по месяцам.

2. Расчет необходимых величин: Для получения значений TY, LY, TYTD, LYTD можно использовать оконные функции, чтобы избежать множественных запросов.

3. Формирование окончательного отчета: Объединим результаты в итоговую таблицу.

Пример 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);

WITH FinancialData AS (
    SELECT 
        YEAR(Date) AS [Year],
        MONTH(Date) AS [Month],
        SUM(CASE WHEN YEAR(Date) = @year THEN NetTotal ELSE 0 END) AS [TY],
        SUM(CASE WHEN YEAR(Date) = @previousyear THEN NetTotal ELSE 0 END) AS [LY]
    FROM 
        dbo.FinancialSummaryView
    WHERE 
        SchoolId = @schoolid
    GROUP BY 
        YEAR(Date),
        MONTH(Date)
),
CumulativeData AS (
    SELECT 
        Month,
        TY,
        LY,
        SUM(TY) OVER (ORDER BY Month) AS TYTD,
        SUM(LY) OVER (ORDER BY Month) AS LYTD
    FROM 
        FinancialData
)
SELECT 
    Month,
    TY,
    LY,
    TY - LY AS [TY_vs_LY],
    TYTD,
    LYTD,
    TYTD - LYTD AS [TYTD_vs_LYTD]
FROM 
    CumulativeData
ORDER BY 
    Month;

Объяснение структуры запроса

• CTE FinancialData: Этот блок собирает данные по месяцам, агрегируя суммы для текущего года (TY) и прошлого года (LY). Это позволяет нам избежать многократного обращения к представлению.

• CTE CumulativeData: Здесь рассчитываются накопительные итоговые значения для TYTD и LYTD с использованием оконных функций SUM() OVER (ORDER BY Month). Это эффективный способ получения кумулятивных значений без дополнительных затруднений.

• Итоговый SELECT: Мы собираем все необходимые значения в одном запросе и упорядочиваем вывод по месяцам для удобства восприятия.

Заключение

Данный подход продемонстрировал, как можно в значительной степени упростить процесс сбора и агрегации данных без использования курсоров и циклов, что делает его более производительным и легче поддающимся поддержке. Использование CTE и оконных функций позволяет эффективно работать с наборами данных и получать необходимые финансовые метрики, что соответствует современным стандартам разработки в области информационных технологий.

Данный метод не только упрощает реализацию, но и позволяет легко адаптировать запрос к изменяющимся требованиям или при расширении структуры данных в будущем.