There is something like this table:

Date | Value ------------------------- 01/11/2018 | 12 02/11/2018 | 14 03/11/2018 | 11 04/11/2018 | 11 05/11/2018 | 177 07/11/2018 | 13

It is necessary to take the last three days from 07.11, if any day is not in this range, then insert the value on this date 9999. In the example above, there is no 06/11. The result should be:

 Date | Value ------------------------- 05/11/2018 | 177 06/11/2018 | 9999 07/11/2018 | 13

I can't get the missing date with a fixed value.

    1 answer 1

    For clarity, I create a table with one column, since the task comes down to finding a space.

     CREATE TABLE #Datelist (DateN Datetime) INSERT INTO #Datelist VALUES ('2018-01-01'),('2018-01-03'),('2018-01-08'),('2018-01-14'); WITH C AS ( SELECT DateN AS Cur, LEAD(DateN) OVER (ORDER BY DateN) as Nxt FROM #Datelist ), C2 AS ( SELECT DATEADD(DAY,1,cur) as [start],DATEADD(DAY,-1,Nxt) as [end] FROM C WHERE DATEDIFF(DAY,Cur,Nxt) >1 ), C3 AS ( SELECT [start],[end] FROM C2 UNION ALL SELECT [start]+1,[end] FROM C3 WHERE [start]<[end] ) SELECT [start] FROM C3 ORDER BY [start] DROP TABLE #Datelist

    In the presented code, replace the table name and field names. The resulting missing value is inserted with the required attribute value in the target table.

    PS Problem solution for MS SQL version> = 2012 is presented

    Read more about: LEAD

    UPD Now the list of dates from the beginning of the range to its end is displayed, everything works even if there are several ranges.

    • So there is the beginning of the space (s), i.e. date 2018-01-06 in the data is missing and the request returns it. But if to throw out in the initial data one more neighboring date 2018-01-07 , then its request will not return. - i-one
    • Fair remark, I will correct soon - Nick Proskuryakov
    • @ i-one now everything is as it should be. - Nick Proskuryakov
    • Wise somehow. And then, in the question "take the last three days from 07.11, if a certain day is not in this range, then ... In the example above, there is no 06/11," but what if there is no 07/11 (the last day of the range)? You can simply take a sequence of dates in the desired range and to it with-left-join-iit source data (three days can even be taken in the variables in the values , if of course three is not a parameter). - i-one