I have two tables, which share a key that link the two. Table A has a date column (of the format MM/DD/YYYY), and table B has a date field of the format (YYYY-MM-DD HH:MM:SS).
What I need to do is select all those in table B, that have a key matching table A AND a date field within 30 days of the date field found in table A.
Edit: Both variables are varchars, here is what I currently have (error from using alias formattedEffective in a join). I think the below would work, if I could use aliases in that way.
select *,
DATE_FORMAT(STR_TO_DATE(`Eff_date`, '%m/%d/%Y'), '%Y-%m-%d') as formattedEffective
from `customers`
right join `dispatch` on `customers`.`Member_no` = `dispatch`.`Member_no`
AND `dispatch`.`sortdate` > formattedEffective
AND `dispatch`.`sortdate` < DATE_ADD(formattedEffective,INTERVAL 30 DAY)
What the community is asking for is the ability to create a scenario to give a definitive answer to your question (create table statements, sample data, etc..). The approach below is speculation.
The assumption the query makes is eff_date is a string and sortdate is stored as a MySQL date (i.e., date, datetime, timestamp).
select d.*,
str_to_date(c.eff_date, '%m/%d/%Y') ) as formattedEffective
from customer c
join dispatch d on ( d.member_no = c.member_no
and d.sortdate between str_to_date(c.eff_date, '%m/%d/%Y')
and str_to_date(c.eff_date, '%m/%d/%Y') + interval 30 day );
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments