CODEWITHSUNDEEP
sqlpostgresql
CY Hng
CY Hng

Reputation: 9

Is there a SQL function that can populate missing dates in Date column and fill the same record in column B with previous value? (PostgresSQL)

The link article explains and answers my requirement in R. How do I achieve this in PostgreSQL? https://blog.exploratory.io/populating-missing-dates-with-complete-and-fill-functions-in-r-and-exploratory-79f2a321e6b5

In summary:

Image of Initial table

Basically in the table shown, I want to auto create new records of missing dates (range from 10-02 to 10-14).

Table with date created

The problem of this table is, the data of discount rate and product is still missing. The SQL statement should be able to detect, when there is first item in range (for example in discount rate column), the subsequent empty records in the discount rate will be populated with the same value of first item until there is another item found, then repeat the process.

For example, discount rate from 10-02 to 10-14 should be 0.1 (based on previous value on 10-01) and from 10-16 onward should be 0.2 (based on previous value on 10-15). How do I achieve this in SQL if it involves hundreds and thousands of records?

Upvotes: 0

Views: 194

Answers (1)

user330315
user330315

Reputation:

You can left join to a list of dates generated through generate_series() 

select ...
from generate_series(date '2020-10-01', date '2020-10-14', interval '1 day) as g(dt)
  left join your_table t on t."date" = g.dt::date
where ...

Upvotes: 1

Related Questions