Complex SSIS Lookup/Merge Join Using NK and Dates

I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

-- Brian

Hi Brian. I would go ahead and join based on your NaturalKey, then use a secondary step such as a Conditional Split to filter based on the dates.

Jay|||Brian, I just came across the same problem. Any chance you can post the code example to accomplish this? thanks|||

There's a possible option here: http://blogs.conchango.com/jamiethomson/archive/2006/03/29/3294.aspx

Beware though, no data gets cached in memory.

-Jamie

Complex SSIS Lookup/Merge Join Using NK and Dates

I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

-- Brian

Hi Brian. I would go ahead and join based on your NaturalKey, then use a secondary step such as a Conditional Split to filter based on the dates.

Jay|||Brian, I just came across the same problem. Any chance you can post the code example to accomplish this? thanks|||

There's a possible option here: http://blogs.conchango.com/jamiethomson/archive/2006/03/29/3294.aspx

Beware though, no data gets cached in memory.

-Jamie

Complex SELECT QUERY using look-up tables

Hi gang,

I'm trying to find the best way to get a SELECT query to return field values for a table that are stored in another lookup table. Here's a basic example that will illustrate what I'm trying to do.

Assume three tables: tblItem, tblCustomFieldNames, tblCustomFieldValues.

The schemas/columns for the tables are as follows:

tblItem
id
itemName

tblCustomFieldName
id
customFieldName

tblCustomFieldValue
id
customFieldValue
customFieldID
itemID references tblItem(id)

Further assume, that the tables contain the following data:

tblItem
id|itemName
(1,CPU)
(2,Motherboard)

tblCustomFieldName
id|customFieldName
(1,Manufacturer)
(2,Price)
(3,Qty)

tblCustomFieldValue
id|customFieldValue|customFieldID|itemID
(1,AMD,1,1)
(2,$99,2,1)
(3,2,3,1)
(4,ASUS,1,2)
(5,$79,2,2)
(6,1,3,2)

My question is what does my SQL "SELECT query" syntax need to be such that I am able to return a result with the following form:

tblItem.id|Manufacturer|Price|Qty
(1, Intel, $99, 2)
(2, ASUS, $79, 1)

Note: It's not an option for me to re-design the database schema, as it's someone else's database. I simply need to be able to obtain the above resultset using a single SELECT query.

Thanks in advance! :)
-EHere you are:SELECT id, MAX(manufacturer) manufacturer, MAX(price) price, MAX(qty) qty
FROM
(
SELECT i.id,
CASE n.id
WHEN 1 THEN v.customfieldvalue
ELSE NULL
END manufacturer ,
CASE n.id
WHEN 2 THEN v.customfieldvalue
ELSE NULL
END price,
CASE n.id
WHEN 3 THEN v.customfieldvalue
ELSE NULL
END qty
FROM TBLITEM i, TBLCUSTOMFIELDNAME n, TBLCUSTOMFIELDVALUE v
WHERE v.customfieldid = n.id
AND i.id = v.itemid
)
GROUP BY id;