Select replace empty values with values from different date

Question

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?

If there is no Data for Jun-8 it should bring the data from the 7th? — Amir Pelled, Jul 02 '18 at 13:45
Would a simple `WHERE Date <= '2017-06-09 00:00:00' ORDER BY Date DESC` do? If not, what are the exact rules for picking a previous day? If there's no data for 2017-06-08 either, what then? — Jeroen Mostert, Jul 02 '18 at 13:45
You can use [date] <= ... order by [date] desc. (I suggest you to use a date literal without any dashes. ie: '20170609' - otherwise you would get erroneous results with some versions and settings). — Cetin Basoz, Jul 02 '18 at 13:45
Do u have that specific requirement for those two dates or you will need to select data from date-1 — Ven, Jul 02 '18 at 13:48
No no, I need to select all rates, data, from all days, but for the 9.6.2017 there are no rates(data) so in such case the query should replace it with data from previous day, but also give me results from 5.6, 6.6, 10.6 11.6 etc etc. — David Belovezcik, Jul 02 '18 at 13:53
Could you add some sample data and expected result for each day? Also, if you're querying for each day separately, then you already should have the data for the 8th if there is no data on the 9th. Simply post it again in the display. No need to query the data over and over. — Amir Pelled, Jul 02 '18 at 13:56
Here is the sample: dtmDate intCurrencyCode 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. — David Belovezcik, Jul 02 '18 at 13:59
If you are selecting data from All dates , you will still only get top 10 rows as you need. This doesn't make any sense — Ven, Jul 02 '18 at 13:59
David Include the data and expected output on the question not at the comments You can format your data here https://stackoverflow.com/questions/3006431/how-to-display-table-data-more-clearly-in-oracle-sqlplus — Juan Carlos Oropeza, Jul 02 '18 at 14:02
If you have 0 rates from day 09, 7 rates from day 08 and 5 rates from day 07 do you get rates from 08 and 07 ? In this case got all 7 from day 08 and random 3 from day 07 ? or just the 7 from day 08? — Juan Carlos Oropeza, Jul 02 '18 at 15:38

Gordon Linoff · Answer 1 · 2018-07-02T14:51:21.977

1

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.

edited Jul 02 '18 at 14:51

answered Jul 02 '18 at 13:54

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, thanks, but it is not this what I need. I need all the data from this table and I know there are no data for this one correct date so I need to select * from rates when date is 9.6.2018 then use rates from date 8.6.2017 – David Belovezcik Jul 02 '18 at 13:57
@DavidBelovezcik . . . That is what this query does. – Gordon Linoff Jul 02 '18 at 14:33
This is clever solution, as I understand it inner selection will return all rates for single day (`top 1 with ties`) and the order guarantees you will get the newest available before or for given date. – Rafal Jul 02 '18 at 14:42

score 0 · Answer 2 · answered Jul 02 '18 at 14:05

0

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

answered Jul 02 '18 at 14:05

Juan Carlos Oropeza

47,252
12
78
118

score 0 · Answer 3 · answered Jul 02 '18 at 14:21

0

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'

answered Jul 02 '18 at 14:21

Mohsen

21
4

Select replace empty values with values from different date

3 Answers3