I have a table that holds date and the corresponding values. I want to write a sql query in a way it-CodePudding

I have a table that holds date and their corresponding values. I want to write a query to fill in the missing dates and fill the values column with the value of the previous date.

DATE_         CURR            VALUE
2020-12-31    Dollar USA      73,8757
2020-12-30    Dollar USA      73,6567
2020-12-29    Dollar USA      73,7175
2020-12-26    Dollar USA      73,6921
2020-12-25    Dollar USA      74,8392
2020-12-24    Dollar USA      75,4571

OUTPUT:

DATE_         CURR            VALUE
2020-12-31    Dollar USA      73,8757
2020-12-30    Dollar USA      73,6567
2020-12-29    Dollar USA      73,7175
2020-12-28    Dollar USA      73,7175
2020-12-27    Dollar USA      73,7175
2020-12-26    Dollar USA      73,6921
2020-12-25    Dollar USA      74,8392
2020-12-24    Dollar USA      75,4571

with d as (
   select 
      min(DATE_) mi, 
      max(DATE_) mx, 
      max(DATE_) - min(DATE_) num 
   from CURR_TRAN), 
d1 as (
   select 
      level-1   mi DATE_ 
   from d CONNECT by level <= num 1) 

select 
   d1.DATE_,
   last_value(VALUE ignore nulls) over (order by d1.DATE_) VALUE 
from d1 
left outer join CURR_TRAN on (d1.DATE_=CURR_TRAN.DATE_) 
order by d1.DATE_;

CodePudding user response：

Below solution if for MySQL (because that is/was the tag below the question at the time of writing this... )

I created a DBFIDDLE which seems to produce the requested result

insert into table1
WITH RECURSIVE cte1 as (
   SELECT min(DATE_) as D FROM table1
   
   union all
   
   SELECT DATE_ADD(D,INTERVAL 1 DAY) 
   from cte1 
   WHERE D<(SELECT max(DATE_) from table1)
)
select D,CURR,VALUE
FROM (
   select 
      cte1.D,
      p.CURR,
      p.VALUE,
      ROW_NUMBER() OVER (PARTITION BY cte1.D ORDER BY p.DATE_ desc) R
   from cte1
   LEFT join table1 p on p.DATE_ < cte1.D
   LEFT JOIN table1 ON table1.DATE_ = cte1.D
   WHERE table1.DATE_ IS NULL
   ORDER BY D) y
WHERE y.R=1;

CodePudding user response：

For SQL Server try something like this:

DECLARE @minDate DATETIME = (SELECT MIN(DATE_) FROM table1)
DECLARE @maxDate DATETIME = (SELECT MAX(DATE_) FROM table1)
DECLARE @currentDate DATETIME = @minDate

WHILE @currentDate < @maxDate
BEGIN
  IF NOT EXISTS (SELECT * FROM table1 WHERE Date_ = @currentDate)
  BEGIN
    INSERT INTO table1
    SELECT TOP(1) @currentDate, CURR, VALUE
    FROM table1
    WHERE DATE_ < @currentDate
    ORDER BY Date_ DESC
  END
  
  SET @currentDate = @currentDate   1
END

If you have multiple currencies in this table than you need to change the IF NOT EXISTS ... and the WHERE clause in the INSERT statement