How do I join on a timestamp and include NULL entries?

Question

I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in April would have 30 entries.

However, I want the budget amount over days to exist permanently even if there are no invoices for that day, either yet or actually on that day.

My current query joins on the date_part('day',a.date) of a timestamp which only hits if there are invoices on that day. I am using PostgreSQL version 11.3. I have tried all join possibilities, and end up with the same result each time. Unless I use CROSS JOIN which does give all 30 days, but the sum of ALL invoices against each day. I had thought that a FULL OUTER JOIN would work, or some other way of using UNION ALL, but as yet I am unsuccessful.

WITH RECURSIVE t(days,budget) AS (
SELECT 1 as days,b.amount/30 AS budget
FROM budgets b
WHERE b.id = 10
UNION ALL
SELECT t.days + 1,t.budget
FROM t WHERE t.days < 30)
SELECT
t.days AS "Day",
COALESCE ( SUM ( CASE WHEN a.account_id IN (17,43,44)
THEN a.credit - a.debit ELSE 0 END),0) AS "Invoices",
t.budget AS "Budget"
FROM t
LEFT JOIN accounting a
ON date_part('day',a.date) = t.days
WHERE a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.account_id = 20
GROUP BY 1,3;

For the ouput, lets assume days 1, 2, and 3 have passed where day 2 had no entries.
Current Output
Day Invoices Budget
1 100 50
3 75 50

Desired Output
Day Invoices Budget
1 100 50
2 0 50
3 75 50
4 0 50
5 0 50
...
30 0 50

I am open to other query ideas to accomplish this. I appreciate the help!

Answer 1

I have managed to get this, by removing the join altogether from the main query and creating a CASE WHEN subquery to look first if a match is made, and assign 0 if not. Then, to sum the invoices on conditions met.

If there is a better method to handle this than the query I now have below, please let me know. Since I am working with a CASE WHEN and subqueries within, it reads to me that this part can be better streamlined somehow. But if it works, it works and does not seem terribly expensive.

Also, please note: above I make a reference to a.account_id in error, on line 18 (second from the bottom). That happened when I modified the exact tables being used in my situation to a more simple set for understanding the SQL being used. To be sure of the fields in my accounting table representation:

a.account_id = IDs representing Chart of Accounts
a.invoice_id = IDs representing the Sales Group on the invoice (this above is in error at a.account_id is 20, and should read a.invoice_id = 20)
a.credit = Double Ledger Accounting Credits
a.debit = Double Ledger Accounting Debits

WITH RECURSIVE t(days,budget) AS (
SELECT 1 as days,b.amount/30 AS budget
FROM budgets b
WHERE b.id = 10
UNION ALL
SELECT t.days + 1,t.budget
FROM t WHERE t.days < 30)
SELECT
t.days AS "Day",
SUM ( CASE WHEN
( SELECT a.date FROM accounting a
LEFT JOIN t ON date_part('day',a.date) = t.days
WHERE a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.invoice_id = 20
LIMIT 1) IS NULL
THEN 0 ELSE
( SELECT SUM
( CASE WHEN a.account_id IN (17,43,44)
THEN a.credit - a.debit ELSE 0 END)
FROM accounting a
WHERE date_part('day',a.date) = t.days
AND a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.invoice_id = 20)
END ) AS "Invoices",
t.budget AS "Budget"
FROM t
GROUP BY 1,3
ORDER BY 1;

I appreciate those who took a peek at this query, and considered an approach to solve it.