Sample Header Ad - 728x90

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