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

Navigation
1

Friends, good afternoon!

I came across a problem at work today, and I have not yet got a solution for it. As I do not have much experience in the database area I am having difficulty solving this problem, I would be very happy with a help, hint etc ...

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

I need that when there is no next date repeat the previous one 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
    
asked by anonymous 12.09.2017 / 22:17

2 answers

1

Use subquery matching ORDER BY for the date you want 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 in SQL Fiddle .

    
12.09.2017 / 22:45
2

Simply select the largest date, and group it by the other columns. I gave an example: 

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

and put it in SQLFiddle to see it working: link

    
12.09.2017 / 22:49
Struct presenting problem .append () an img tag?