SQL Column results with NULL Values

Question

I am having the following issue with my query.

I am trying to import data from multiple tables (Fact_Contact, Quali_Seg, etc…) into one table (Fact_Forecast). This is to predict how many individuals are eligible for a specific offer. The problem I am having is that for some reason, the column Date_ID, which is been pulled from Fact_Contact, when importing has NULL values. I don’t know where these NULL values are coming from as the table Fact_Contact don’t have any NULL values in the column DATE_ID.

This is the section of the query that has the problem,

DECLARE @lastDateID int
 SELECT TOP 1 @lastDateID = date_id
   FROM Fact_Contact
  ORDER BY CREATE_DATE DESC

 SELECT date_id, Offers.Segmentation_id, Offers.Offer_Code, Offers.Wave_no,
        Offers.cadencevalue,
        CASE
            WHEN dailydata.activity_count IS NOT NULL THEN dailydata.activity_count
            ELSE 0
        END as "activity_count"
   FROM (
         SELECT s.Segmentation_id, s.Offer_Code, s.Wave_no, o.cadencevalue,
                o.campaign_id, o.offer_desc
           FROM Forecast_Model.dbo.Quali_Segment s
                 LEFT JOIN Forecast_Model.dbo.Dim_Offers o 
                        ON s.offer_code = o.offer_code
        ) Offers
           LEFT JOIN (
                      SELECT date_id, Offer_Code_1 Offer_Code,
                             segmentation_group_id, Count(indv_role_id) Activity_count
                        FROM Forecast_Model.dbo.Fact_Contact
                       WHERE date_id = @lastDateID
                       GROUP BY offer_code_1,segmentation_group_id,date_id
                     ) DailyData  
                  ON DailyData.offer_code = Offers.offer_code
                      AND Offers.Segmentation_id = dailydata.segmentation_group_id 
  ORDER BY Segmentation_id,Wave_no

The column Date_ID as I mentiones gets only 2 dates which is the same as the @LastDateID which is 2014-05-20 but the rest are NULL.

Thank you, Omar

You're pulling date_id from the right side of a left join. A left join means the right side does not have to exist, and therefore may consist of entirely null values. — Joachim Isaksson
– Joachim Isaksson, Commented May 27, 2014 at 20:13

RyanB · Accepted Answer · 2014-05-27 20:14:45Z

1

date_id will be NULL whenever you have records in Offers (join Quali_Segment) but no matching records in Fact_Contact

answered May 27, 2014 at 20:14

RyanB

7574 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ron tornambe Over a year ago

+1 for being on right track. You should mention that the NULL occurs only with inner joins.

Collectives™ on Stack Overflow

SQL Column results with NULL Values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related