How to change the date_add interval type dependant of a field in the table?

Question

Imagine the following table (it has other rows but the ones shown below being used in the query),

+-------------------+-----------------+---------------------+ 
| INTERVAL_QUANTITY | INTERVAL_UOM_ID | LAST_UPDATED_STAMP  |
+-------------------+-----------------+---------------------+ 
| 10                | TF_wk           | 2017-10-31 17:21:40 |
| 20                | TF_mon          | 2017-10-30 17:21:40 | 
| 30                | TF_day          | 2017-10-29 17:21:40 |
+-------------------+-----------------+---------------------+

Key

TF_wk => WEEK
TF_mon => MONTH
TF_day => DAY

Now, I have the following query,

SELECT
    *
FROM
    product_maint 
WHERE
    ( INTERVAL_UOM_ID = 'TF_day' OR INTERVAL_UOM_ID = 'TF_wk' OR INTERVAL_UOM_ID = 'TF_mon' ) 
    AND DATEDIFF( DATE( DATE_ADD( LAST_UPDATED_STAMP, INTERVAL INTERVAL_QUANTITY DAY ) ), DATE( NOW( ) ) ) <= 0;

The issue is, when it selects a row which has a INTERVAL_UOM_ID of TF_wk and INTERVAL_QUANTITY of 10 it actually needs to add on 70 days not 10 days, likewise when the INTERVAL_UOM_ID is TF_mon and the INTERVAL_QUANTITY is 20 it needs to add on 560 days (assuming all my math is correct).

How would I go about implementing this logic?

Gordon Linoff · Accepted Answer · 2017-11-01T11:07:13.080

1

You need to check each interval separately. I think you want:

WHERE ( INTERVAL_UOM_ID = 'TF_day' AND
        LAST_UPDATED_STAMP > CURDATE() - INTERVAL INTERVAL_QUANTITY DAY 
      ) OR
      ( INTERVAL_UOM_ID = 'TF_wk' AND 
        LAST_UPDATED_STAMP > CURDATE() - INTERVAL INTERVAL_QUANTITY WEEK 
      ) OR
      ( INTERVAL_UOM_ID = 'TF_mon' AND
        LAST_UPDATED_STAMP > CURDATE() - INTERVAL INTERVAL_QUANTITY MONTH 
      )

Note that I changed the NOW() to CURDATE(). I don't think the time component is important for what you want to do. Also note that I changed the logic to work only with date arithmetic.

edited Nov 01 '17 at 11:07

answered Nov 01 '17 at 11:05

Gordon Linoff

1,242,037
58
646
786

It seems for some reason using the above query, it shows all the rows, the logic is broken somewhere, any ideas? – Script47 Nov 01 '17 at 11:17
Using your logic of combining the `TF_*` check with the DATE check worked for me when I used my comparison method. So basically: `SELECT * FROM product_maint WHERE (INTERVAL_UOM_ID = 'TF_day' AND DATEDIFF ( DATE( DATE_ADD( LAST_UPDATED_STAMP, INTERVAL INTERVAL_QUANTITY DAY)), DATE( NOW( ))) <= 0 ) OR ( INTERVAL_UOM_ID = 'TF_wk' AND DATEDIFF ( DATE_ADD(DATE( LAST_UPDATED_STAMP), INTERVAL INTERVAL_QUANTITY WEEK), DATE( NOW( ))) <= 0 ) OR (INTERVAL_UOM_ID = 'TF_mon' AND DATEDIFF ( DATE( DATE_ADD( LAST_UPDATED_STAMP, INTERVAL INTERVAL_QUANTITY MONTH)), DATE( NOW( ))) <= 0 );` – Script47 Nov 01 '17 at 11:25

How to change the date_add interval type dependant of a field in the table?

1 Answers1