Find a record between two dates. If not found, return the entry with the lowest date
I have a table like
Name DateOfBirth
--------------------------------
Ramesh 17/04/1983
Pavan 16/03/1980
Rakesh 18/03/1975
Nadeem 19/05/1974
Kalam 20/08/2973
I am writing an SP whose pseudo code is given below:
I am passing the date to this SP as an input parameter
- If @InputDate is found, SP should return an entry
- If @InputDate is less than the smallest date in the table, then the record with the smallest date should be returned
-
If @InputDate is not found, but it is between the smallest and largest values in the column, then the entry that will fall immediately later should be returned
- Is it possible to write logic in one statement?
- The most streamlined way to accomplish this task.
source to share
I think the query below (in MySQL) provides what you want:
SELECT Name, DateOfBirth
FROM mytable
WHERE DateOfBirth >= @mydate
ORDER BY DateOfBirth LIMIT 1
Case 1 , input:
@mydate = '1972-03-16'
Output:
Name, DateOfBirth
-------------------
Nadeem, 1974-05-19
Case 2 , input:
@mydate = '1980-03-16'
Output:
Name, DateOfBirth
-------------------
Pavan, 1980-03-16
Case 3 , input:
@mydate = '1982-03-16'
Output:
Name, DateOfBirth
-------------------
Ramesh, 1983-04-17
Case 4 , input:
@mydate = '2982-03-16'
Output:
Name, DateOfBirth
-------------------
No records returned
source to share
Yes, you can. First, let's see how you can get records with the minimum and maximum dates of your set:
Min
select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth
Max
select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth desc
This is how you get the corresponding entry :
select top 1 Name, DateOfBirth
from yourtable
where DateOfBirth = @InputDate
Now let's put everything together into a query:
select mymin.Name as myminName, mymin.DateOfBirth as myminDateOfBirth,
mymax.Name as mymaxName, myMax.DateOfBirth as mymaxDateOfBirth,
therecord.Name as therecordName, therecord.DateOfBirth as therecordDateOfBirth
from
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth) mymin
join
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth desc) mymax
on 1 = 1
left join yourtable therecord
on therecord.DateOfBirth = @InputDate
As you can see, we can have select
all possible values. The last step - change the selection to get only Name
and DateOfBirth
the desired records. If there is no match, and the date is not less than the minimum and not more than the maximum, then it is returned null
. To do this, we need to use the syntax case
- when
for example:
select case (therecord.Name is null)
When 1 then (case mymin.DateOfBirth > @InputDate when 1 then mymin.Name
else case mymax.DateOfBirth < @InputDate when 1 then mymax.Name else null end)
Else therecord.Name
End as Name,
case (therecord.Name is null)
When 1 then (case mymin.DateOfBirth > @InputDate when 1 then mymin.DateOfBirth
else case mymax.DateOfBirth < @InputDate when 1 then mymax.DateOfBirth else null end)
Else therecord.DateOfBirth
End as DateOfBirth
from
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth) mymin
join
(select top 1 Name, DateOfBirth
from yourtable
order by DateOfBirth desc) mymax
on 1 = 1
left join yourtable therecord
on therecord.DateOfBirth = @InputDate
I assumed you are using SQL Server.
Warning: none of the code has been tested, if there are any typos please let me know.
source to share