dateKey int,

dateVal varchar(25)

);

INSERT INTO #date (dateKey, dateVal) VALUES

(20000101, 'FY1')

,(20000102, 'FY2')

,(20000103, 'FY3')

,(20000104, 'FY4')

,(20000105, 'FY5');

CREATE TABLE #dim (

dKey int,

attributeOne int,

attributeTwo int,

attributeThree int

);

INSERT INTO #dim

(dKey, attributeOne, attributeTwo, attributeThree) VALUES

(1, 20000101, 20000102, NULL)

,(2, NULL, 20000104, 20000105)

,(3, 20000301, 20000501, 20000104)

,(4, NULL, 20000102, NULL);

CREATE TABLE #fact (

fKey int,

Naming varchar(25)

);

INSERT INTO #fact

(fKey, Naming) VALUES

(1, 'ex1')

,(2, 'ex2')

,(3, 'ex3')

, MAX(CASE WHEN dim.attributeOne = dt.dateKey THEN dt.dateVal WHEN dim.attributeOne IS NOT NULL THEN '<missing>' ELSE '' END) AS attributeOne

, MAX(CASE WHEN dim.attributeTwo = dt.dateKey THEN dt.dateVal WHEN dim.attributeTwo IS NOT NULL THEN '<missing>' ELSE '' END) AS attributeTwo

, MAX(CASE WHEN dim.attributeThree = dt.dateKey THEN dt.dateVal WHEN dim.attributeThree IS NOT NULL THEN '<missing>' ELSE '' END) AS attributeThree

FROM #fact AS fact

LEFT JOIN #dim AS dim

ON dim.dKey = fact.fKey

LEFT JOIN #date AS dt

ON dt.dateKey IN (dim.attributeOne, dim.attributeTwo, dim.attributeThree)

GROUP BY fact.fKey, fact.Naming

ORDER BY fact.fKey