Problem With Joining 4 Tables With 1 Being A Reference Table

1

I am trying to create a query that will pull data from three different tables with a fourth table being a date reference table between two of them.

Here is my query so far:

SELECT 
    rm2.SQSaleKey
,    rm2.[Plan Code]
,    cd.cd_commission_amount
,    cd.cd_commission_statement_date
,    cd.cd_commission_payment_date
,    dd.MonthYear
,    ece.date_ecmpv
,    ece.cash_ecmpv
FROM  RevOps.dbo.RR_Master2 AS rm2 WITH (NOLOCK)
LEFT OUTER JOIN [SNR-SRTS-Application].dbo.commission_data AS **cd** WITH (NOLOCK)
    ON cd.cd_sale_record_key_SelectCARE = rm2.SQSaleKey
LEFT OUTER JOIN [SNR-SRTS-Supplemental-Information].dbo.mapd_expected_cash_ecmpv_NEW AS **ece** WITH (NOLOCK)
    ON ece.idmpv_ecmpv = rm2.SQSaleKey
LEFT OUTER JOIN RevOps.dbo.Dim_Date AS dd WITH (NOLOCK)
    ON (dd.FullDate = cd.cd_commission_payment_date and dd.FullDate = ece.date_ecmpv)
WHERE  
    (rm2.[Plan Code] = 18285)
    AND (rm2.SQSaleKey = 540053)
   AND dd.FullDate BETWEEN '01-01-2022' AND '07-01-2024'

My issue pertains to when I join the date reference table, Dim_Date, to both the cd and ece tables at the same time. Right now when I run this query nothing is returned. When I remark out one of the "on" clauses in the Dim_Date table join, I get way too many (1,600) records as my output should be around 30 records.

Sorry that the site won't allow me to add screenshots of the tables used.

What my output should be is:
image

I know the select statement above includes more columns then the output below.

Any help is greatly appreciated

2 
ON
    (   dd.FullDate = cd.cd_commission_payment_date 
    OR  dd.FullDate = ece.date_ecmpv
    )

or

ON  dd.FullDate IN (cd.cd_commission_payment_date, ece.date_ecmpv)
3

Even doing this isn't enough if you're doing what I think you're doing.

Will there ALWAYS be at least 1 cd or ece row for every rm2 row?

4

I've tried the OR statement, it returned too many records.

5

85% or more in the ece table. If it is a new account, there may not be any values in the cd table

6

rm2 is the table which contains new account information

7

Give this a try:

ON  dd.FullDate = ISNULL(cd.cd_commission_payment_date, ece.date_ecmpv)
8

I just discovered that there is another field in the Dim_Date table which can be used to tie those tables together. Thanks for your suggestions.

9

I would recommend changing those outer joins to APPLY (probably OUTER but CROSS my work depending on your desired results).

Adding the check for dd.FullDate to the outer WHERE clause, you are effectively turning it into an inner join and dropping any rows from rm2 that do not have any data.

Using OUTER/CROSS APPLY would be something like:

SELECT ...
  FROM RevOps.dbo.RR_Master2 AS rm2
 OUTER APPLY (
    SELECT ...
      FROM RevOps.dbo.DimDate AS dd
      LEFT JOIN [SNR-SRTS-Application].dbo.commission_data AS cd ON cd.cd_sale_record_key_SelectCARE = dd.FullDate AND cd.cd_sale_record_key_SelectCARE = rm2.SQSaleKey
      LEFT JOIN [SNR-SRTS-Supplemental-Information].dbo.mapd_expected_cash_ecmpv_NEW AS ece ON ece.date_ecmpv = dd.FullDate AND ece.idmpv_ecmpv = rm2.SQSaleKey
     WHERE dd.FullDate BETWEEN '01-01-2022' AND '07-01-2024'
             ) AS dts
  WHERE rm2.[Plan_Code] = 18285
    AND rm2.SQSaleKey = 540053;

Using OUTER APPLY will end up returning a row for every date - for each row in RR_Master2, regardless of any matches.

I am just guessing, but if you only want rows that have a commission date - and only the date_ecmpv if it exists that fall in the range then:

SELECT ...
  FROM RevOps.dbo.RR_Master2 AS rm2
 CROSS APPLY (
    SELECT ...
      FROM RevOps.dbo.DimDate AS dd
     INNER JOIN [SNR-SRTS-Application].dbo.commission_data AS cd ON cd.cd_sale_record_key_SelectCARE = dd.FullDate AND cd.cd_sale_record_key_SelectCARE = rm2.SQSaleKey
      LEFT JOIN [SNR-SRTS-Supplemental-Information].dbo.mapd_expected_cash_ecmpv_NEW AS ece ON ece.date_ecmpv = dd.FullDate AND ece.idmpv_ecmpv = rm2.SQSaleKey
     WHERE dd.FullDate BETWEEN '01-01-2022' AND '07-01-2024'
             ) AS dts
  WHERE rm2.[Plan_Code] = 18285
    AND rm2.SQSaleKey = 540053;