Выполнение общего запроса SQL Server

Пока у меня есть следующий запрос SQL Server 2005:

WITH D AS (
SELECT CONVERT(VARCHAR, '2020.11.01', 102) AS d_y_m, CAST('2020-11-01' AS DATETIME) AS dt
UNION ALL
SELECT CONVERT(VARCHAR, DATEADD(dd, 1, z.dt), 102) AS d_y_m, DATEADD(dd, 1, z.dt)
FROM D AS z
WHERE DATEADD(dd, 1, z.dt) <= '2020-11-30')
SELECT x.d_y_m, ISNULL(SUM(y.Total), 0) AS [Invoiced], ISNULL(SUM(FEI.Total), 0) AS [Paid] FROM D x
LEFT JOIN Invoices y ON CONVERT(VARCHAR, y.InvoiceDate, 102) = x.d_y_m
LEFT JOIN Payments AS FEI ON CONVERT(VARCHAR, FEI.PaymentDate, 102) = x.d_y_m
GROUP BY x.d_y_m
ORDER BY x.d_y_m OPTION (MAXRECURSION 0)

Как мне добавить еще один столбец (RunningTotal) в запрос, который суммирует результат (Invoiced-Paid) за предыдущий день с результатом на сегодняшний день

Пример:

d_y_m | Invoiced | Paid | RunningTotal

2020.11.01 | 24 | 5 | 19

2020.11.02 | 45 | 2 | 62

2020.11.03 | 10 | 20 | 52

2020.11.04 | 5 | 0 | 57

2020.11.05 | 0 | 10 | 47
Источник

1 ответ

Пара замечаний по вашему текущему решению:

  • Не используйте "случайные" псевдонимы таблиц. Dдля "дат" имеет смысл. yдля "Счетов" нет. d_y_mтакже не соответствует вашему формату даты. Сохраняйте значимость псевдонимов таблиц и столбцов.
  • Не перетаскивайте преобразование даты через все решение. Работайте со значениями даты как dateтипы и конвертировать значения один раз в финальном select.
  • Не группируйте суммы выставленных и оплаченных сумм в одном запросе. Если у вас есть несколько счетов или платежей в один день, то суммы будут неверными! Объяснение см. в разделе «Дополнительно» внизу.
  • Сделайте так, чтобы нам было легко вам помочь. В следующий раз, пожалуйста, предоставьте образцы данных, которые мы можем скопировать и вставить вместо того, чтобы изобретать свои собственные.
  • SQL Server 2005 официально не поддерживается с 12 апреля 2016 г. Пришло время искать новую версию!

Образец данных

      create table Invoices
(
  InvoiceDate date,
  Total money
);

insert into Invoices (InvoiceDate, Total) values
('2020-11-01', 20),
('2020-11-01',  4),
('2020-11-02', 40),
('2020-11-02',  5),
('2020-11-03', 10),
('2020-11-04',  3),
('2020-11-04',  2);

create table Payments
(
  PaymentDate date,
  Total money
);

insert into Payments (PaymentDate, Total) values
('2020-11-01',  5),
('2020-11-02',  2),
('2020-11-03', 10),
('2020-11-03', 10),
('2020-11-05', 10);

Решение

      with DateRange as
(
  select convert(date, '2020-11-01') as DateValue
  union all
  select dateadd(day, 1, dr.DateValue)
  from DateRange dr
  where dr.DateValue < '2020-11-30'
),
InvoicedTotal as
(
  select dr.DateValue,
         isnull(sum(i.Total), 0) as Invoiced
  from DateRange dr
  left join Invoices i
    on i.InvoiceDate = dr.DateValue
  group by dr.DateValue
),
PaidTotal as
(
  select dr.DateValue,
         isnull(sum(p.Total), 0) as Paid
  from DateRange dr
  left join Payments p
    on p.PaymentDate = dr.DateValue
  group by dr.DateValue
)
select convert(varchar(10), dr.DateValue, 102) as [YYYY.MM.DD],
       it.Invoiced as [Invoiced],
       sum(it.Invoiced) over(order by it.DateValue
                             rows between unbounded preceding and current row) as [CumInvoiced],
       pt.Paid as [Paid],
       sum(pt.Paid) over(order by pt.DateValue
                         rows between unbounded preceding and current row) as [CumPaid],
       sum(it.Invoiced) over(order by it.DateValue
                             rows between unbounded preceding and current row) -
       sum(pt.Paid) over(order by pt.DateValue
                         rows between unbounded preceding and current row) as [RunningTotal]
from DateRange dr
join InvoicedTotal it
  on it.DateValue = dr.DateValue
join PaidTotal pt
  on pt.DateValue = dr.DateValue
order by dr.DateValue;

Результат

Перечислены только первые 10 из 30 строк за ноябрь.

      YYYY.MM.DD Invoiced CumInvoiced Paid    CumPaid RunningTotal
---------- -------- ----------- ------- ------- ------------
2020.11.01  24.0000     24.0000  5.0000  5.0000      19.0000
2020.11.02  45.0000     69.0000  2.0000  7.0000      62.0000
2020.11.03  10.0000     79.0000 20.0000 27.0000      52.0000
2020.11.04   5.0000     84.0000  0.0000 27.0000      57.0000
2020.11.05   0.0000     84.0000 10.0000 37.0000      47.0000
2020.11.06   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.07   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.08   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.09   0.0000     84.0000  0.0000 37.0000      47.0000
2020.11.10   0.0000     84.0000  0.0000 37.0000      47.0000

Скрипка , чтобы увидеть его в действии.

Дополнительно: почему бы не подсчитать обе суммы в одном запросе.

Используя те же данные образца, вы можете запустить этот запрос, чтобы увеличить конкретную дату, здесь: 2020-11-01. На эту дату данные выборки содержат 2 счета и 1 платеж.

      with DateRange as
(
  select '2020-11-01' as DateValue -- filtering data to explain
)
select dr.DateValue,
       isnull(sum(i.Total), 0) as Invoiced,
       isnull(sum(p.Total), 0) as Paid
from DateRange dr
left join Invoices i
  on i.InvoiceDate = dr.DateValue
left join Payments p
  on p.PaymentDate = dr.DateValue
group by dr.DateValue
order by dr.DateValue;

Просто выполнение соединений даст вам результат ниже. Из-за комбинированного left joinстрока оплаты указана дважды!

      dr.DateValue | i.Total | p.Total
------------ | ------- | -------
2020-11-01   |      20 |       5
2020-11-01   |       4 |       5 --> payment row got joined TWICE

Суммирование этих строк дает неверную сумму платежа за этот день.

      group by dr.DateValue | sum(i.Total) | sum(p.Total)
--------------------- | ------------ | ------------
2020-11-01            |           24 |           10 --> last sum is WRONG !

Изменить: версия SQL Server 2005 с cross apply. Но обновление версии SQL Server по-прежнему рекомендуется!

      with DateRange as
(
  select convert(date, '2020-11-01') as DateValue
  union all
  select dateadd(day, 1, dr.DateValue)
  from DateRange dr
  where dr.DateValue < '2020-11-30'
),
InvoicedTotal as
(
  select dr.DateValue,
         isnull(sum(i.Total), 0) as Invoiced
  from DateRange dr
  left join Invoices i
    on i.InvoiceDate = dr.DateValue
  group by dr.DateValue
),
PaidTotal as
(
  select dr.DateValue,
         isnull(sum(p.Total), 0) as Paid
  from DateRange dr
  left join Payments p
    on p.PaymentDate = dr.DateValue
  group by dr.DateValue
)
select convert(varchar(10), dr.DateValue, 102) as [YYYY.MM.DD],
       it1.Invoiced as [Invoiced],
       it3.Invoiced as [CumInvoiced],
       pt1.Paid as [Paid],
       pt3.Paid as [CumPaid],
       it3.Invoiced - pt3.Paid as [RunningTotal]
from DateRange dr
join InvoicedTotal it1
  on it1.DateValue = dr.DateValue
join PaidTotal pt1
  on pt1.DateValue = dr.DateValue
cross apply ( select sum(it2.Invoiced) as Invoiced
              from InvoicedTotal it2
              where it2.DateValue <= dr.DateValue ) it3
cross apply ( select sum(pt2.Paid) as Paid
              from PaidTotal pt2
              where pt2.DateValue <= dr.DateValue ) pt3
order by dr.DateValue;

Обновлена ​​скрипка .

Источник
Другие вопросы по тегам