dannyg280
asked on
Need help pulling data will NULL fields from MS SQL query.
Hello,
I have a query which pulls contact data from accounts that a salesman has and also includes sales and profit data from the prior year. This works fine, except if the account had no activity in 2012 (or no entry for 2012 in the arcsales table) then it doesn't pull any data for that account. I need it to pull the contact info regardless if there is Sales and Profit info. I've been told I need to check for null fields for T3.YTDTotSales and
T3.YTDPubProfit but I am ubsure how to do this.
I have a query which pulls contact data from accounts that a salesman has and also includes sales and profit data from the prior year. This works fine, except if the account had no activity in 2012 (or no entry for 2012 in the arcsales table) then it doesn't pull any data for that account. I need it to pull the contact info regardless if there is Sales and Profit info. I've been told I need to check for null fields for T3.YTDTotSales and
T3.YTDPubProfit but I am ubsure how to do this.
SELECT
T.CustAcct,
T.CustomerName,
T.Address1,
T.Address2,
T.City,
T.State,
T.Zip,T3.YTDTotSales,
T3.YTDPubProfit
T1.FirstName As SalesContactFirstName,
T1.LastName As SalesContactLastName,
T1.EmailAddress,
T2.SmanCode As SmanCode,
FROM arcusts T
Left Outer Join arcconts T1 On T.SalesContactID = T1.CustContactID
Left Outer Join arsmen T2 On T.SmanID = T2.SmanID
Left Outer Join arcsales T3 On T.CustID = T3.CustID
WHERE T3.FiscalYear=2012
and T2.SmanCode='Salesman1'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try keeping them in the join clause
SELECT
T.CustAcct,
T.CustomerName,
T.Address1,
T.Address2,
T.City,
T.State,
T.Zip,T3.YTDTotSales,
T3.YTDPubProfit
T1.FirstName As SalesContactFirstName,
T1.LastName As SalesContactLastName,
T1.EmailAddress,
T2.SmanCode As SmanCode,
FROM arcusts T
Left Outer Join arcconts T1 On T.SalesContactID = T1.CustContactID
Left Outer Join arsmen T2 On T.SmanID = T2.SmanID
Left Outer Join arcsales T3 On T.CustID = T3.CustID
and T3.FiscalYear=2012
and T2.SmanCode='Salesman1'