0

I have table like this

ID             effective_date                 status
1              2014-12-01                     1
2              2016-11-18                     0 
3              2016-11-25                     1 
4              2017-04-21                     0
5              2017-05-20                     1

when status 1 = active and 0 = inactive and i have a date-search '2016-12-30'

how i can get result a date_effective with active status nearest that date-search? actually the result is 

ID             effective_date                 status
3              2016-11-25                     1

thank you for your answer

Drajad
  • 53
  • 7
  • @SebastianBrosch not like this, this post only check last periode, how to check before and after periode too? – Drajad Sep 06 '17 at 08:28
  • The first solution (42 upvotes) of the linked answer is the solution. You have to edit this a little bit (hint `LIMIT`) but it should work. – Sebastian Brosch Sep 06 '17 at 08:37
  • OK thank you for all of your solution and information. i got the point now – Drajad Sep 06 '17 at 08:39

2 Answers2

0

No need to get too complicated:

SELECT * FROM
  table
WHERE
  effective_date < str_to_date('20161230', '%Y%m%d') AND
  status = 1
ORDER BY 
  effective_date DESC
LIMIT 1

Edit: I'm not sure why Giorgios Betaos deleted his post, as it seems to have been the correct answer based on the new info supplied:

SELECT * FROM
  table
WHERE
  status = 1
ORDER BY 
  ABS(effective_date - str_to_date('20161230', '%Y%m%d')) ASC
LIMIT 1

We get the absolute difference between the date supplied in the search and all the dates in the table, and order by it ascending. The one with the smallest difference is then the nearest date to what is being searched

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • its only check last period, how about after period? i mean i need to check before and after period too – Drajad Sep 06 '17 at 08:30
  • Edited accordingly.. Credit should go to Giorgios Betaos though, who first proposed this solution. Giorgios, maybe undelete your post? – Caius Jard Sep 06 '17 at 08:33
0

Order by date difference and keep the first record:

select *
from mytable
where status = 1
order by abs(datediff(effective_date, date '2016-12-30'))
limit 1;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73