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

What SQL server version do you use? Window functions will come in handy. — George Menoutis
– George Menoutis, Commented Apr 10, 2019 at 8:18
What have you tried so far? This looks like a gaps and islands question. — Thom A
– Thom A ♦, Commented Apr 10, 2019 at 8:18

Thom A · Accepted Answer · 2019-04-10 08:59:07Z

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

edited Apr 10, 2019 at 8:59

answered Apr 10, 2019 at 8:46

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alavi Over a year ago

this will wrong result when user add values before 2018 like (1,'20170314',4) this will cause wrong result

Thom A Over a year ago

This is why full sample data in important @Alavi ; none of your data has a value with a value from 2017. If you have data from 2017, or other years, you should be including samples in your data. I've added a further clause to the ON which should work, but without full sample data, it's not tested.

Thom A Over a year ago

OK, now tested by adding more representative data, @Alavi. Please remember, when asking questions in the future, to provide data that represents the data you have, not the answer.

Alavi Over a year ago

Thanks a lot, that is cool just a little wrong in first data (01/01/2018 00:00:00 13/02/2018 00:00:00 0) value of first data is 0 but should be 4 because of this (3,'20170314',4) old data value before @FromDate is 4

Thom A Over a year ago

@Alavi your first row in your expected results has a value of 0 in your question. Why is 0 wrong, is that;s what you have in your question. You need to fix your question before anyone can begin to help you any more.

Dale K · Accepted Answer · 2019-04-23 07:21:28Z

1

with cte as 
(
    select 0 as row_num, @StartDate as start_date, 0 as val
    UNION
    select ROW_NUMBER() OVER(ORDER BY start_date) as row_num, * from input
)
select curr.start_date 
  , DATEADD(day,-1,ISNULL(nex.start_date,DATEADD(day,1,@Enddate))) as end_date 
  , curr.val 
from cte curr
left join cte nex on curr.row_num = nex.row_num - 1;

You can find the simulation here: https://rextester.com/EIAXW23839

edited Apr 23, 2019 at 7:21

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Apr 10, 2019 at 8:31

sabhari karthik

1,3818 silver badges18 bronze badges

5 Comments

Thom A Over a year ago

Shame that this answer was too downvoted without comment; especially when the OP has taken to time to show the example works.

Alavi Over a year ago

this will genarate wrong result if our data start before 2018 like this : insert into input values ('2017-02-14',4),('2018-09-26',7);

Thom A Over a year ago

@Alavi my point in my below answer stands. if you don't provide us data from 2017, how do we know that it's there? We can't see your data.

Alavi Over a year ago

data will create by users in application life time , maybe they define before date, i mean if there is no data before the value must be 0 and if its exist get value

sabhari karthik Over a year ago

would like to know why i am getting down votes here. Doesn't the start and end dates mean the data will be lying in between? is my understanding wrong here?

Collectives™ on Stack Overflow

Generate data between two range date by some values

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related