Generate data between two range date by some values

Question

I have 2 dates, StartDate and EndDate:

Declare @StartDate date='2018/01/01', @Enddate date ='2018/12/31'

Then there is some data with a date and value in a mytable table:

----------------------------
 ID    date          value
----------------------------
  1  2018/02/14      4
  2  2018/09/26      7
  3  2017/09/20      2

data maybe start before 2018 and if it exist before @startdate get before values else get 0 I'm looking to get a result that looks like this:

-----------------------------------
fromdate      todate       value
-----------------------------------
2018/01/01    2018/02/13     2
2018/02/14    2018/09/25     4
2018/09/26    2018/12/31     7

The first fromdate comes from @StartDate and the last todate is from @Enddate, and the other data should be generated.

I'm hoping to get this in an SQL query. I use sql-server 2016

Answer 1

You could use a CTE to create your full range of dates, and then LEAD to create the ToDate column:

DECLARE @FromDate date = '20180101',
        @ToDate date = '20181231';


WITH VTE AS(
    SELECT ID,
           CONVERT(date,[date]) [date], --This is why using keywords for column names is a bad idea
           [value]
    FROM (VALUES(1,'20180214',4),
                (2,'20180926',7),
                (3,'20170314',4))V(ID,[date],[value])),
Dates AS(
    SELECT [date]
    FROM VTE V
    WHERE V.[date] BETWEEN @FromDate and @ToDate
    UNION ALL
    SELECT [date]
    FROM (VALUES(@FromDate))V([date]))
SELECT D.[date] AS FromDate,
       LEAD(DATEADD(DAY, -1,D.[date]),1,@ToDate) OVER (ORDER BY D.[date]) AS ToDate,
       ISNULL(V.[value],0) AS [value]
FROM Dates D
     LEFT JOIN VTE V ON D.[date] = V.[date];

db<>fiddle