Searching many tables for a change in value 1

[funforus]

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!

 

[PHV]

SELECT Sales_Order_Number, MIN(Serial_No), MAX(Serial_No)
FROM (SELECT Sales_Order_Number, Serial_No FROM 2007_JAN
UNION SELECT Sales_Order_Number, Serial_No FROM 2007_FEB
...
UNION SELECT Sales_Order_Number, Serial_No FROM 2008_AUG
) U
GROUP BY Sales_Order_Number
HAVING COUNT(*)>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLSister]

Be aware that that PHVs solution may not be speedy with so many rows, so I suggest you run it when the server is least busy.

"NOTHING is more important in a database than integrity." ESquared

 

[funforus]

Did the trick, thanks PHV! And yes, very slow -thanks sQLSister for the warning.