CODEWITHSUNDEEP
sqlsql-servert-sql
David Belovezcik
David Belovezcik

Reputation: 29

Select replace empty values with values from different date

I am using SQL Server and I have a simple query

SELECT TOP 10 * 
FROM Rates 
WHERE Date = '2017-06-09 00:00:00'

for that date there are no rates, if there are no rates the query should give me results from 2017-06-08 00:00:00 Sample Data:

dtmDate int     CurrencyCode    strCurrency dblInRate   dblCCRate 
2012-05-16 00:00:00 12           DZD       0.010545     0.010545 
2012-05-11 00:00:00 12           DZD       0.010511     0.010511
2006-06-26 00:00:00 12           DZD       0.011334     0.011334 
2016-03-30 00:00:00 12           DZD       0.008309     0.008309 
2017-04-26 00:00:00 12           DZD       0.008530     0.008530 
2017-04-28 00:00:00 12           DZD       0.008561     0.008561 
2017-05-03 00:00:00 12           DZD       0.008530     0.008530 
2017-10-13 00:00:00 12           DZD       0.007587     0.007587 
2017-10-19 00:00:00 12           DZD       0.007581     0.007581

--and for 2018-06-09 there is nothing no record in the table and I need to replace it with previous date.

So how do I get it to select all rates for all dates when the date is 2017-06-09 then use rates from 2017-06-08, else use the rates for the corresponding date?

Upvotes: 1

Views: 129

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Here is one method:

SELECT TOP 10 r.* 
FROM (SELECT TOP (1) WITH TIES r.*
      FROM Rates r
      WHERE Date <= '2017-06-09'
      ORDER BY Date DESC
     ) r;

The TOP (1) WITH TIES returns all records with the same date (assuming there is no time component, which is consistent with your sample data and question). The WHERE says the date is no later than 2017-06-09. The ORDER BY says that it is the most recent date on or before the specified date.

The outer TOP (10) chooses 10 of these arbitrarily, as does your query.

Upvotes: 1

Mohsen
Mohsen

Reputation: 21

you can cast your specific column to 'date' and compare it with the filter. just like that:

SELECT TOP 10 * 
FROM Rates 
WHERE cast(Date as date)= '2017-06-09'

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

SELECT TOP 10 * 
FROM Rates 
WHERE Date <= '2017-06-09 00:00:00'
ORDER BY Date DESC

Upvotes: 0

Related Questions