vendredi 30 janvier 2015

Postgres - Inject 0 values when rows do not exist


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!





Aucun commentaire:

Enregistrer un commentaire