Help with a Two Facts to a Dim

Transact-SQL
1

My two tables I gave an aliases of A & B are fact tables and the table I gave the name C to is a Dim table. I am trying to pull all fields that A and B where they match with C...in some places I will have an A but no B or a B but no A. For some reason the way I wrote this is not working out any help I would be grateful for. I am using MSSQL

SELECT A.DutyName AS Actual,
B.DutyName AS Published,
A.RosterDate AS ActualRosDate,
B.RosterDate AS PubRosDate,
C.CrewID,
C.CrewName,
C.TerminationDate,
C.Status,
E.CrewCategoryDesc AS RosterGroup,
F.PositionCode

FROM FctCrewAvailabilityDetails AS A,
FctCrewAvailabilityDetailsPublished AS B,
Full Outer Join DimCrewMaster AS C
ON A.CrewMasterKey = C.CrewMasterKey = B.CrewMasterKey,
INNER JOIN AIMS.FactCrewQualification_Primary AS D
ON C.CrewMasterKey = D.CrewMasterKey,
INNER JOIN DimCrewCategory AS E
ON D.CrewCategoryKey = E.CrewCategoryKey,
INNER JOIN DimCrewPositions AS F
ON D.CrewPositionKey = F.CrewPositionKey

WHERE A.DutyName IN ('TVL', 'NDL', 'TRAI', 'HUET', 'OPC', 'CRM','LPC', 'SIM', 'GRND', 'X', 'RIOX', 'MEAX', 'CFBX', 'VIXX', 'SBFX', 'MEDV', 'NSBY', 'O')
AND C.TerminationDate = '1980-01-01 00:00:00:000'
AND E.CrewCategoryDesc IN ('Brazil Crew')
AND F.PositionCode IN ('FO', 'CP')

ORDER BY C.CrewID, A.RosterDate

2

Sounds to me like you want to left outer join a and b table.
Try this:

select a.DutyName as Actual
      ,b.DutyName as Published
      ,a.RosterDate as ActualRosDate
      ,b.RosterDate as PubRosDate
      ,c.CrewID
      ,c.CrewName
      ,c.TerminationDate
      ,c.Status
      ,e.CrewCategoryDesc as RosterGroup
      ,f.PositionCode
  from DimCrewMaster as c
       left outer join FctCrewAvailabilityDetails as a
                    on a.CrewMasterKey=c.CrewMasterKey
                   and a.DutyName in ('TVL'
                                     ,'NDL'
                                     ,'TRAI'
                                     ,'HUET'
                                     ,'OPC'
                                     ,'CRM'
                                     ,'LPC'
                                     ,'SIM'
                                     ,'GRND'
                                     ,'X'
                                     ,'RIOX'
                                     ,'MEAX'
                                     ,'CFBX'
                                     ,'VIXX'
                                     ,'SBFX'
                                     ,'MEDV'
                                     ,'NSBY'
                                     ,'O'
                                     )
       left outer join FctCrewAvailabilityDetailsPublished as b
                    on b.CrewMasterKey=c.CrewMasterKey
       inner join AIMS.FactCrewQualification_Primary as d
               on c.CrewMasterKey=c.CrewMasterKey
       inner join DimCrewCategory as e
               on e.CrewCategoryKey=d.CrewCategoryKey
              and e.CrewCategoryDesc='Brazil Crew'
       inner join DimCrewPositions as f
               on f.CrewPositionKey=d.CrewPositionKey
              and f.PositionCode in ('FO','CP')
 where c.TerminationDate='1980-01-01 00:00:00:000'
 order by c.CrewID
         ,a.RosterDate
;
1 Like
3

Thanks you for the Reply. That helped me get all my data into one extract.

Thanks again I appreciate your time