Post reply

How to get zplid that have two code type one code type have Null on zfeature key

  • December 13, 2020 at 3:45 pm

    #3819988

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

    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

  • December 13, 2020 at 11:25 pm

    #3820099 

    with zpl_cte(Zplid) as (
        select Zplid
        from #gen
        group by Zplid
        having count(*)=3
               and sum(iif(Zfeaturekey is null, 1, 0)=1)
    select g.*
    from #gen g
         join zpl_cte z on g.Zplid=z.Zplid
    order by g.Zplid, g.CodeTypeId desc, g.Zfeaturekey;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply