Repeat previous rows when there is no record in SQL SERVER data

Question

Friends, good afternoon!

I came across a problem at work today, and I still haven’t found a solution to it. As I do not have much experience in the database area I am having difficulty to solve this problem, I would be very happy with a help, tip etc...

I have the following problem: I have a table where records are updated daily, but not all Ids have daily updated records.

I need that when one there is no next date repeat the previous example:

CREATE TABLE #CADASTRO ( ID INT IDENTITY, DATA_ATUALIZACAO DATE, VALOR INT)

INSERT INTO #CADASTRO VALUES ('20170201',1), ('20170204',4), ('20170205',5)

DECLARE @DATAINICIAL DATE = '20170201'; 
DECLARE @DATAFINAL DATE = '20170205';

;

WITH CTE AS 
( 

SELECT 

@DATAINICIAL AS DATA_GUIA 

/**********/UNION ALL/**********/ 

SELECT 
    DATEADD(DAY, 1, DATA_GUIA) 
FROM CTE 
WHERE DATA_GUIA < @DATAFINAL


)

SELECT 
    B.DATA_GUIA, 
    A.* 
FROM #CADASTRO AS A
RIGHT JOIN CTE AS B ON A.DATA_ATUALIZACAO = B.DATA_GUIA

Answer 1

just select the highest date, and group by the other columns. I made an example:

select 
    id,
    max(data_atualizacao) as data_atualizacao,
    valor
from CADASTRO
group by id,valor
order by id

and put on Sqlfiddle to see working: http://sqlfiddle.com/#! 6/db1da/2

Answer 2

Use a subquery matching ORDER BY by the date you wish and TOP:

DECLARE @datainicial DATE = '20170201';
DECLARE @datafinal DATE = '20170205';

WITH cte AS (SELECT @datainicial AS data_guia
             UNION ALL
             SELECT DATEADD(DAY, 1, data_guia)
               FROM cte
              WHERE data_guia < @datafinal)
SELECT b.data_guia,
       (SELECT TOP(1) c.valor
          FROM #cadastro c
         WHERE c.data_atualizacao <= b.data_guia
        ORDER BY c.data_atualizacao DESC) AS valor
  FROM cte b;

See working on SQL Fiddle.