Hi SQL Experts,
We have a situation where we have a sales order table with a Sales Order say number 000999 which has attached to it a serial number 2345678. At some point in the life of the sales order, this serial number could change to a different number, e.g. 7891011, because the customer upgraded. Currently, we have no way of tracking when this happened! Our IT group is working on that... but in the meantime, what we have is a ‘live’ sales order table which would currently show:
Sales_Order_Number, Serial_No
000999 7891011
We also have frozen tables for each month in the exact same format. So some month since Jan 2007,(transaction could have only happened as of 1/1/2007) we could find in one of the 20 frozen month tables, say Sales_Orders_2007_JUN_Frozen:
Sales_Order_Number, Serial_No
000999 2345678
Someone asked me if I could search all the tables to get a count of how many times this has happened. Even better yet, identify the sales order and the month it was changed.
Is there any way for me to search those 20 separate tables (2007_JAN…2008_AUG) all at once for each Sales_Order (they are estimating 20,000 such orders)? I’m using Hyperion Interactive, but could use anything. There are an avg of 4 million rows in the Sales_order table and it has 40 columns.
It sounds like maybe a script is in my future...?
Thank you!
We have a situation where we have a sales order table with a Sales Order say number 000999 which has attached to it a serial number 2345678. At some point in the life of the sales order, this serial number could change to a different number, e.g. 7891011, because the customer upgraded. Currently, we have no way of tracking when this happened! Our IT group is working on that... but in the meantime, what we have is a ‘live’ sales order table which would currently show:
Sales_Order_Number, Serial_No
000999 7891011
We also have frozen tables for each month in the exact same format. So some month since Jan 2007,(transaction could have only happened as of 1/1/2007) we could find in one of the 20 frozen month tables, say Sales_Orders_2007_JUN_Frozen:
Sales_Order_Number, Serial_No
000999 2345678
Someone asked me if I could search all the tables to get a count of how many times this has happened. Even better yet, identify the sales order and the month it was changed.
Is there any way for me to search those 20 separate tables (2007_JAN…2008_AUG) all at once for each Sales_Order (they are estimating 20,000 such orders)? I’m using Hyperion Interactive, but could use anything. There are an avg of 4 million rows in the Sales_order table and it has 40 columns.
It sounds like maybe a script is in my future...?
Thank you!