How to return a value if an INNER JOIN isn't satisfied?

• chocthree

  Ten Centuries

  Points: 1276

  More actions

  March 15, 2019 at 9:30 am

  #377688

  Hi,
  I have Historical T-SQL query as below:
  SELECT 'North Historical' AS Region
       , Grouping_Level
       , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
       , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
       , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
       , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
  FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
  WHERE Region IN ( 'TOP North Historical' )
  GROUP BY Grouping_Level
  
  that returns the below dataset:
  

  I have National T-SQL query as below:

  SELECT 'North National' AS Region
       , Grouping_Level
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
      , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
  FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
  WHERE Region IN ( 'TOP North National')
  GROUP BY Grouping_Level

  that returns the below dataset:
  

  However, I have wrapped both queries into an INNER JOIN to be able to do a subtraction between two values, one from each table. Below is my full query:
  

  SELECT
       'North Central' AS Region
       , nh.Grouping_Level AS Grouping_Level
       , ( nh.MTDValue - nn.MTDValue ) AS MTDValue
       , ( nh.MTDTarget - nn.MTDTarget ) AS MTDTarget
       , ( nh.DEValue - nn.DEValue ) AS DEValue
       , ( nh.DETarget - nn.DETarget ) AS DETarget
  FROM 
    (
     SELECT 'North Historical' AS Region
           , Grouping_Level
           , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
           , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
           , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
          , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
     FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
     WHERE Region IN ( 'TOP North Historical' )
     GROUP BY Grouping_Level ) AS nh
     INNER JOIN
   (
          SELECT 'North National' AS Region
          , Grouping_Level
           , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
          , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
          , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
           , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
     FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
     WHERE Region IN ( 'TOP North National')
     GROUP BY Grouping_Level ) AS nn  
   ON nh.Grouping_Level = nn.Grouping_Level

  The returned dataset is:
  

  How do I make it so the subtraction is performed when the INNER JOIN is satisfied, otherwise do the following:
  1) The Historical value is returned if this side of the join has a value
  2) The National value is returned if this side of the join has a value BUT this value needs to be a negative number as the calculation would be, in affect, 0/empty - the National value (a double negative must remain a negative).
  3) If the value doesn't exist in either table a 0 (zero) is returned.

  Please help (amend my query to meet the requirements)?
  Thanks in advance.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 15, 2019 at 9:42 am

  #2024604

  I don't really know, but here's a guess:
  SELECT
'North Central' AS Region
, nh.Grouping_Level AS Grouping_Level
, (COALESCE(nh.MTDValue,0) - COALESCE(nn.MTDValue,0) ) AS MTDValue
, (COALESCE(nh.MTDTarget,0) - COALESCE(nn.MTDTarget,0) ) AS MTDTarget
, (COALESCE(nh.DEValue,0) - COALESCE(nn.DEValue,0) ) AS DEValue
, (COALESCE(nh.DETarget,0) - COALESCE(nn.DETarget,0) ) AS DETarget
FROM
(
SELECT 'North Historical' AS Region
, Grouping_Level
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
WHERE Region IN ( '########' )
GROUP BY Grouping_Level ) AS nh
FULL OUTER JOIN
(
SELECT 'North National' AS Region
, Grouping_Level
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
, SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
WHERE Region IN ( '########' )
GROUP BY Grouping_Level ) AS nn
ON nh.Grouping_Level = nn.Grouping_Level

  If that's not what you're looking for, please provide table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and expected results.

  John

• chocthree

  Ten Centuries

  Points: 1276

  More actions

  March 15, 2019 at 10:05 am

  #2024607

  Hi John, having tried using COALESCE I find it doesn't achieve what I require. The full query continues to return nothing due to the join not being satisifed and no 0 (zero) is being substituted. Thanks.

• Jonathan AC Roberts

  SSCoach

  Points: 18347

  More actions

  March 15, 2019 at 10:09 am

  #2024608

  Have you tried FULL JOIN instead of INNER?

• chocthree

  Ten Centuries

  Points: 1276

  More actions

  March 15, 2019 at 10:18 am

  #2024611

  Hi Jonathan,
  Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
  

  So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736162

  More actions

  March 15, 2019 at 10:50 am

  #2024616

  You're joining on grouping level, but the grouping levels don't match. Is there something else that should match? Or are you trying to ensure the single row from the national level is applied to every other one?

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  March 15, 2019 at 11:53 am

  #2024630

  chocthree - Friday, March 15, 2019 10:18 AM

  Hi Jonathan,
  Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
  

  So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

  With an INNER JOIN, it doesn't matter whether you're pulling nh.Grouping_Level or nn.Grouping_Level, because they will be the same based on the join conditions.  That's not true with a FULL OUTER JOIN.  You're only looking at one, when you need to look at both.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Jonathan AC Roberts

  SSCoach

  Points: 18347

  More actions

  March 15, 2019 at 12:21 pm

  #2024635

  chocthree - Friday, March 15, 2019 10:18 AM

  Hi Jonathan,
  Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
  

  So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

  It would be easier for us to understand if you gave us the data in the tables and the output you expect from the query.

• kaj

  SSCommitted

  Points: 1605

  More actions

  March 15, 2019 at 1:36 pm

  #2024646

  Jonathan AC Roberts - Friday, March 15, 2019 12:21 PM

  chocthree - Friday, March 15, 2019 10:18 AM

  Hi Jonathan,
  Using a FULL JOIN in place of an INNER JOIN returns the below dataset:
  

  So it does return some data, however in the Grouping_Level column only CoS Adj value/heading is returned. The five remaining rows need to have their Grouping_Level values/heading returned which are those shown in the National query's dataset as shown above (in the OP).

  It would be easier for us to understand if you gave us the data in the tables and the output you expect from the query.

  Yes, DDL and test data + expected outcome would be very welcome.

  Lacking that, how about something like this:
  SELECT
 'North Central' AS Region
 , COALESCE(nh.Grouping_Level, nn.Grouping_Level) AS Grouping_Level
 , (COALESCE(nh.MTDValue,0) - CASE WHEN nn.MTDValue IS NOT NULL AND nn.MTDValue < 0 THEN nn.MTDValue ELSE 0 END) AS MTDValue
 , (COALESCE(nh.MTDTarget,0) - CASE WHEN nn.MTDTarget IS NOT NULL AND nn.MTDTarget < 0 THEN nn.MTDTarget ELSE 0 END) AS MTDTarget
 , (COALESCE(nh.DEValue,0) - CASE WHEN nn.DEValue IS NOT NULL AND nn.DEValue < 0 THEN nn.DEValue ELSE 0 END) AS DEValue
 , (COALESCE(nh.DETarget,0) - CASE WHEN nn.DETarget IS NOT NULL AND nn.DETarget < 0 THEN nn.DETarget ELSE 0 END) AS DETarget
FROM
 (
 SELECT
  'North Historical' AS Region
  , Grouping_Level
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
 FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
 WHERE Region IN ( '########' )
 GROUP BY Grouping_Level
 ) AS nh
FULL OUTER JOIN
 (
 SELECT
  'North National' AS Region
  , Grouping_Level
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDValue ), 2 ) ) AS MTDValue
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), MTDTarget ), 2 ) ) AS MTDTarget
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DEValue ), 2 ) ) AS DEValue
  , SUM( ROUND( CONVERT( DECIMAL( 36,2 ), DETarget ), 2 ) ) AS DETarget
 FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
 WHERE Region IN ( '########' )
 GROUP BY Grouping_Level
 ) AS nn
ON nh.Grouping_Level = nn.Grouping_Level

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  March 17, 2019 at 10:37 am

  #2024721

  chocthree - Friday, March 15, 2019 9:30 AM

  How do I make it so the subtraction is performed when the INNER JOIN is satisfied, otherwise do the following:
  1) The Historical value is returned if this side of the join has a value
  2) The National value is returned if this side of the join has a value BUT this value needs to be a negative number as the calculation would be, in affect, 0/empty - the National value (a double negative must remain a negative).
  3) If the value doesn't exist in either table a 0 (zero) is returned.

  Please help (amend my query to meet the requirements)?
  Thanks in advance.

  The problem - I believe - is that you want a list of all possible grouping_levels but you have not included anything that will contain that list.  Based on your queries - you will have a value in either the historical or national query and therefore you will never encounter item 3 above.

  For Item 2 - I am not sure what you mean by a double negative must remain a negative.  For example, if the historical value does not exist and the national value is negative - what value should be returned?  If the historical value exists - but is less than or equal to 0.00 - and the national value is negative - what value should be returned?

  With allGroupingLevels (Region, Grouping_Level)
  As (
Select Distinct
   'North Central'
  , dd.Grouping_Level
 From MISReporting.dbo.tbl_DailyE2E_Div   dd
Where dd.Region In ('TOP North Historical', 'TOP North National')
   )
  , historicalTotals (Grouping_Level, MTDValue, MTDTarget, DEValue, DETarget)
  As (
Select Grouping_Level
  , sum(round(convert(decimal(36, 2), dd.MTDValue), 2))
  , sum(round(convert(decimal(36, 2), dd.MTDTarget), 2))
  , sum(round(convert(decimal(36, 2), dd.DEValue), 2))
  , sum(round(convert(decimal(36, 2), dd.DETarget), 2))
 From MISReporting.dbo.tbl_DailyE2E_Div   dd
Where dd.Region = 'TOP North Historical'
Group By
   dd.Grouping_Level
   )
  , nationalTotals (Grouping_Level, MTDValue, MTDTarget, DEValue, DETarget)
  As (
Select Grouping_Level
  , sum(round(convert(decimal(36, 2), dd.MTDValue), 2))
  , sum(round(convert(decimal(36, 2), dd.MTDTarget), 2))
  , sum(round(convert(decimal(36, 2), dd.DEValue), 2))
  , sum(round(convert(decimal(36, 2), dd.DETarget), 2))
 From MISReporting.dbo.tbl_DailyE2E_Div   dd
Where dd.Region = 'TOP North National'
Group By
   dd.Grouping_Level
   )
Select agl.Region
  , agl.Grouping_Level
  , MTDValue = coalesce(nh.MTDValue, 0.00) - coalesce(iif(coalesce(nh.MTDValue, 0.00) <= 0.00, (nn.MTDValue * -1), nn.MTDValue), 0.00)
  , MTDTarget = coalesce(nh.MTDTarget, 0.00) - coalesce(iif(coalesce(nh.MTDTarget, 0.00) <= 0.00, (nn.MTDTarget * -1), nn.MTDTarget), 0.00)
  , DEValue = coalesce(nh.DEValue, 0.00) - coalesce(iif(coalesce(nh.DEValue, 0.00) <= 0.00, (nn.DEValue * -1), nn.DEValue), 0.00)
  , DETarget = coalesce(nh.DETarget, 0.00) - coalesce(iif(coalesce(nh.DETarget, 0.00) <= 0.00, (nn.DETarget * -1), nn.DEValue), 0.00)
 From allGroupingLevels     agl
 Left Join historicalTotals    nh On nh.Grouping_Level = agl.Grouping_Level
 Left Join nationalTotals     nn On nn.Grouping_Level = agl.Grouping_Level;


  Here is what I think you are looking for - but this will not satisfy item 3.  In order to satisfy item 3 - you would need a complete list of all available grouping levels.  If you can identify the query that produces that list - or a table that contains that list - you can put it in place of the query used in the allGroupingLevels CTE.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• chocthree

  Ten Centuries

  Points: 1276

  More actions

  March 20, 2019 at 4:41 am

  #2024987

  Thank you all for your responses.
  There is a winner and that is Jeffrey Williams' post. I had to tweak it slightly to handle the negative values. Jeffrey, I know you required clarification on the negative comments but yoru post helped me to just put the cherry on the cake.
  Thank you all again. I've learnt another trick if I have the same scenario in the future.

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  March 20, 2019 at 2:42 pm

  #2025069

  chocthree - Wednesday, March 20, 2019 4:41 AM

  Thank you all for your responses.
  There is a winner and that is Jeffrey Williams' post. I had to tweak it slightly to handle the negative values. Jeffrey, I know you required clarification on the negative comments but yoru post helped me to just put the cherry on the cake.
  Thank you all again. I've learnt another trick if I have the same scenario in the future.

  Glad I could help - so what tweaks did you make for the negative values?

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 21, 2019 at 2:49 am

  #2025085

  You'd almost certainly get better performance by combining both of those aggregates into one, like this:
  SELECT
   'North Central' AS Region,
   Grouping_Level,
   (nh_MTDValue - nn_MTDValue) AS MTDValue,
   (nh_MTDTarget - nn_MTDTarget) AS MTDTarget,
   (nh_DEValue - nn_DEValue) AS DEValue,
   (nh_DETarget - nn_DETarget) AS DETarget
  FROM (
   SELECT
    'North Historical' AS Region,
    Grouping_Level,
    SUM(CASE WHEN Region = 'TOP North Historical' THEN MTDValue ELSE 0 END) AS nh_MTDValue,
    SUM(CASE WHEN Region = 'TOP North Historical' THEN MTDTarget ELSE 0 END) AS nh_MTDTarget,
    SUM(CASE WHEN Region = 'TOP North Historical' THEN DEValue ELSE 0 END) AS nh_DEValue,
    SUM(CASE WHEN Region = 'TOP North Historical' THEN DETarget ELSE 0 END) AS nh_DETarget,

    SUM(CASE WHEN Region = 'TOP North National' THEN MTDValue ELSE 0 END) AS nn_MTDValue,
    SUM(CASE WHEN Region = 'TOP North National' THEN MTDTarget ELSE 0 END) AS nn_MTDTarget,
    SUM(CASE WHEN Region = 'TOP North National' THEN DEValue ELSE 0 END) AS nn_DEValue,
    SUM(CASE WHEN Region = 'TOP North National' THEN DETarget ELSE 0 END) AS nn_DETarget
   FROM [MISReporting].[dbo].[tbl_DailyE2E_Div]
   WHERE Region IN ('TOP North Historical', 'TOP North National')
   GROUP BY Grouping_Level
  ) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden