dannyg280
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'