SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

select * from Invoices;

select i.InvoiceDate,
       sum(i.Total) as Invoiced
from Invoices i
group by i.InvoiceDate
order by i.InvoiceDate;


InvoiceDate
Total


2020-11-01
20.0000

2020-11-01
4.0000

2020-11-02
40.0000

2020-11-02
5.0000

2020-11-03
10.0000

2020-11-04
3.0000

2020-11-04
2.0000




InvoiceDate
Invoiced


2020-11-01
24.0000

2020-11-02
45.0000

2020-11-03
10.0000

2020-11-04
5.0000


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

select * from Payments;

select p.PaymentDate,
       sum(p.Total) as Paid
from Payments p
group by p.PaymentDate
order by p.PaymentDate;


PaymentDate
Total


2020-11-01
5.0000

2020-11-02
2.0000

2020-11-03
10.0000

2020-11-03
10.0000

2020-11-05
10.0000




PaymentDate
Paid


2020-11-01
5.0000

2020-11-02
2.0000

2020-11-03
20.0000

2020-11-05
10.0000


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;


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.11
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.12
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.13
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.14
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.15
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.16
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.17
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.18
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.19
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.20
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.21
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.22
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.23
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.24
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.25
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.26
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.27
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.28
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.29
0.0000
84.0000
0.0000
37.0000
47.0000

2020.11.30
0.0000
84.0000
0.0000
37.0000
47.0000


Warning: Null value is eliminated by an aggregate or other SET operation.


-- do NOT count both totals in one query

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;

-- SQL joins before making the SUM calculations
/*
step 1 : JOINS

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

step 2 : SUM for above rows

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


DateValue
Invoiced
Paid


2020-11-01
24.0000
10.0000

InvoiceDate	Total
2020-11-01	20.0000
2020-11-01	4.0000
2020-11-02	40.0000
2020-11-02	5.0000
2020-11-03	10.0000
2020-11-04	3.0000
2020-11-04	2.0000

InvoiceDate	Invoiced
2020-11-01	24.0000
2020-11-02	45.0000
2020-11-03	10.0000
2020-11-04	5.0000

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.11	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.12	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.13	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.14	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.15	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.16	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.17	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.18	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.19	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.20	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.21	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.22	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.23	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.24	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.25	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.26	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.27	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.28	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.29	0.0000	84.0000	0.0000	37.0000	47.0000
2020.11.30	0.0000	84.0000	0.0000	37.0000	47.0000