July 6, 2011 at 7:47 am
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
July 6, 2011 at 10:43 am
Uggh...I thought about this a few different ways, maybe normalizing your Xref table a bit more, maybe running different queries based on inputs that would only join to a subset of the 4 element columns...but I am hearing about too much IF...THEN...ELSE logic required to make your JOIN and WHERE predicates return the proper result. The business problem itself is conditional by nature.
I may get ruthlessly flamed for throwing out this idea but here goes...consider implementing a Multi-statement Table-Valued function (mTVF) to deliver your ServiceLineCode. You would employ the TVF in your queries using APPLY. It would accept all 4 elements and would append the relevant ServiceLineCode, or the default, to your result set. You'll be signed up for RBAR with an mTVF, so performance may suffer in the large, but at least all the business logic will be contained in one place.
With a TVF your queries might look something like this:
SELECT sa.ServiceActivityID,
-- more columns here
xref.ServiceLineCode
FROM ServiceActivity sa
CROSS APPLY dbo.ActivityServiceLineCode(sa.Make, sa.Model, sa.Transmission, sa.Fuel) xref
WHERE sa.ServiceActivityID = 123456 ;
Hopefully someone will drop by with a better plan but this is where I would start. Best case scenario is if your logic is implementable such that you could create your TVF as an Inline TVF (iTVF). With an iTVF your business logic is still contained in one place plus RBAR goes away and you get proper set-based performance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 19, 2011 at 7:40 am
My final solution was to go with a single wide table with every existing combination in it. I then have a routine which checks the datasource and adds new records to the X-ref table for new combinations and an Access maintenance screen to allow the user to allocate the X-ref to a cost center. It's not the most efficient storage wise, but I will be moving on soon and it is relatively simple to understand and shows up glaring DQ issues
(e.g. Insulin Jab (diabietes), given by a nurse who is part of the Falls service whilst working as part of the Macmillan (cancer) team against a referral to podiatry) :w00t:
We get a dozen or so new combinations each week so it is just a case of keeping the plates spinning in terms of the maintenance.
August 19, 2011 at 5:40 pm
Sorry...you lost me at "Jab" 😛
Seriously though, simple to understand sometimes must weigh more heavily than certain technical considerations and it sounds like you got to a place where the technical and the human workflow of the system will fit the environment. I am happy you arrived at a working solution. Thanks for posting back!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply