CODEWITHSUNDEEP
sqlsql-server
John Stud
John Stud

Reputation: 1779

SQL: Conditionally join on a date table if value found

I want to return the columns found in a date dimension, if the values in my columns in a separate dimension are valid and match keys in the date dimension. But, I want to do this conditionally, checking first for attributeOne and whether or not a date key exists there that can map the date dimension, then checking attributeTwo, and so on.

How can I specify a join to the date dimension such that all possible columns that I want to check join if found (e.g., attributeOne, attributeTwo, attributeThree, etc) so I can conditionally report/return the date dimension columns for that match.

JOIN #date D on D.dateKey = d1.attributeOne;

Consider this probably far too contrived example:


drop table #date;
drop table #dim;
drop table #fact;

    CREATE TABLE #date (dateKey int, dateVal varchar(25))
    INSERT  #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  #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  #fact (fKey, Naming)
    VALUES 
    (1, 'ex1')
    ,(2, 'ex2')
    ,(3, 'ex3')
    ,(4, 'ex4')

-- how to 
select
f.fKey as 'F Key',
CASE 
    WHEN d1.attributeOne = D.dateKey THEN D.dateVal
    WHEN d1.attributeTwo = D.dateKey THEN D.dateVal
    WHEN d1.attributeThree = D.dateKey THEN D.dateVal
    ELSE '<missing>' END AS "attributeOne"
from #fact f
JOIN #dim d1 on f.fKey = d1.dKey
JOIN #date D on D.dateKey = d1.attributeOne;

Upvotes: 0

Views: 31

Answers (1)

LukStorms
LukStorms

Reputation: 29667

Maybe something like joining on an IN with some conditional aggregation?

select fact.Naming
, 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

Naming | attributeOne | attributeTwo | attributeThree
:----- | :----------- | :----------- | :-------------
ex1    | FY1          | FY2          |               
ex2    |              | FY4          | FY5           
ex3    | <missing>    | <missing>    | FY4           
ex4    |              | FY2          |

db<>fiddle here

Upvotes: 1

Related Questions