How to get zplid that have two code type one code type have Null on zfeature key and another have value only without null ?

ahmed salah 3,216 Reputation points
2020-12-13T15:41:33.127+00:00

I work on sql server 2012 . I face issue I can't get zplid from table #gen that have Null only zfeature key on code type and another have value only

on zfeaturekey based on zplid and codetypeid .

meaning I need to get rows that have NULL ON zfeaturekey and Values on Zfeaturekey but must be different code type but same zplid .

 create table #gen
         (
         CodeTypeId int,
         Zplid  int,
         Zfeaturekey nvarchar(50)
         )
         insert into #gen values
 (854838,25820,NULL),
 (849774,25820,1502260001),
 (849774,25820,1502260016),

 (854890,28700,NULL),
 (849780,28700,1502260013),
 (849780,28700,1502260030),

 (987431,26777,1502270003),
 (987431,26777,1502280005),
 (987431,26777,1502290001),

 (987471,26720,NULL),
 (987490,26720,1502280005),
 (987490,26720,NULL)

Expcted Result :

 CodeTypeId    Zplid    Zfeaturekey
 854838    25820    NULL
 849774    25820    1502260001
 849774    25820    1502260016
 854890    28700    NULL
 849780    28700    1502260013
 849780    28700    1502260030

result will be zplid 25820 and 28700 because it have more than on code type code type 854890 and have null on zfeature key

and code type 849774 and not have null only value

i will not get zplid 26777 because it Not have NULL ON zfeaturekey

i will not get zplid 26720 because it have NULL ON BOTH Code Type ON zfeaturekey

so how to solve that please ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,955 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,651 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 118K Reputation points
    2020-12-13T16:05:10.493+00:00

    Check if this query works for you:

    ;
    with E as
    (
        select *,
            count(*) over (partition by Zplid) c,
            count(Zfeaturekey) over (partition by Zplid) nn
        from #gen
    )
    select CodeTypeId, Zplid, Zfeaturekey
    from E
    where c-nn = 1 and nn > 0
    order by Zplid, CodeTypeId
    
    0 comments No comments

  2. MelissaMa-MSFT 24,201 Reputation points
    2020-12-14T03:17:39.627+00:00

    Hi @ahmed salah ,

    Please also refer below query:

    ;with cte as (  
    select zplid,count(distinct CodeTypeId) count,  
    sum(iif(Zfeaturekey is null,1,0)) sumnull  
    from #gen group by Zplid)  
    select * from #gen  
    where Zplid in (select Zplid from cte where count>1 and sumnull=1)  
    

    Output:

    CodeTypeId Zplid Zfeaturekey  
    854838 25820 NULL  
    849774 25820 1502260001  
    849774 25820 1502260016  
    854890 28700 NULL  
    849780 28700 1502260013  
    849780 28700 1502260030  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.