August 6, 2008 at 6:54 am
Hello,
Sorry, bit of long post but it might be worth it 🙂
I have a table - not mine and i've no way of changing it - that looks like this.
CREATE TABLE #TOrigData
(
[SerialNum] [char](9) NOT NULL,
[Component1] [varchar](20) NOT NULL,
[Component2] [varchar](20) NULL,
[Component3] [varchar](20) NULL,
[Component4] [varchar](20) NULL,
[Component5] [varchar](20) NULL,
[Component6] [varchar](20) NULL,
[Component7] [varchar](20) NULL,
[Component8] [varchar](20) NULL
)
I have a second table with this structure -again not mine and i've no way of changing it.
CREATE TABLE #TOrigDatalookup
(
[Component] [varchar](20) NOT NULL,
[ComponentType] [char](1) NOT NULL
)
#TOrigData.Component(1-8) joins to #TOrigDatalookup.Component
I'm trying create a report that works out a value for the row in #TOrigData based on the Component(1-8) columns
using the ComponentType column from #TOrigDatalookup which needs a specific set of rules applied to it.
I started by normalizing #TOrigData and storing the ComponentType column from #TOrigDatalookup
CREATE TABLE #TData
(
[SerialNum] [char](9) NOT NULL,
[ComponentType] [char](1) NOT NULL
)
Insert into #TData
Select SerialNum, ComponentType
from #TOrigData
Inner join #TOrigDatalookup on Component1 = Component
Union All
Select SerialNum, ComponentType
from #TOrigData
Inner join #TOrigDatalookup on Component2 = Component
etc
Some test data:
CREATE TABLE #TData
(
[SerialNum] [char](9) NOT NULL,
[ComponentType] [char](1) NOT NULL
)
INSERT INTO #TData (SerialNum, ComponentType)
SELECT '123456789', 'V' UNION ALL
SELECT '123456789', 'C' UNION ALL
SELECT '123456789', 'D' UNION ALL
SELECT '123456789', 'D' UNION ALL
SELECT '212121212', 'W' UNION ALL
SELECT '345645666', 'V' UNION ALL
SELECT '345645666', 'D' UNION ALL
SELECT '710101566', 'D' UNION ALL
SELECT '785572421', 'D' UNION ALL
SELECT '785572421', 'D' UNION ALL
SELECT '454213138', 'V'
I then needed to apply the following rules to calculate the value for a serial number
if serial number has component Types V+C then must be 1
if serial number has component type W then must be 1
if serial number has only component type V then 2
if serial number has only component type D then 3
if serial number has components type V+D then 4
For reference these business rules are enforced on server
1. Serial number must have at least 1 component
2. No components are mandatory
3. Only 1 V component per serial number
4. Serial numbers with component W will not have any other components
5. C components must be paired with V components
After scratching my head for some time I then actually tried to use my brain.
I began to think this might be easier when it was unnormalized.
First i'd need to use a rather ugly query to get my componenttypes
but the result would get me;
CREATE TABLE #TData
(
[SerialNum] [char](9) NOT NULL,
[CType1] [varchar](20) NOT NULL,
[CType2] [varchar](20) NULL,
[CType3] [varchar](20) NULL,
[CType4] [varchar](20) NULL,
[CType5] [varchar](20) NULL,
[CType6] [varchar](20) NULL,
[CType7] [varchar](20) NULL,
[CType8] [varchar](20) NULL
)
INSERT INTO #TData
SELECT '123456789', 'V','C','D','D',Null,Null,Null,Null UNION ALL
SELECT '212121212', 'W',Null,Null,Null,Null,Null,Null,Null UNION ALL
SELECT '345645666', 'V','D',Null,Null,Null,Null,Null,Null UNION ALL
SELECT '710101566', 'D',Null,Null,Null,Null,Null,Null,Null UNION ALL
SELECT '785572421', 'D','D',Null,Null,Null,Null,Null,Null UNION ALL
SELECT '454213138', 'V',Null,Null,Null,Null,Null,Null,Null
Select SerialNum,
CType1 + isnull(CType2,'') + isnull(CType3,'') + isnull(CType4,'') +
isnull(CType5,'') + isnull(CType6,'') + isnull(CType7,'') + isnull(CType8,'') as CT,
Case
when CType1 + isnull(CType2,'') + isnull(CType3,'') + isnull(CType4,'') + isnull(CType5,'') +
isnull(CType6,'') + isnull(CType7,'') + isnull(CType8,'') like '%VC%' then 1
when CType1 + isnull(CType2,'') + isnull(CType3,'') + isnull(CType4,'') + isnull(CType5,'') +
isnull(CType6,'') + isnull(CType7,'') + isnull(CType8,'') = 'W' then 1
when CType1 + isnull(CType2,'') + isnull(CType3,'') + isnull(CType4,'') + isnull(CType5,'') +
isnull(CType6,'') + isnull(CType7,'') + isnull(CType8,'') like '%VD%' then 4
when CType1 + isnull(CType2,'') + isnull(CType3,'') + isnull(CType4,'') + isnull(CType5,'') +
isnull(CType6,'') + isnull(CType7,'') + isnull(CType8,'') like '%V%' then 2
when CType1 + isnull(CType2,'') + isnull(CType3,'') + isnull(CType4,'') + isnull(CType5,'') +
isnull(CType6,'') + isnull(CType7,'') + isnull(CType8,'') like '%D%' then 3
End as SerialNumValue
From #TData
DROP TABLE #TData
This gives me the expected results.
SerialNum CT SerialNumberValue
123456789VCDD1
212121212W1
345645666VD4
710101566D3
785572421DD3
454213138V2
However this method doesn't "feel right" at all i.e. what would i do if i was stuck with the normalised version?
I'm not expecting anyone to actually answer this i'd just like a pointer to some
literature which deals with how to calculate results over rows when you need to apply
rules and you have your data in a normalized format.
For reference this will be used once overnight for part of an ODS reporting
service so speed is not essential. It will be working on 500,000 rows of data growing at ~30,000 rows a month.
Thanks
K.
August 6, 2008 at 3:03 pm
What about using left join, so you will get one row for serial number with calculated component type which can be verified against you business rules to get SerialNumberValue.
Insert into #TData(SerialNum, CalculatedComponentType)
Select od.SerialNum
,IsNull(odl1.ComponentType,'')+IsNull(odl2.ComponentType,'')+IsNull(odl3.ComponentType,'')+...+IsNull(odl8.ComponentType,'')
from #TOrigData od
left join #TOrigDatalookup odl1
on od.Component1 = odl1.Component
left join #TOrigDatalookup odl2
on od.Component2 = odl2.Component
left join #TOrigDatalookup odl3
on od.Component3 = odl3.Component
.....
left join #TOrigDatalookup odl8
on od.Component8 = odl8.Component
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply