How do I join on a timestamp and include NULL entries?
0
votes
1
answer
487
views
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 = '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!
Asked by Nick Buxton
(1 rep)
Apr 23, 2020, 07:46 PM
Last activity: Apr 24, 2020, 10:02 PM
Last activity: Apr 24, 2020, 10:02 PM