Show data as null or empty when using a join and one table has no records

Question

I am trying to write a query to extract some sales info, and I need to pull all customers for a particular person even if they have no sales for a month.  For example, I am using the following query to try and just pull the sales data for 2 customers for this one person:

select st.custid, st.shiptoid, st.custname, st.outsideslsrep, substring(st.zip,1,5) as zip, 
       datepart(month,ohi.invoicedate) as invmonth, sum(ohi.r_sales) as sales
from shipto st
left outer join order_header_invoice ohi
on st.custid = ohi.custid and
  st.shiptoid = ohi.shiptoid
where st.outsideslsrep = '114' and st.shiptoid <> '' and st.custid in ('1012','380') and
      ohi.invoicedate between '01/01/12' and GETDATE()
group by st.custid, st.shiptoid, st.custname, st.outsideslsrep, st.zip, datepart(month,ohi.invoicedate)

This will get me the following results:

custid shiptoid custname       outsideslsrep     zip           invmonth     sales

380 001 Customer1     114                    77777       1                 152.26

380 001 Customer1     114                    77777       2                 752.25

380 001 Customer1     114                    77777       3                 412.25

380 001 Customer1     114                    77777       4                 300.58

In my query I am trying to get the sales for the first 4 months of this year for customers 380 and also 1012.  I know that the 1012 customer does exist in the "st" table, but I also know that this customer has no sales for months 1 through 4, so no records are in the "ohi" table for these months.  I need to have all the same info for each customer, but show either "0" or null in the sales column for customer 1012.  This is what I would want to see in the results:

custid shiptoid custname       outsideslsrep     zip           invmonth     sales

380 001 Customer1     114                    77777       1                 152.26

380 001 Customer1     114                    77777       2                 752.25

380 001 Customer1     114                    77777       3                 412.25

380 001 Customer1     114                    77777       4                 300.58

1012 001 Customer2     114                    77456       1                 0

1012    001 Customer2     114                    77456       2                 0

1012 001 Customer2     114                    77456       3                 0

1012 001 Customer2     114                    77456       4                 0

If there is anyone that can help me do this I would greatly appreciate it.

Thank you!

Answer 1

Hi,

Please post your DDL along with some sample data + the expected output. It will help us to provide quality help, also we do not know your db schema :)

eg: CREATE TABLE....

INSERT INTO table1....

etc.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

In order to get the desired output, we first need to create a cross join between all Customers and first 4 months, e.g.

;with AllCombinations as (select C.CustID, C.ShipToID, C.CustName, C.OutsideSlsRep, C.Zip, M.InvMonth from Customers C

CROSS JOIN (select 1 as InvMonth UNION ALL SELECT 2 as InvMonth UNION ALL 3 as InvMonth UNION ALL Select 4 as InvMonth) M)

select AC.*, S.Sales from AllCombinations AC LEFT JOIN (select sum(sales) as Sales, CustID, InvMonth from Sales GROUP BY InvMonth, CustID) S

ON AC.InvMonth = S.InvMonth and AC.CustID = S.CustID

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Hard to estimate the outcome due to many missing details, but I'm curious if this works

SELECT  st.custid,
        st.shiptoid,
        st.custname,
        st.outsideslsrep,
        SUBSTRING(st.zip, 1, 5) AS zip,
        DATEPART(month, ohi.invoicedate) AS invmonth,
        SUM(ohi.r_sales) AS sales
FROM    shipto st
        LEFT OUTER JOIN ( SELECT  *
                          FROM    order_header_invoice
                          WHERE   invoicedate BETWEEN '01/01/12' AND GETDATE() ) ohi ON st.custid = ohi.custid AND
                                                                                            st.shiptoid = ohi.shiptoid
WHERE   st.outsideslsrep = '114' AND
        st.shiptoid <> '' AND
        st.custid IN ( '1012', '380' )
GROUP BY st.custid,
        st.shiptoid,
        st.custname,
        st.outsideslsrep,
        st.zip,
        DATEPART(month, ohi.invoicedate)

Answer 4

I think this may work, I will give it a try now.  Thank you!

Answer 5

Thanks again, this did work perfectly for me!