Select rows where value is equal given value or lower and nearest to it

database, sql, sql-server-2008

Solution

Check this SQL FIDDLE DEMO

with CTE_test
as 
(
    select int_id,
       max(date) MaxDate
    from test 
    where date<='2010-01-04 00:00:00:000'
    group by int_id
)
select A.int_id, A.[Value], A.[Date]
from test A
    inner join CTE_test B
       on a.int_id=b.int_id 
          and a.date = b.Maxdate
union all
select int_id, null, null 
from test 
where int_id not in (select int_id from CTE_test)

Problem

Sorry for confusing title. Please, tell, if it's possible to do via db request. Assume we have following table ``` ind_id name value date ----------- -------------------- ----------- ---------- 1 a 10 2010-01-01 1 a 20 2010-01-02 1 a 30 2010-01-03 2 b 10 2010-01-01 2 b 20 2010-01-02 2 b 30 2010-01-03 2 b 40 2010-01-04 3 c 10 2010-01-01 3 c 20 2010-01-02 3 c 30 2010-01-03 3 c 40 2010-01-04 3 c 50 2010-01-05 4 d 10 2010-01-05 ``` I need to query all rows to include each `ind_id` once for the given date, and if there's no `ind_id` for given date, then take the nearest lower date, if there's no any lower dates, then return ind_id + name (name/ind_id pairs are equal) with nulls. For example, date is 2010-01-04, I expect following result: ``` ind_id name value date ----------- -------------------- ----------- ---------- 1 a 30 2010-01-03 2 b 40 2010-01-04 3 c 40 2010-01-04 4 d NULL NULL ``` If it's possible, I'll be very grateful if someone help me with building query. I'm using SQL server 2008.

Original source