Hello, I'm trying to get a list of all cost center values along with their active from and two dates using the view dbo.DIMATTRIBUTEOMCOSTCENTER as the main source which returns all cost center codes and descriptions. The problem is that when I join the view to DIMENSIONATTRIBUTEVALUE and then DIMENSIONATTRIBUTE to get the dates I'm only getting a subset.
So for example this query returns 3 records:
SELECT *
FROM dbo.DIMATTRIBUTEOMCOSTCENTER dacc
WHERE 1 = 1
AND dacc.VALUE IN ('AAAA', 'BBBB', 'CCCC')
But when I do a LEFT JOIN to get the active from and two dates I only get back two records, one for 'AAAA' and 'BBBB'. 'CCCC' is not returned.
SELECT
dacc.RECID
, dacc.VALUE
, dacc.NAME
, dav.ACTIVEFROM
, dav.ACTIVETO
, da.NAME AS DimensionAttribute_NAME
FROM dbo.DIMATTRIBUTEOMCOSTCENTER dacc
LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUE dav ON dacc.RECID = dav.ENTITYINSTANCE
LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTE da ON dav.DIMENSIONATTRIBUTE = da.RECID
WHERE 1 = 1
AND dacc.VALUE IN ('AAAA', 'BBBB', 'CCCC')
AND da.NAME = 'CostCenter'
Anyone have any ideas? Also I don't have to use this query if there's a better way to do this.
