Блог

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

Я использую две таблицы: одну с данными транзакций и одну календарную таблицу.

Я смог получить желаемый результат для одной учетной записи с помощью кода, показанного ниже (ZWID — это идентификатор банковского счета)

 WITH sum_transactions as
(
  SELECT csd.ZWID, csd.ValueDate, sum_total = sum(csd.amount) 
  FROM myDataBase.CashData as csd
  WHERE csd.ValueDate > '20190131' and csd.ValueDate <= '20190208'
  AND csd.ZWID IN (1592)
  GROUP BY csd.ZWID, csd.ValueDate
)

SELECT st.zwid, cal.Calendar_Date, st.sum_total 
FROM treasury.dbo.calendar as cal
LEFT JOIN sum_transactions as st on st.ValueDate = cal.Calendar_Date
WHERE cal.Calendar_Date > '20190131' and cal.Calendar_Date<= '20190208'
ORDER BY 1, 2
  

Я получаю следующий (желаемый) результат:

 zwid    Calendar_Date   sum_total
1592    2019-02-01   606174,09
NULL    2019-02-02  NULL
NULL    2019-02-03  NULL
1592    2019-02-04  -600000
NULL    2019-02-05  NULL
NULL    2019-02-06  NULL
NULL    2019-02-07  NULL
NULL    2019-02-08  NULL
  

т. е. в течение периода было два дня с транзакциями по этому конкретному банковскому счету.

Теперь, когда я добавляю вторую учетную запись (ID 1593) (в инструкцию IN), я бы надеялся получить второй набор из 8 новых строк (с 01 по 08 февраля) либо с суммой, либо с нулевым значением (всего 16 строк для обеих учетных записей).

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

 zwid    Calendar_date   sum_total
NULL    2019-02-02  NULL
NULL    2019-02-03  NULL
1592    2019-02-04  -600000
1592    2019-02-01  606174,09
1593    2019-02-01  -847958,75
1593    2019-02-04  303105,26
1593    2019-02-05  -285312,64
1593    2019-02-06  502762,95
1593    2019-02-07  405372,02
1593    2019-02-08  326213,87
  

Очевидно, что мне не удается заставить запрос записывать все даты для каждой учетной записи отдельно.

Как мне нужно изменить мой запрос, чтобы он выполнялся через один банковский счет, записывать все дни периода (значение или NULL) и только затем переходить к следующей учетной записи?

Обновление: я просматриваю большое количество банковских счетов. Количество учетных записей будет меняться со временем

Я думаю, это может быть то, что вам нужно, попробуйте и дайте мне знать. Но в основном мне приходилось использовать CROSS APPLY для полного списка идентификаторов / дат, которые вы искали, а затем я использовал остальную часть вашего кода, чтобы получить желаемые результаты:

 DROP TABLE IF EXISTS #Test;
DROP TABLE IF EXISTS #FullCalendar;

CREATE TABLE #Test
    (
        ZWID INT ,
        ValueDate DATE ,
        Amount MONEY
    );

INSERT INTO #Test ( ZWID ,
                    ValueDate ,
                    Amount )
VALUES ( 1, '20190101', 100.00 ) ,
       ( 1, '20190101', 75.00 ) ,
       ( 1, '20190108', 75.00 ) ,
       ( 1, '20190110', 50.00 ) ,
       ( 2, '20190101', 25.00 ) ,
       ( 2, '20190102', 35.00 ) ,
       ( 2, '20190103', 50.00 ) ,
       ( 2, '20190103', 125.00 ) ,
       ( 3, '20190102', 150.00 ) ,
       ( 3, '20190109', 100.00 ) ,
       ( 3, '20190110', 75.00 ) ,
       ( 3, '20190110', 75.00 );

SELECT dd.Date, t.ZWID
INTO #FullCalendar
FROM dbo.DateDimension AS dd
CROSS APPLY #Test AS t
WHERE dd.Date >= '20190101' AND dd.Date < '20190111'
GROUP BY dd.Date ,
         t.ZWID
--SELECT * FROM #FullCalendar ORDER BY ZWID, Date

;WITH sum_trans AS (

SELECT
t.ZWID, t.ValueDate, sum_total = SUM(t.Amount)
FROM #Test AS t
GROUP BY t.ZWID ,
         t.ValueDate )

SELECT fc.Date, fc.ZWID, st.sum_total
FROM #FullCalendar AS fc
LEFT OUTER JOIN sum_trans AS st ON st.ZWID = fc.ZWID AND fc.Date = st.ValueDate
ORDER BY fc.ZWID,fc.Date;
  

Здесь также оставляю мой старый ответ.

Я смог получить желаемый результат, используя 2 CTE и объединение ВСЕХ:

 WITH sum_transactions as
(
  SELECT csd.ZWID, csd.ValueDate, sum_total = sum(csd.amount) 
  FROM myDataBase.CashData as csd
  WHERE csd.ValueDate > '20190131' and csd.ValueDate <= '20190208'
  AND csd.ZWID IN (1592)
  GROUP BY csd.ZWID, csd.ValueDate
) ,
WITH sum_transactions2 as
(
  SELECT csd.ZWID, csd.ValueDate, sum_total = sum(csd.amount) 
  FROM myDataBase.CashData as csd
  WHERE csd.ValueDate > '20190131' and csd.ValueDate <= '20190208'
  AND csd.ZWID IN (1593)
  GROUP BY csd.ZWID, csd.ValueDate
)

SELECT st.zwid, cal.Calendar_Date, st.sum_total 
FROM treasury.dbo.calendar as cal
LEFT JOIN sum_transactions as st on st.ValueDate = cal.Calendar_Date
WHERE cal.Calendar_Date > '20190131' and cal.Calendar_Date<= '20190208'
ORDER BY 1, 2

UNION ALL

SELECT st.zwid, cal.Calendar_Date, st.sum_total 
FROM treasury.dbo.calendar as cal
LEFT JOIN sum_transactions2 as st on st.ValueDate = cal.Calendar_Date
WHERE cal.Calendar_Date > '20190131' and cal.Calendar_Date<= '20190208'
ORDER BY 1, 2