datetime filter mySQL

Question

Im needing to combine 2 queries. The second of two queries is used to filter the rows based on the last updated transfer date (Datetime). I'm using a mySQL database and are attempting to use a filter to bring back the correct results.

Query 1:

SELECT DISTINCT 
    F.client_license_ID
    , EM.create_DTM
    , EM.event_ID
    , CEQ.consumer_ID
    , EM.event_mapping_ID em_ID
    , EM.export_value campaign_number
    , EM.export_value_2 sequence_number
    , EM.export_value_3 campaign_number_2
    , EM.export_value_4 sequence_number_2
    , EM.export_value_5 ffs_event_id
    , EM.export_value_6
    , EM.export_value_7
    , EM.export_value_8
    , EM.export_value_9
    , EM.export_value_10
    , F.footprint_ID
    , F.event_token_ID
FROM data_transfer.Mappings EM
JOIN data_transfer.Event_Queue CEQ ON CEQ.event_ID = EM.event_ID
JOIN efn.Footprints F ON CEQ.consumer_ID = F.consumer_ID
JOIN data_transfer.DT_Runs as DR ON DR.data_transfer_ID = EM.data_transfer_ID
LEFT JOIN efn_data_transfer.CRM_Records LCR ON LCR.consumer_ID = CEQ.consumer_ID
WHERE EM.data_transfer_ID = 24
AND EM.mode = 'production'
AND EM.active_flag = 1
AND F.sample_flag = 0
AND LCR.failureCode = 0
AND EM.create_DTM > ?

Query 2:

SELECT CAST(DATE_SUB(start,INTERVAL 3 DAY) AS CHAR) last_transfer
, CAST(DATE_FORMAT(NOW(),"%Y%m%d") AS CHAR) today
, CAST(DATE_FORMAT(NOW(),"%H%i%s") AS CHAR) "Time"
, CAST(DATE_FORMAT(NOW(),"%m%d%Y") AS CHAR) "Date"
, NOW() timeNow
FROM data_transfer.DT_Runs DTR
WHERE DTR.data_transfer_ID = 24
AND DTR.result = 1
AND DTR.mode = 'production'
ORDER BY DTR.dt_run_ID DESC
LIMIT 1;

My attempt was to add a where filter (AND EM.create_DTM >= DR.start() - INTERVAL 3 DAY) for the last transfer date but it does not work as expected. Currently I have an ETL job that processes both queries feeding the "last transfer" variable from query 2 into the ? variable for the where filter. Help is appreciated

Why doesn't the query you have work as expected, what is it doing? — AdamMc331, Jul 14 '15 at 19:50
There is no `START()` function that I know of, I think you are just looking for `DR.start` — AdamMc331, Jul 14 '15 at 19:53
The alias isn't the problem, the problem is that there is no function `START()` as the error message explains. — AdamMc331, Jul 14 '15 at 19:56
the client unfortunately uses START as a column name which is a key word in mySQL. I suppose I could just create a function and plug it in or maybe try max date. — Tone, Jul 14 '15 at 20:01
To work around reserved words as column names, you need back ticks: `dt.'start'`. Since backticks are code formatters in SO comments, imagine my single quotes are back ticks. See more info here: http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql — AdamMc331, Jul 14 '15 at 20:03
I'm familiar with the work around with back ticks but deleting the parenthesis worked. Query is slow but will look at ways to optimize. Thanks. — Tone, Jul 14 '15 at 20:11

score 1 · Accepted Answer · answered Jul 14 '15 at 20:29

You could implement it as one query.

SELECT DISTINCT 
    F.client_license_ID
    , EM.create_DTM
    , EM.event_ID
    , CEQ.consumer_ID
    , EM.event_mapping_ID em_ID
    , EM.export_value campaign_number
    , EM.export_value_2 sequence_number
    , EM.export_value_3 campaign_number_2
    , EM.export_value_4 sequence_number_2
    , EM.export_value_5 ffs_event_id
    , EM.export_value_6
    , EM.export_value_7
    , EM.export_value_8
    , EM.export_value_9
    , EM.export_value_10
    , F.footprint_ID
    , F.event_token_ID
FROM data_transfer.Mappings EM
JOIN data_transfer.Event_Queue CEQ ON CEQ.event_ID = EM.event_ID
JOIN efn.Footprints F ON CEQ.consumer_ID = F.consumer_ID
JOIN data_transfer.DT_Runs as DR ON DR.data_transfer_ID = EM.data_transfer_ID
LEFT JOIN efn_data_transfer.CRM_Records LCR ON LCR.consumer_ID = CEQ.consumer_ID
WHERE EM.data_transfer_ID = 24
AND EM.mode = 'production'
AND EM.active_flag = 1
AND F.sample_flag = 0
AND LCR.failureCode = 0
AND EM.create_DTM > (SELECT last_transfer FROM (SELECT CAST(DATE_SUB(start,INTERVAL 3 DAY) AS CHAR) last_transfer
, CAST(DATE_FORMAT(NOW(),"%Y%m%d") AS CHAR) today
, CAST(DATE_FORMAT(NOW(),"%H%i%s") AS CHAR) "Time"
, CAST(DATE_FORMAT(NOW(),"%m%d%Y") AS CHAR) "Date"
, NOW() timeNow
FROM data_transfer.DT_Runs DTR
WHERE DTR.data_transfer_ID = 24
AND DTR.result = 1
AND DTR.mode = 'production'
ORDER BY DTR.dt_run_ID DESC
LIMIT 1) )

score 0 · Answer 2 · answered Jul 14 '15 at 19:37

0

take a look at datediff: https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff

where datediff(EM.create_DTM, DR.start()) >= {x} // where x is the length of 'days' between the to column values.

answered Jul 14 '15 at 19:37

Cody Adams

61
1
3

DATEDIFF would be a little easier to read than doing the date comparison, but I don't think that's the problem here. – AdamMc331 Jul 14 '15 at 19:58

datetime filter mySQL

2 Answers2