I am in a odd situation. Following is my query running against the dataset as show below:
Select name from table_name
where (send_yn='Y' or send_yn='N') and (last_chnage_dt = date -1)
and (last_change_dt > sent_system_dt OR sent_system_dt is null)
Users via front end manipulates send_yn (by marking it Y and N) and in doing so last_change_dt also gets update for current time and date. This SQL is wrapped within a unix shell script. Once a user marks a record Y or N all records belonging this person with flag Y (name) will be extracted via this SQL and sent to another system. So the trigger is marking Y or N. Once the records are sent sent_system_dt gets update with current time and stamp. My SQL will only work for the records that are marked a day prior (last_change_dt = date -1).
For some reason if this unix script fails to run for a day...i will be in a jam because now date-1 will not yield any records since date-1 will not return anything. How to fix this SQL to take care of issues that arise if unix shell script fails to run for certain day or two.
Name send_yn last_change_dt sent_system_dt
joe smith N 2012-03-06 10:35:33 2011-10-19 05:00:15
joe smith N 2012-03-06 10:21:11 2011-10-19 05:00:46
joe smith Y 2011-10-18 14:03:55 2011-10-19 05:00:15
Tom smith N 2012-03-05 10:35:33 2011-10-19 05:00:00
Based on above data and running my SQL today i.e. 03/07/12 i get:
Joe Smith
But what is Unix script failed to run this morning ...how can a make sure that Joe Smith gets picked up during tomorrow run i.e. 3/8/12 ?
Thanks,
Al
Select name from table_name
where (send_yn='Y' or send_yn='N') and (last_chnage_dt = date -1)
and (last_change_dt > sent_system_dt OR sent_system_dt is null)
Users via front end manipulates send_yn (by marking it Y and N) and in doing so last_change_dt also gets update for current time and date. This SQL is wrapped within a unix shell script. Once a user marks a record Y or N all records belonging this person with flag Y (name) will be extracted via this SQL and sent to another system. So the trigger is marking Y or N. Once the records are sent sent_system_dt gets update with current time and stamp. My SQL will only work for the records that are marked a day prior (last_change_dt = date -1).
For some reason if this unix script fails to run for a day...i will be in a jam because now date-1 will not yield any records since date-1 will not return anything. How to fix this SQL to take care of issues that arise if unix shell script fails to run for certain day or two.
Name send_yn last_change_dt sent_system_dt
joe smith N 2012-03-06 10:35:33 2011-10-19 05:00:15
joe smith N 2012-03-06 10:21:11 2011-10-19 05:00:46
joe smith Y 2011-10-18 14:03:55 2011-10-19 05:00:15
Tom smith N 2012-03-05 10:35:33 2011-10-19 05:00:00
Based on above data and running my SQL today i.e. 03/07/12 i get:
Joe Smith
But what is Unix script failed to run this morning ...how can a make sure that Joe Smith gets picked up during tomorrow run i.e. 3/8/12 ?
Thanks,
Al