CODEWITHSUNDEEP
postgresql
Backstreet Imrul
Backstreet Imrul

Reputation: 100

How can I get the target table in PostgreSQL?

The table look like this

number      day amount
013xxxxxxxx 1   62773
013xxxxxxxx 8   52963
013xxxxxxxx 9   10810
013xxxxxxxx 10  84193
013xxxxxxxx 11  91791
019xxxxxxxx 1   89055
019xxxxxxxx 3   85366
019xxxxxxxx 5   47318

I want the table like this.

number      day amount
013xxxxxxxx 1   62773
013xxxxxxxx 2   0
013xxxxxxxx 3   0
013xxxxxxxx 4   0
013xxxxxxxx 5   0
013xxxxxxxx 6   0
013xxxxxxxx 7   0
013xxxxxxxx 8   52963
013xxxxxxxx 9   10810
013xxxxxxxx 10  84193
013xxxxxxxx 11  91791
019xxxxxxxx 1   89055
019xxxxxxxx 2   0
019xxxxxxxx 3   85366
019xxxxxxxx 4   0
019xxxxxxxx 5   47318

Upvotes: 1

Views: 180

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Using a calendar table approach:

WITH cte AS (
    SELECT number, MIN(day) AS min_day, MAX(day) AS max_day
    FROM yourTable
    GROUP BY number
)

SELECT n.number, s.day, COALESCE(t2.amount, 0) AS amount
FROM (SELECT DISTINCT number FROM yourTable) n
CROSS JOIN (SELECT * FROM generate_series(1, 31)) AS s(day)
INNER JOIN cte 
    ON t.number = n.number
LEFT JOIN yourTable t2
    ON t2.number = n.number AND t2.day = s.day
WHERE s.day BETWEEN t.min_day AND t.max_day
ORDER BY n.number, s.day;

screen capture from demo link below

Demo

Upvotes: 1

Philippe
Philippe

Reputation: 1827

You can write this query (Result here)

with x as (SELECT distinct test.number,generated_day FROM generate_series(1, 31) as generated_day, test),
y as (SELECT distinct test.number, MAX(day) OVER (PARTITION BY number) AS max_day FROM test)
select x.number,x.generated_day,coalesce(t.amount,0)
from x left join test t on t."number" = x.number and t.day = x.generated_day
where x.generated_day <= (SELECT MAX(day) max_day FROM test where test.number = x.number) 
order by 1, 2

Upvotes: 2

Related Questions