CODEWITHSUNDEEP
mysqldatetime
Tone
Tone

Reputation: 783

datetime filter mySQL

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

Upvotes: 0

Views: 108

Answers (2)

Vincent Charette
Vincent Charette

Reputation: 136

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

Upvotes: 1

Cody Adams
Cody Adams

Reputation: 81

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.

Upvotes: 0

Related Questions