Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  x-ref table with missing elements

Author  Topic  obiron Starting Member 23 Posts Posted - 2011-07-06 : 08:02:13 Hi guys, Did a brief search by couldn't find a solution for this. Lets say I have some data that I need to map to an activity code (most common scenario is cost center mapping) but for some activity I am not interested in one or more elements of the activity for some maps. Hypothetical example: servicing vehicles. The following elements MAY affect the service cost center line. Make, Model, Transmission, Fuel type; Each activity to be costed will contain all four elements. I would set up an X-Ref table with these four columns and the target service line code. CREATE TABLE Xref as ( Make nvarchar(100), Model nvarchar(100), Transmission nvarchar(100), Fuel nvarchar(100), ServiceLinecode nvarchar(10) ) If the make is Ford then I only care about the Transmission and Fuel; I dont want to enter a line for each model. If the make is Jeep, then I am interested in Model and Transmission, I don't care about Fuel If the make is Ferrari I don't care about anything else. If the make is Porsche then I care about the model (Boxter, Cayenne, 911) and I only care about the transmission IF it is a 911. If it is an electric car, I don't care about the make, model or transmission. I don't want to lose any data, and all activity must feed to a ServiceLineCode. Anything that does not have an interface will go to a default code (X999) The questions I have are; What data would you put into the empty cells How would you code the join between the activity records and the Xref table How would you escalate a match through all elements to xref lines that match 3, 2 or 1 element (E.g an Electric Ferrari car does not have a specific interface line but matches both Ferrari and Electric) The elements may not need to be matched in field order. How would you cater for unhandled exceptions, E.g. I don't have a line for Ford with Hybrid transmission, I don't have Porsche Panama in the model list.I don't have a 911 with tiptronic transmission, I don't have any interfaces for Honda Data for XRef table insert into xref values('Ford','','Auto','Diesel','A000') insert into xref values('Ford','','Auto','Petrol','A001') insert into xref values('Ford','','Manual','Diesel','A000') insert into xref values('Ford','','Manual','Petrol','A002') insert into xref values('Jeep','Cherokee','Manual','','B000') insert into xref values('Jeep','Cherokee','Auto','','B001') insert into xref values('Jeep','Wrangler','Manual','','B002') insert into xref values('Jeep','Wrangler','Auto','','B003') insert into xref values('Ferrari','','','','C001') insert into xref values('Porsche','Boxter','','','D000') insert into xref values('Porsche','Cayenne','','','D001') insert into xref values('Porsche','911','Manual','','D002') insert into xref values('Porsche','911','Auto','','D003') insert into xref values('Porsche','911','','','D003') insert into xref values('','','','Electric','G456') insert into xref values('','','','','X999') TIA Obiron