Solved

How to exclude customers from showing if they have a specific package

Posted on 2011-02-10
11
264 Views
Last Modified: 2012-05-11
Ok I will try and be as clear as possible.  I have two tables one is pp and one is cp.  I have one entry field is called classnum in the PP table.  The CP table is my customer number.  For some reason the code I have now is still pulling the wrong results and I am sure I have something wrong.  

The results I am trying to obtain:  Show me all customers (custnum) that does not have any packages that have a class of 17.

Each package in this PP table has different classnum.  

The results I am getting:  It is showing the unique customer numbers but it is showing me ALL unique customer numbers.  Currently it is pulling 1558 rows which is my customer count.  If the query is correct it should only pull two.  As I know for a fact there are two unique customers with a package class of 17.  

I have attached the query that I have now.
SELECT DISTINCT TOP (100) PERCENT cp.custnum
FROM         KC_Freeside.dbo.cust_pkg AS cp INNER JOIN
                      KC_Freeside.dbo.part_pkg AS pp ON pp.pkgpart = cp.pkgpart
WHERE     (cp.cancel IS NULL) AND (pp.classnum <> 17) AND (pp.freq <> '0')
ORDER BY cp.custnum

Open in new window

0
Comment
Question by:binaryman101
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 34867984

SELECT cp.custnum
FROM KC_Freeside.dbo.cust_pkg AS cp LEFT JOIN
     KC_Freeside.dbo.part_pkg AS pp ON pp.pkgpart = cp.pkgpart AND pp.classnum = 17
WHERE cp.cancel IS NULL AND pp.pkgpart IS NULL
ORDER BY cp.custnum

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34868048
Just a small addition to the previous solution (no points please):
SELECT  DISTINCT cp.custnum
FROM    KC_Freeside.dbo.cust_pkg AS cp
        LEFT JOIN KC_Freeside.dbo.part_pkg AS pp ON pp.pkgpart = cp.pkgpart AND pp.classnum = 17 AND pp.freq <> '0'
WHERE   cp.cancel IS NULL
        AND pp.pkgpart IS NULL
ORDER BY cp.custnum

Also, notice how the TOP(100) PERCENT should not be used as it makes no sense.  The SQL QO is smart enough to see that you have TOP(100) PERCENT and promptly ignore the ORDER BY clause, so your results will not not sorted.
0
 

Author Comment

by:binaryman101
ID: 34868319
I am currently get 1682 rows now.  And the two that shouldn't be in the results are showing up in the results.  I also try and remove the TOP 100 PERCENT and it comes right back.  I am using SQL server management studio.  I am really confused why I can't get this to work.  
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 22

Expert Comment

by:Thomasian
ID: 34868360
And the two that shouldn't be in the results are showing up in the results.

Can you post all the records from tables cp and pp of the two results you mentioned?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34868424
ok from your original statement....


this query will return
those custnums which don't have any classnum 17 's


select distinct custnum
  from KC_Freeside.dbo.cust_pkg AS cp
 where not exists (select  
                     from KC_Freeside.dbo.part_pkg AS pp
                    where classnum=17
                      and cp.custnum=pp.custnum)
 order by 1


would you now like to explain what the other two conditions you where trying to apply mean in relation to the problem?
0
 

Author Comment

by:binaryman101
ID: 34874478
I have attached the data in tables to this spreadsheet.  Each tab is the data.

Custnum 3654 and 4306 should not show up because they are the only ones with classnum of 17.  Let me know if you need something else.  Thank you!
CP-PP.xls
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34875393
i think part of your problem is that the cancel column appears to contain the literal 'NULL'
and not an actual null


also there is an entry for 3654 with cancel='NULL' and the freq is 1


freq comes over as a float in the import...
0
 

Author Comment

by:binaryman101
ID: 34875604
How do I compensate for that?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34875830
what is the intention ?...

if all literal 'Nulls' should be database nulls then that is a trivial correction

 vis   update table
           set [cancel] = nullif([cancel],'Null')
        where [cancel] ='Null'


however as i indicated the other table for 3654 at least does have a valid classnum=17 entry...

didn't check the other one...

i'm not sure what datatype you intend for freq since you show a character test <> '0' but at least from
an import point of view that came over as a float...


testing on a smaller sample set  is probably advisable initially at least.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34876384
>>I also try and remove the TOP 100 PERCENT and it comes right back.  I am using SQL server management studio.  <<
That is because you are using the Design mode.  Don't do that or you will be frustrated to say the least.  Use the Query window instead and type in the SQL Statement there.
0
 

Author Closing Comment

by:binaryman101
ID: 34998485
Nulls were a problem.  I found a work around.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question