CODEWITHSUNDEEP
sqlt-sql
Dragos D
Dragos D

Reputation: 69

T-SQL; repreat one value for a time period by day from a table

I'm having this scenario:

One table with entry_time (datetime) and value (float). I retrive timestamps at different intervals with same or different values. Like this:

date_time   value
2012-12-08  77252,8046875
2012-12-08  77252,8046875
2012-12-22  77413,2734375
2012-12-24  0
2012-12-24  0
2012-12-24  0
2012-12-24  0
2012-12-24  68549,4453125
2012-12-24  0
2012-12-24  79194,703125
2012-12-26  0
2012-12-26  77252,8046875
2013-01-05  0
2013-01-05  0
2013-01-26  63780
2013-02-08  10362,27734375
2013-02-16  0
2013-03-04  76400
2013-03-20  0
2013-03-20  76400

What I need is to repeat the first value from the first date by day range untill the next value (even if is the same). When there are multiple values per day, the max value to be selected. After the first repeat, when it's reached the second date, the process repeats with the second value untill the third date and so on.

Something like this:

date_time   value
2012-12-22  77413,2734375
2012-12-23  77413,2734375
2012-12-24  79194,703125
2012-12-25  79194,703125
2012-12-26  77252,8046875
2012-12-27  77252,8046875
2012-12-28  77252,8046875
.............................
2013-01-05  0
2013-01-06  0
.............................
2013-01-25  0
2013-01-26  63780
.............................

Thank you in advance for your answers!

Dragos.

Upvotes: 2

Views: 135

Answers (1)

valex
valex

Reputation: 24144

SQLFiddle demo

with t_max_min 
as 
(select min(date_time) min_dt, 
        max(date_time) max_dt  
        from t)

, period as
(
   select min_dt dt from t_max_min
   union all
   select dt+1 from period 
      where dt<(select max_dt from t_max_min)

)
,t_values as
(
  select date_time, max(value) value 
      from t group by date_time
)

select dt as date_time,
       ISNULL((select TOP 1 value 
                     from t_values
                     where date_time<=dt

                     order by date_time desc)
              , 0) value
    from period
order by dt

Upvotes: 4

Related Questions