Inner join generation series with "with clause" to populate missing dates as null

Question

I have the postgres statement below.


WITH dailyhh as (
    SELECT distinct on(settlement_period_id) t1.estimated_consumption, t1.settlement_date,t1.annualised_consumption,
        rank() OVER (
            PARTITION BY settlement_period_id
                ORDER BY
                    CASE settlement_period_interval_count
                        WHEN 0 THEN 1
                        ELSE 2
                    END
        )
    FROM consumption_db_schema.halfhourlyconsumption t1
    where t1.mprn = '123456789'
    and t1.settlement_time between '2021-01-01' and '2021-12-31'
    )
SELECT DATE_TRUNC('month', settlement_date) as settle,
    sum(estimated_consumption) as sum_estimated,
    sum(annualised_consumption) as sum_annualiseds
FROM dailyhh
GROUP BY DATE_TRUNC('month', settlement_date)
ORDER BY settle ASC;

I need to return null if there is no values in that date period. For instance the provides the table below:

settle timestamp with time zone	sum_estimated numeric	sum_annualiseds numeric
2021-10-01T00:00:00.000Z	null	713.2
2021-11-01T00:00:00.000Z	null	981.0
2021-12-01T00:00:00.000Z	null	992.3

However, I need a table which provides null for the missing dates, i.e

settle timestamp with time zone	sum_estimated numeric	sum_annualiseds numeric
2021-01-01T00:00:00.000Z	null	null
2021-02-01T00:00:00.000Z	null	null
2021-03-01T00:00:00.000Z	null	null
2021-04-01T00:00:00.000Z	null	null
2021-05-01T00:00:00.000Z	null	null
2021-06-01T00:00:00.000Z	null	null
2021-07-01T00:00:00.000Z	null	null
2021-08-01T00:00:00.000Z	null	null
2021-09-01T00:00:00.000Z	null	null
2021-10-01T00:00:00.000Z	null	713.2
2021-11-01T00:00:00.000Z	null	981
2021-12-01T00:00:00.000Z	null	992.3

I have tried using the code from How to return rows with 0 count for missing data?

SELECT *
FROM  (
   SELECT day::date
   FROM   generate_series(timestamp '2007-12-01'
                        , timestamp '2008-12-01'
                        , interval  '1 month') day
   ) d

but how would one inner join with the "with clause" to get the desired table?

I don't see an actual problem statement here. Also, why did you tag with MySQL, given that your very question makes it clear you are using Postgres? — Tim Biegeleisen, Mar 06 '23 at 14:28

score 1 · Accepted Answer · answered Mar 06 '23 at 15:09

generate_series to generate a range of days, then you only have to do is to left join this generated range by the output data coming from dailyhh :

WITH dailyhh as (
    SELECT distinct on(settlement_period_id) t1.estimated_consumption, t1.settlement_date,t1.annualised_consumption,
        rank() OVER (
            PARTITION BY settlement_period_id
                ORDER BY
                    CASE settlement_period_interval_count
                        WHEN 0 THEN 1
                        ELSE 2
                    END
        )
    FROM halfhourlyconsumption t1
    where t1.mprn = '123456789'
    and t1.settlement_time between '2021-01-01' and '2021-12-31'
)
select day as settle, s.sum_estimated, s.sum_annualiseds from (
  SELECT day::date
  FROM generate_series(date '2021-01-01', date '2021-12-31', interval  '1 month') day
) as dayrange
left join (
  SELECT DATE_TRUNC('month', settlement_date) as settle,
    sum(estimated_consumption) as sum_estimated,
    sum(annualised_consumption) as sum_annualiseds
  FROM dailyhh
  GROUP BY DATE_TRUNC('month', settlement_date)
  ORDER BY settle ASC
) as s on dayrange.day = s.settle

why does replacing "month" with week, cause all of the values to be null? — constantlyFlagged, Mar 06 '23 at 19:43
This logic will not work for week intervall, an other logic should be done to make it works. — SelVazi, Mar 06 '23 at 21:37

Inner join generation series with "with clause" to populate missing dates as null

1 Answers1