Link to home
Start Free TrialLog in
Avatar of dannyg280
dannyg280Flag for United States of America

asked on

SQL: Select Statement to return all records even if one condition is not met

I have the query below which is pulling a comparison report on sales for schools for 2015 and 2016. My problem is that the table "arcsales" only has entries if there was sales activity during that year so the "T.FiscalYear = 2015 AND T4.FiscalYear = 2016" causes any school who has no sales in one of the 2 years not to appear. How do I get the all records to appear that had activity in either year, and have 0 or null appear in the SummerSales or SummerProfit column if there was no enteries for that year?
This is running on MS SQL.

SELECT CustAcct, 
         CustomerName, 
         SManCode, 
         CustGroupCode, 
         MIN(SummerSales15)  AS SummerSales15, 
         MIN(SummerProfit15)   AS SummerProfit15, 
         MIN(SummerSales16)  AS SummerSales16, 
         MIN(SummerProfit16) AS SummerProfit16, 
         SUM(PubProfit)      AS 'Pending Orders Profit' 
    FROM (  SELECT T1.CustAcct, 
                   T1.CustomerName, 
                   T2.SManCode                                                        AS SmanCode,
                   T3.CustGroupCode, 
                   SUM(T.SalesBal1 + T.SalesBal2 + T.SalesBal3 + T.SalesBal4 + T.SalesBal5 + T.SalesBal6 + T.SalesBal7 + T.SalesBal8)         AS 'SummerSales15',
                   SUM(T.PubProfit1 + T.PubProfit2 + T.PubProfit3 + T.PubProfit4 + T.PubProfit5 + T.PubProfit6 + T.PubProfit7 + T.PubProfit8)     AS 'SummerProfit15',
                   SUM(T.SalesBal1 + T.SalesBal2 + T.SalesBal3 + T.SalesBal4 + T4.SalesBal5 + T4.SalesBal6 + T4.SalesBal7 + T4.SalesBal8)     AS 'SummerSales16',
                   SUM(T.PubProfit1 + T.PubProfit2 + T.PubProfit3 + T.PubProfit4 + T4.PubProfit5 + T4.PubProfit6 + T4.PubProfit7 + T4.PubProfit8) AS 'SummerProfit16',
                   T5.PubProfit 
              FROM arcsales T 
                   LEFT  JOIN arcusts T1 
                     ON T.CustID = T1.CustID 
                   LEFT  JOIN arsmen T2 
                     ON T1.SManID = T2.SManID 
                   LEFT  JOIN argroups T3 
                     ON T1.ARGroupID = T3.CustGroupID 
                  LEFT JOIN arcsales T4 
                     ON T1.CustID = T4.CustID 
                   LEFT OUTER JOIN obordhed T5 
                     ON T1.CustID = T5.CustID 
             WHERE T3.CustGroupCode = 'SCHOOL' 
                   AND T.FiscalYear = 2015 
                   AND T4.FiscalYear = 2016 
                   AND T5.StatusCode <> 'Q' 
          GROUP BY T1.CustAcct, 
                   T1.CustomerName, 
                   T2.SManCode, 
                   T3.CustGroupCode, 
                   T5.PubProfit) t1 
GROUP BY CustAcct, 
         CustomerName, 
         SManCode, 
         CustGroupCode 

Open in new window

Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Since it's a LEFT JOIN you can add IS NULL filter for the fiscal year.
Change the WHERE clause to:

WHERE T3.CustGroupCode = 'SCHOOL'
                  AND (T.FiscalYear = 2015 OR T.FiscalYear IS NULL)
                   AND (T4.FiscalYear = 2016 OR T4.FiscalYear IS NULL)

                   AND T5.StatusCode <> 'Q'
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial