So far, I have the following SQL Server 2005 query:
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)
How do I add another column (RunningTotal) to the query which sums up the (Invoiced-Paid) result from the previous day to the one for today
Example:
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
Couple remarks on your current solution:
D for "dates" makes sense. y for "Invoices" does not. d_y_m does not match your date format either. Keep the table and column aliases meaningful.date types and convert the values once in the final select.Sample data
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);
Solution
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;
Result
Only listing the first 10 of the 30 rows for November.
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
Fiddle to see it in action.
Extra: why not to count both totals in one query.
Using the same sample data you can run this query to zoom in a particular date, here: 2020-11-01. On this date the sample data has 2 invoices and 1 payment.
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;
Just executing the joins would give you the result below. Because of the combined left join the payment row is listed twice!
dr.DateValue | i.Total | p.Total
------------ | ------- | -------
2020-11-01 | 20 | 5
2020-11-01 | 4 | 5 --> payment row got joined TWICE
Summing up those rows gives an invalid payment sum for that day.
group by dr.DateValue | sum(i.Total) | sum(p.Total)
--------------------- | ------------ | ------------
2020-11-01 | 24 | 10 --> last sum is WRONG !
Edit: SQL Server 2005 version with cross apply. But a SQL Server version update is still recommended!
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;