I'm new to store procedures and request some help.
I have a detail table with some messy data that i wan to convert to a clean summarized master table. This is needed for specific reasons for calculating comission, etc.
I need to have a record to record script that checks to see if the record is in the Master table. If not I need to add this record's contents to it.
Then If a certain portion of order is canceled or changed, I need to make some adjustments to the numbers in the Master table.
If the revenue numbers hit zero, I need to switch a flag in the master table from 1 to 0 to show it is 100% canceled.
Would I use a stored procedure for this logic or something else? And which ever route I should take, what is the basic routine I would take in accomplishing it?
The detail table will show multiple records for one order if additions are made to it. They are actually legs to a reservation. The way the reporting table was designed, there is a field for RESV_NEW and RESV_CANCEL. If a leg of the reservation was added a flag for RESV_NEW is set as 1. For RESV_CANCEL, the flag would be a 1 if the leg was cancelled. This was a flaw built into the reporting database years ago, and I have now control over it. What I want to do Is have a new table that combines these legs as one reservation and changes the flag in my new master table to 0 when all the legs are cancelled. The way I propose above is pretty much the only solution I have to this problem, so let me know if you can help.