Question 20302 in Mysql Question posted in
The official MySQL documentation can be found here.

Get a Value from a column based on Effective Date in another column – Mysql

Table 1

|Part| Date         | Price   |
|A   | 1-May-2021   |10       |
|B   | 1-June-2021  |20       |

Table 2

|Part| Date         | Quantity |
|A   | 15-May-2021  |4       |
|B   | 25-June-2021 |5       |

Need Output as below

|Part| Date         | Quantity | Price   | Amount  
|A   | 15-May-2021  |4         | 10      | 40   
|B   | 25-June-2021 |5         | 20      | 100

So on the effective date of 15-May-21, the price was 10 for Part A , so it should bring me the effective rate and multiply with Qty

Tags:

2

Answers


  1. 0

    Assuming the Date columns are actual sane date types and not insane/broken varchar types:

    SELECT t.Part, t.Date, t.Quantity, t.Price, t.Price*t.Quantity As Amount
FROM (
    SELECT t2.*, t1.Price, row_number() over (PARTITION BY t2.Part ORDER BY t1.Date) rn
    FROM Table2 t2
    INNER JOIN Table1 t1 ON t1.Part = t2.Part and t2.Date <= t1.Date
) t
WHERE t.rn = 1
    Login or Signup to reply.
  2. 0

    Is the Date column really using that date format? If so, then here’s a couple of suggestion:

    1. You can JOIN them by using SUBSTRING_INDEX() to get the month & year from Date column:
    SELECT t1.Part, t2.Date, t2.Quantity, t1.Price, t2.Quantity*t1.Price AS "Amount"
  FROM Table1 t1
  LEFT JOIN Table2 t2
  ON t1.Part=t2.Part
   AND SUBSTRING_INDEX(t1.date,'-',-2)=  SUBSTRING_INDEX(t2.date,'-',-2)
    1. OR you can JOIN them by using EXTRACT to extract the year & month from a converted Date column. Using STR_TO_DATE, you can convert the Date to MySQL standard date format provided that the date value is consistent with the DATE_FORMAT specifier:
     SELECT t1.Part, t2.Date, t2.Quantity, t1.Price, t2.Quantity*t1.Price AS "Amount"
  FROM Table1 t1
  LEFT JOIN Table2 t2
  ON t1.Part=t2.Part
   AND EXTRACT(YEAR_MONTH FROM STR_TO_DATE(t1.date,'%e-%M-%Y'))
      =EXTRACT(YEAR_MONTH FROM STR_TO_DATE(t2.date,'%e-%M-%Y'))

    I think that those two options above are effective in most cases. However, I personally would prefer using the proper MySQL standard date datatype instead. In that case, I’d add another date column and use that for the date function:

    ALTER TABLE Table1 ADD COLUMN mDate DATE;
ALTER TABLE Table2 ADD COLUMN mDate DATE;

UPDATE Table1 SET mDate=STR_TO_DATE(date,'%e-%M-%Y');
UPDATE Table2 SET mDate=STR_TO_DATE(date,'%e-%M-%Y');

SELECT * FROM Table1;
SELECT * FROM Table2;

SELECT t1.Part, t2.Date, t2.Quantity, t1.Price, t2.Quantity*t1.Price AS "Amount"
  FROM Table1 t1
  LEFT JOIN Table2 t2
  ON t1.Part=t2.Part
   AND EXTRACT(YEAR_MONTH FROM t1.mDate)=EXTRACT(YEAR_MONTH FROM t2.mDate);

    Demo fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search