CODEWITHSUNDEEP
mysqlsqlsql-serverpostgresql
user1624577
user1624577

Reputation: 577

fill in data and missing dates

I am trying to use SQL to take my current query results:

+------------+------------+------------+
| STORE_NUM  |  PRICE     | DATE_CHNG  |
+------------+------------+------------+
| 100        |  1.50      | 2014-05-01 |
| 100        |  1.52      | 2014-05-03 |
| 100        |  1.48      | 2014-05-05 |
| 100        |  1.51      | 2014-05-10 |
+------------+------------+------------+

And get them to look more like this, where the missing dates are added, but the value of the price is filled in as well: 

+------------+------------+------------+
| STORE_NUM  |  PRICE     | DATE_CHNG  |
+------------+------------+------------+
| 100        |  1.50      | 2014-05-01 |
| 100        |  1.50      | 2014-05-02 |
| 100        |  1.52      | 2014-05-03 |
| 100        |  1.52      | 2014-05-04 |
| 100        |  1.48      | 2014-05-05 |
| 100        |  1.48      | 2014-05-06 |
| 100        |  1.48      | 2014-05-07 |
| 100        |  1.48      | 2014-05-08 |
| 100        |  1.48      | 2014-05-09 |
| 100        |  1.51      | 2014-05-10 |
+------------+------------+------------+

Any help/advice/resources are very much appreciated.

Thanks!

Upvotes: 1

Views: 105

Answers (1)

krokodilko
krokodilko

Reputation: 36127

Try:

SELECT "STORE_NUM", "PRICE", "DATE_CHNG" + x - 1 As "DATE_CHNG"
FROM (
  SELECT *,
       Lead("DATE_CHNG") OVER (Order By "DATE_CHNG" ) - "DATE_CHNG" As number_of_days
  FROM table1
) m,
LATERAL (
  SELECT generate_series( 1, 
                         CASE coalesce(m.number_of_days, 1)
                         WHEN 0 THEN 1
                         ELSE coalesce(m.number_of_days, 1) END) x
) y

Demo ==> http://sqlfiddle.com/#!15/3c5a47/2

This query works on version 9.3 only, earlier versions don't support lateral join

Upvotes: 1

Related Questions