Postgres - Inject 0 values when rows do not exist

Question

I have 3 tables that I need for reporting:

 *dates* 
date_sk | full_date | day_number_of_month 
1 | 2013年01月01日 | 1 
2 | 2013年02月01日 | 1 
3 | 2013年03月01日 | 1
4 | 2013年02月02日 | 2
5 | 2013年02月03日 | 3
 *person* 
person_sk | person_id | person_name 
1 | 10 | John 
2 | 11 | Bob 
3 | 12 | Jill 
 *person_portfolio* 
person_portfolio_sk | date_sk | person_sk | res_value | report_month
1 | 1 | 1 | 15 | 2013年01月01日
2 | 1 | 2 | 10 | 2013年01月01日
3 | 1 | 3 | 1 | 2013年01月01日
4 | 2 | 1 | 30 | 2013年02月01日

(imagine the 'dates' table filled with every date for the past 10 and next 10 years which includes every day of every month. My reporting is for a monthly level which is why i need day_number_of_month = 1 for the first of every month)

I have been struggling to find out, for comparison reporting purposes using a date range, how to replace no entries during that timeframe with 0 values for the person. Here is the query I have tried:

SELECT
 p.person_id,
 COALESCE(pp.res_value,0)::NUMERIC(16,2) AS res_value,
 pp.report_month
FROM person p
LEFT JOIN person_portfolio pp
ON p.person_sk = pp.person_sk
LEFT JOIN date d
ON d.date_sk = pp.date_sk
WHERE person_id IN ('10','11','12')
AND pp.report_month >= '2013-01-01' --From Date
AND pp.report_month <= '2013-05-01' -- To Date
AND d.day_number_of_month = 1
ORDER BY p.person_id DESC;

The output I want to return would end up being 15 rows total. 3 people x 5 months of data = 15 total rows. It should look like this:

person_id | res_value | report_month
10 | 15 | 2013年01月01日
10 | 30 | 2013年02月01日
10 | 0 | 2013年03月01日
10 | 0 | 2013年04月01日
10 | 0 | 2013年05月01日
11 | 10 | 2013年01月01日
11 | 0 | 2013年02月01日
11 | 0 | 2013年03月01日
11 | 0 | 2013年04月01日
11 | 0 | 2013年05月01日
12 | 1 | 2013年01月01日
12 | 0 | 2013年02月01日
12 | 0 | 2013年03月01日
12 | 0 | 2013年04月01日
12 | 0 | 2013年05月01日

but I am only getting these results:

person_id | res_value | report_month
10 | 15 | 2013年01月01日
10 | 30 | 2013年02月01日
11 | 10 | 2013年01月01日
12 | 1 | 2013年01月01日

So basically... is there currently a feasible way that I could inject the 0 value rows into the results when there is no entry for the 'report_month' for a specific person(s)? I would appreciate any kind of help as I have been working on this for 2 weeks now trying to complete this report. Thanks!

Answer 1

You need to create a pseudo date range and join to it... Like such:

select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date;

Once you have your series, you can join to it and use COALESCE to fill in 0's for where there is no data..

WITH days AS (
 select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
 d.day, COALESCE(t1.col1, 0) AS value
FROM days d
LEFT JOIN <something with a date> t1 on d.day = t1.day

If you need to join to something that DOESN'T have a date (for example, your "person" table above), then just join with the condition being "TRUE" (to make a cartesian join, so every person gets every day)

WITH days AS (
 select generate_series('2014-01-01'::timestamp, '2014-10-01'::timestamp, '1 month'::interval)::date AS day
) SELECT
 d.day, COALESCE(t1.col1, 0) AS value
FROM days d
JOIN person p ON TRUE

• Lennart - Slava Ukraini: In the last example, you can use an explicit CROSS JOIN instead of JOIN ON TRUE. Also, there is no need for COALESCE, and t1.col1 should be replaced with p.<whatever>.