Select rows where the value is equal to or lower than the specified value and closer to it.

Question

Sorry for the wrong name. Please tell me if this is possible through a db request. Suppose we have the 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 a given date, and if there is no ind_id for a given date, then take the nearest lower date, if there are no lower dates, and then return ind_id + name (name / ind_id pairs are equal ) with zeros. For example, the date 2010-01-04, I expect the 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 possible, I will be very grateful if someone will help me in creating the request. I am using SQL Server 2008.

Answer 1

Mark 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) 

Answer 2

(Updated) Try:

 with cte as (select m.*, max(date) over (partition by ind_id) max_date, max(case when date <= @date then date end) over (partition by ind_id) max_acc_date from myTable m) select ind_id, name, case when max_acc_date is null then null else value end value, max_acc_date date from cte c where date = coalesce(max_acc_date, max_date) 

(SQLFiddle here )

Answer 3

You can use something like:

 declare @date date = '2010-01-04' ;with ids as ( select distinct ind_id from myTable ) ,ranks as ( select * , ranking = row_number() over (partition by ind_id order by date desc) from myTable where date <= @date ) select ids.ind_id , ranks.value , ranks.date from ids left join ranks on ids.ind_id = ranks.ind_id and ranks.ranking = 1 

SQL Fiddle with a demo .

Ideally, you will not use the DISTINCT operator to include ind_id values, but I used it in this case to get the results you need.

In addition, the standard clause for these types of requests; if you have duplicate data, you should consider the tie-break column in ORDER BY or use RANK instead of ROW_NUMBER .

Edited after updating OPs

Just add a new column to your existing query:

 with ids as ( select distinct ind_id, name from myTable ) ,ranks as ( select * , ranking = row_number() over (partition by ind_id order by date desc) from myTable where date <= @date ) select ids.ind_id , ids.name , ranks.value , ranks.date from ids left join ranks on ids.ind_id = ranks.ind_id and ranks.ranking = 1 

SQL Fiddle with a demo .

As in the previous one, it would be better to get the ind_id / name by attaching to the persistent data table, if available.

Answer 4

Here is a query that returns the result you are looking for:

 SELECT t1.ind_id , CASE WHEN t1.date <= '2010-01-04' THEN t1.value ELSE null END FROM test t1 WHERE t1.date=COALESCE( (SELECT MAX(DATE) FROM test t2 WHERE t2.ind_id=t1.ind_id AND t2.date <= '2010-01-04') , t1.date) 

The idea is to select a row in a correlated query so that its identifier matches the identifier of the current row and the date is the highest before your target date is '2010-01-04' .

If such a row does not exist, the date for the current row is returned. This date should be replaced with null ; this is what the CASE statement at the top does.

Here is the daemon on sqlfiddle .

Answer 5

Use the option EXISTS statement

 DECLARE @date date = '20100104' SELECT ind_id, CASE WHEN date <= @date THEN value END AS value, CASE WHEN date <= @date THEN date END AS date FROM dbo.test57 t WHERE EXISTS ( SELECT 1 FROM dbo.test57 t2 WHERE t.ind_id = t2.ind_id AND t2.date <= @date HAVING ISNULL(MAX(t2.date), t.date) = t.date ) 

SQLFiddle Demo

Answer 6

Try

 DECLARE @date DATETIME; SET @date = '2010-01-04'; WITH temp1 AS ( SELECT t.ind_id , t.name , CASE WHEN t.date <= @date THEN t.value ELSE NULL END AS value , CASE WHEN t.date <= @date THEN t.date ELSE NULL END AS date FROM test1 AS t ), temp AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ind_id ORDER BY t.date DESC) AS rn FROM temp1 AS t WHERE t.date <= @date OR t.date IS NULL ) SELECT * FROM temp AS t WHERE rn = 1 

Answer 7

This is not an exact answer, but it will give you a concept, as I just write it down without any testing.

 use go if (Select value from table where col=@col1 ) is not null --you code to get the match value else if (Select LOWER(Date) from table ) is not null -- your query to get the nerst dtae record else --you query withh null value end