0

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!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.