Get empty rows based on sequence table

• tshad

  SSCertifiable

  Points: 5905

  More actions

  February 23, 2018 at 2:30 pm

  #410264

  I am trying to get a list of rows for each customer based on quarters of a year.  If I ask for a particular year, I would get 4 records even if they don't already exist for each quarter.  If one exists, I should get 1 row with data and three rows with some customer data and nulls in the other fields.

  I can get it work fine if there are no records using a Cross Join, but if there is even one record I get 4 identical records.

  If you run the following script you can see the issue:

  This is what I would expect if there are no records
  

  If there is one record I get this:
  

  
DECLARE @Quarters TABLE
(
   Qtr int
)
DECLARE @Customers TABLE
(
   CustomerID INT,
   NAME varchar(100)
)
DECLARE @CustomerActivity TABLE
(
   CustomerActivityID INT,
   CustomerID INT,
   CAYear INT,
   CAQtr int
)
INSERT @Customers
(
   CustomerID,
    NAME
)
VALUES
(
   1,
   'Tom'
)
INSERT @Quarters
SELECT TOP (4) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM sys.all_objects AS s1

  SELECT *
  FROM @Customers c
  LEFT JOIN @CustomerActivity ca
  ON c.CustomerID = ca.CustomerID
  RIGHT JOIN @Quarters qt
  ON qt.Qtr = ca.CAQtr

  
  INSERT @CustomerActivity
  (
      CustomerActivityID,
      CustomerID,
      CAYear,
      CAQtr
  )
  VALUES
  (   1, -- CustomerActivityID - int
      1, -- CustomerID - int
      2017, -- CAYear - int
      4  -- CAQtr - int
      )

  SELECT *
  FROM @Customers c
  LEFT JOIN @CustomerActivity ca
  ON c.CustomerID = ca.CustomerID
  RIGHT JOIN @Quarters qt
  ON qt.Qtr = ca.CAQtr
  

  I tried to use a right join and got the 4 records, but the customer detail is missing:

  

  SELECT *
FROM @Customers c
LEFT JOIN @CustomerActivity ca
ON c.CustomerID = ca.CustomerID
RIGHT JOIN @Quarters qt
ON qt.Qtr = ca.CAQtr

  Thanks,

  Tom

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  February 23, 2018 at 2:46 pm

  #1981011

  Part of the issue is that you're joining in the wrong order and part is that you need to specify a more complete condition on the join.

  SELECT *
FROM @Customers c
CROSS JOIN @Quarters q  /*  Do the CROSS JOIN before the LEFT OUTER JOIN  */
LEFT JOIN @CustomerActivity ca
  ON c.CustomerID = ca.CustomerID
   AND q.Qtr = ca.CAQtr  /*  Include the quarter in the JOIN conditions  */


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• tshad

  SSCertifiable

  Points: 5905

  More actions

  February 23, 2018 at 3:14 pm

  #1981014

  That appears to be it.

  I am going to try it on my bigger query but it makes absolute sense once I saw it.

  Thanks a lot