Selecting rows that are within a date range based on a date from another table. (MYSQL)

Bryant

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)
RMathis

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.

edited at
0

Comments

0 comments
Login to comment

Related

SQL - Selecting rows within a date range?

Count the number of rows where date range is within another date range

How to count rows from a different table based on same date range

How to total quantity based on date range from another table?

In Excel, how can I filter, pull, and sum values from a column if their rows match data in another table and are within a specific date range?

select rows from main table based on highest date in child table between a date range

Filter rows of a table based on a condition that implies: 1) value of a field within a range 2) id of the business and 3) date?

MySQL Selecting Dynamic Date Range

MYSQL: Transfer values from one table to another based on date/hour

Pandas get a value from one table based on date range in another table

Update 3rd table based on date range from another table

Conditionally append rows from one dataframe to another based on key-id and date range

Copy Rows to Another Sheet Based on Date Range Multiple Times

Distinct rows based on date range

Merge rows based on date range

Calculating a sum based on the date from another table

Find date range overlaps within the same table, for specific user MySQL

Import data from one workbook to another within a date range

How do I conditionally highlight dates within a start and end date range based on individual date rows?

SQL - Selecting rows based on date difference

sql - conditional query based on column on another table (date range)

MYSQL - error while selecting date range

Retrieving rows from Pandas DataFrame based on month of a date column in a range

Count rows within date range with condition

Selecting rows of a table from a column of another table

SQL Find Where Date Falls in Table Based on Date Range in Another Table

Selecting Most Recent Date Relative to Another Table

hide table rows based on date

From a table of made up of rows of dates, how to count the number of rows where the date is in a certain date range