SQL Server 2019

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

create table Invoices
(
  InvoiceID int,
  InvoiceDate date,
  Total money
);

insert into Invoices (InvoiceID, InvoiceDate, Total) values
(11,'2020-10-25', 20),
(13,'2020-10-23', 15),
(1,'2020-11-01', 20),
(2,'2020-11-01', 14),
(3,'2020-11-02', 40),
(4,'2020-11-02', 35),
(5,'2020-11-03', 10),
(6,'2020-11-04', 63),
(7,'2020-11-04', 42);

select * from Invoices;


InvoiceID
InvoiceDate
Total


11
2020-10-25
20.0000

13
2020-10-23
15.0000

1
2020-11-01
20.0000

2
2020-11-01
14.0000

3
2020-11-02
40.0000

4
2020-11-02
35.0000

5
2020-11-03
10.0000

6
2020-11-04
63.0000

7
2020-11-04
42.0000


create table Payments
(
  InvoiceID int,
  PaymentDate date,
  Total money
);

insert into Payments (InvoiceID, PaymentDate, Total) values
(5,'2020-11-07', 10),
(6,'2020-11-08', 63),
(4,'2020-11-09', 35),
(2,'2020-11-10', 14),
(7,'2020-11-11', 42),
(11,'2020-11-13', 20),
(13,'2020-11-14', 15);

select * from Payments;


InvoiceID
PaymentDate
Total


5
2020-11-07
10.0000

6
2020-11-08
63.0000

4
2020-11-09
35.0000

2
2020-11-10
14.0000

7
2020-11-11
42.0000

11
2020-11-13
20.0000

13
2020-11-14
15.0000


-- SQL 2005

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(case
                      when i.InvoiceDate BETWEEN '2020-11-01' AND '2020-11-30' -- check if matching invoice was found
                      then p.Total                 -- YES = include Total amount in sum
                      else 0                       -- NO  = exclude total amount from sum
                    end), 0) as Paid
  from DateRange dr
  left join Payments p
    on p.PaymentDate = dr.DateValue
  left join Invoices i
    on  i.InvoiceId = p.InvoiceId -- check for invoice related to payment
  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;


YYYY.MM.DD
Invoiced
CumInvoiced
Paid
CumPaid
RunningTotal


2020.11.01
34.0000
34.0000
0.0000
0.0000
34.0000

2020.11.02
75.0000
109.0000
0.0000
0.0000
109.0000

2020.11.03
10.0000
119.0000
0.0000
0.0000
119.0000

2020.11.04
105.0000
224.0000
0.0000
0.0000
224.0000

2020.11.05
0.0000
224.0000
0.0000
0.0000
224.0000

2020.11.06
0.0000
224.0000
0.0000
0.0000
224.0000

2020.11.07
0.0000
224.0000
10.0000
10.0000
214.0000

2020.11.08
0.0000
224.0000
63.0000
73.0000
151.0000

2020.11.09
0.0000
224.0000
35.0000
108.0000
116.0000

2020.11.10
0.0000
224.0000
14.0000
122.0000
102.0000

2020.11.11
0.0000
224.0000
42.0000
164.0000
60.0000

2020.11.12
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.13
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.14
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.15
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.16
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.17
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.18
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.19
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.20
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.21
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.22
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.23
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.24
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.25
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.26
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.27
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.28
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.29
0.0000
224.0000
0.0000
164.0000
60.0000

2020.11.30
0.0000
224.0000
0.0000
164.0000
60.0000


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

InvoiceID	InvoiceDate	Total
11	2020-10-25	20.0000
13	2020-10-23	15.0000
1	2020-11-01	20.0000
2	2020-11-01	14.0000
3	2020-11-02	40.0000
4	2020-11-02	35.0000
5	2020-11-03	10.0000
6	2020-11-04	63.0000
7	2020-11-04	42.0000

InvoiceID	PaymentDate	Total
5	2020-11-07	10.0000
6	2020-11-08	63.0000
4	2020-11-09	35.0000
2	2020-11-10	14.0000
7	2020-11-11	42.0000
11	2020-11-13	20.0000
13	2020-11-14	15.0000

YYYY.MM.DD	Invoiced	CumInvoiced	Paid	CumPaid	RunningTotal
2020.11.01	34.0000	34.0000	0.0000	0.0000	34.0000
2020.11.02	75.0000	109.0000	0.0000	0.0000	109.0000
2020.11.03	10.0000	119.0000	0.0000	0.0000	119.0000
2020.11.04	105.0000	224.0000	0.0000	0.0000	224.0000
2020.11.05	0.0000	224.0000	0.0000	0.0000	224.0000
2020.11.06	0.0000	224.0000	0.0000	0.0000	224.0000
2020.11.07	0.0000	224.0000	10.0000	10.0000	214.0000
2020.11.08	0.0000	224.0000	63.0000	73.0000	151.0000
2020.11.09	0.0000	224.0000	35.0000	108.0000	116.0000
2020.11.10	0.0000	224.0000	14.0000	122.0000	102.0000
2020.11.11	0.0000	224.0000	42.0000	164.0000	60.0000
2020.11.12	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.13	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.14	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.15	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.16	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.17	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.18	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.19	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.20	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.21	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.22	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.23	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.24	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.25	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.26	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.27	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.28	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.29	0.0000	224.0000	0.0000	164.0000	60.0000
2020.11.30	0.0000	224.0000	0.0000	164.0000	60.0000