Select replace empty values with values from different date
Multi tool use
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?
2017-06-08
If there is no Data for Jun-8 it should bring the data from the 7th?
– Amir Pelled
Jul 2 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 2 at 13:45
WHERE Date <= '2017-06-09 00:00:00' ORDER BY Date DESC
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 2 at 13:56
David Include the data and expected output on the question not at the comments You can format your data here stackoverflow.com/questions/3006431/…
– Juan Carlos Oropeza
Jul 2 at 14:02
3 Answers
3
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.
TOP (1) WITH TIES
WHERE
ORDER BY
The outer TOP (10) chooses 10 of these arbitrarily, as does your query.
TOP (10)
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 2 at 13:57
@DavidBelovezcik . . . That is what this query does.
– Gordon Linoff
Jul 2 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 2 at 14:42
top 1 with ties
SELECT TOP 10 *
FROM Rates
WHERE Date <= '2017-06-09 00:00:00'
ORDER BY Date DESC
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'
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
What happen if not data for
2017-06-08??– Juan Carlos Oropeza
Jul 2 at 13:44