CODEWITHSUNDEEP
sqlselect
Valeriy
Valeriy

Reputation: 805

Select between dates

I have a table tem_data with temperatures values and dates

create table tem_data (id int not null, value float, date datetime);

Only when temperature changed , the new value inserted. For instance 

  t   date
  15 12.09.2013
  17 15.09.2013
  23 19 09.2013

And the second table is a days for report 

create table rDays(rDay datetime);

   rDay

  12.09.2013
  13.09.2013
  14.09.2013
  16.09.2013
  17.09.2013
  18.09.2013
  23.09.2013

All of it's rows should be in report, and as a result I want to have all days from rDays with it's temperature value.

       date              t
      12.09.2013       15
      13.09.2013       15
      14.09.2013       15
      16.09.2013       17
      17.09.2013       17 
      18.09.2013       17
      23.09.2013       19


select rDay,t from tem_data, rDays where ... ? , only ansi sql

Upvotes: 0

Views: 84

Answers (2)

Robert Co
Robert Co

Reputation: 1715

Here you go. Note that your upper bound is a > and not >=. If you really want BETWEEN, you have to subtract one from the end date, which I consider as unnecessary since it would not be displayed.

WITH t AS (
SELECT date as start_date
     , coalesce(lead(date) over (order by date)
              , convert(datetime, '12/31/9999')) as end_date
     , value
  FROM tem_data
)
SELECT rDays.rDay
     , value
  FROM rDays
 INNER JOIN t
    ON t.start_date <= rDay
   AND t.end_date > rDay

Upvotes: 1

Geordee Naliyath
Geordee Naliyath

Reputation: 1859

Assuming that you are using SQL Server.

Please check the following SQL Fiddle for a working example.

http://sqlfiddle.com/#!6/8d306/1

The answer is based on a earlier StackOverflow question on the same lines.

Filling in missing days for rows

with dates (start_date, end_date) as
(select  min(tem_date), max(tem_date)
 from    tem_data t
 union all
 select  dateadd(d, 1, start_date), end_date
 from    dates d
 where   start_date < end_date
)
select start_date, q.tem_value
from dates d
cross apply (select top 1 tem_value
             from tem_data t
             where t.tem_date <= d.start_date
             order by t.tem_date desc
             ) q
option (maxrecursion 0)

Upvotes: 1

Related Questions