M
mel_doherty
I'm having a problem with an update query; I need to update a date
field if it is not a known processing date.
2 tables, 1 has a list of items and the target date for completion the
other has a list of processing dates. We want the items completed on a
processing date, not a weekend, holiday or "freeze day".
I created the list of items and target dates using rules (example: last
day -3 days for Europe, last day -2 days for US) and then I need to
compare the dates generated to a list of processing dates to determine
if the dates are valid. If they are not, I need to return the next or
previous (either will work since I can choose the sort order) date in
the list that is closest to the date if it's not in the list.
Here's a better explanation:
Table1:
acct. target date
1 12/23/06
2 12/25/06
3 12/21/06
4 12/26/06
Table2:
....
12/20/06
12/21/06
12/22/06
12/26/06
12/27/06
....
12/23/06 and 12/25/06 are not in the 2nd table because they are a
weekend and holiday respectively and I need to update only dates that
aren't in table 2 with the closest earlier date (12/22/06 for acct 1
and acct 2) and leave the rest alone. I think I know how to limit it to
only the data that isn't in the 2nd table but I don't know how to tell
it to change the current value to the next earlier date that is in the
table.
I hope that explanation makes sense and that someone knows how to do
this. I've checked all the help and online already and couldn't find
anything.
Thanks very much if you can point me in the right direction!
- Mel
field if it is not a known processing date.
2 tables, 1 has a list of items and the target date for completion the
other has a list of processing dates. We want the items completed on a
processing date, not a weekend, holiday or "freeze day".
I created the list of items and target dates using rules (example: last
day -3 days for Europe, last day -2 days for US) and then I need to
compare the dates generated to a list of processing dates to determine
if the dates are valid. If they are not, I need to return the next or
previous (either will work since I can choose the sort order) date in
the list that is closest to the date if it's not in the list.
Here's a better explanation:
Table1:
acct. target date
1 12/23/06
2 12/25/06
3 12/21/06
4 12/26/06
Table2:
....
12/20/06
12/21/06
12/22/06
12/26/06
12/27/06
....
12/23/06 and 12/25/06 are not in the 2nd table because they are a
weekend and holiday respectively and I need to update only dates that
aren't in table 2 with the closest earlier date (12/22/06 for acct 1
and acct 2) and leave the rest alone. I think I know how to limit it to
only the data that isn't in the 2nd table but I don't know how to tell
it to change the current value to the next earlier date that is in the
table.
I hope that explanation makes sense and that someone knows how to do
this. I've checked all the help and online already and couldn't find
anything.
Thanks very much if you can point me in the right direction!
- Mel