I have NULL results for thousands of rows. How do I exclude those from populating when they don't exist?

Question

I have a code to look at a sales employee of mine. He was recently hired to create growth on extinct customers. So over the 1,000+ customers we have in the dbase, he only will effect 100. So there have been a lot of rows generated where his value is NULL. I want the report to exclude those rows of customers so it is more efficient in understanding for my management. How would I accomplish this?

    select  T1.CardCode 'BP Code',
        T1.CardName 'BP Name',
        COUNT(CASE WHEN T1.SlpCode<>'37' THEN T0.DocNum END) '# of Orders',
        SUM(CASE WHEN T1.SlpCode<>'37' THEN T0.DocTotal END) 'Total Orders Amt',
        SUM(CASE WHEN T1.SlpCode<>'37' THEN T0.DOCTOTAL END)/
        COUNT(CASE WHEN T1.SlpCode<>'37' THEN T0.DocNum END) 'Avg Order Size',
        COUNT(CASE WHEN T1.SlpCode='37' THEN T0.DocNum END) '# of Orders',
        SUM(CASE WHEN T1.SlpCode='37' THEN T0.DocTotal END) 'Total Orders Amt',
        SUM(CASE WHEN T1.SlpCode='37' THEN T0.DOCTOTAL END)/
        COUNT(CASE WHEN T1.SlpCode='37' THEN T0.DocNum END) 'Avg Order Size'

from    ORDR T0 INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode

where   T0.DocStatus = 'C'
        AND T0.CANCELED <> 'Y'
        AND T0.DocDate >= '2015-05-26'

GROUP BY    T1.CardCode,
            T1.CardName

Order By    t1.CardName

paparazzo · Answer 1 · 2015-07-23T11:58:52.667

0

select tnot.*, tyes.*
from ( select OCRD.CardCode 'BP Code',
              OCRD.CardName 'BP Name',
              COUNT(ORDR.DocNum) '# of Orders',
              SUM(ORDR.DocTotal) 'Total Orders Amt'
         FROM OCRD
         LEFT JOIN ORDR 
           ON ORDR.CardCode= OCRD.CardCode 
          AND ORDR.CANCELED <> 'Y'
          AND ORDR.DocDate >= '2015-05-26'
          AND OCRD.SlpCode <> '37' 
     GROUP BY OCRD.CardCode,
              OCRD.CardName
     ) tnot    
join ( select OCRD.CardCode 'BP Code',
              OCRD.CardName 'BP Name',
              COUNT(ORDR.DocNum) '# of Orders',
              SUM(ORDR.DocTotal) 'Total Orders Amt'
         FROM OCRD
         JOIN ORDR 
           ON ORDR.CardCode= OCRD.CardCode 
          AND ORDR.CANCELED <> 'Y'
          AND ORDR.DocDate >= '2015-05-26'
          AND OCRD.SlpCode  = '37' 
     GROUP BY OCRD.CardCode,
              OCRD.CardName
     ) tyes 
    on tnot.[BP Code] = tyes.[BP Code]
   and tnot.[BP Name] = tyes.[BP Name]
 order by tyes.[BP Name]

edited Jul 23 '15 at 11:58

answered Jul 21 '15 at 22:05

paparazzo

44,497
23
105
176

Thanks for the reply Blam. I entered the query into my SQL Management Studio and received the following message: "Msg 1033, Level 15, State 1, Line 15 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Msg 102, Level 15, State 1, Line 28 Incorrect syntax near 'tyes'." – KTate Jul 23 '15 at 11:55
Did you try taking out the order by? – paparazzo Jul 23 '15 at 11:58
I did not. I just tried it now though and it gave me results. It generated customer accounts that the sales employee is the owner of with sales generated by other sales employees. I want to basically have the query I gave originally and have it trim out the NULL results. I do like this query though. Intriguing to see the sales that have happened on the sales employee's accounts that they didn't contribute too. – KTate Jul 23 '15 at 12:29
Don't get what you mean by null. You should only get row with OCRD.SlpCode = '37' – paparazzo Jul 23 '15 at 12:40
I think I may have answered my own question. I changed a part of the "WHERE" criteria. I adjusted it to `AND (T0.SlpCode = '37' OR T1.SlpCode = '37')` That is giving me 12 rows of data. That will give me the customers where #37 is the account rep and also accounts he is not the account rep but made a sale since his date of hire. – KTate Jul 23 '15 at 13:32

I have NULL results for thousands of rows. How do I exclude those from populating when they don't exist?

1 Answers1