I'm trying to import certain data into a SQL table from an AS400 table. The info I need is order #, a flag that changes, and the current date. When the flag is set to 'Y' on the AS400 on this table, it needs to get imported into the SQL table. Once the flag is changed on the AS400 to an 'N' or blank, it needs to be updated again. This time, the currentdate will be imported into a different column. I want to be able to keep track of how long it stays marked 'Y'.
My thinking is I need to create a table that contains Order #, Flag, Orig. Hold Date (Y), and Release Date (N).
The Stored Procedure would have to:
Grab all orders that are flagged 'Y' in the AS400 and do not exists in the SQL table.
Then grab all orders in the SQL table and find out if there are any orders on the AS400 that are flagged back to 'N'.
I would also like to delete any orders on the SQL table that have a Release Date greater than 30 days.
Any help would be great apprciated.
Thanks,
Greg