Post reply

How to get Parts have two different values on source ?

  • February 28, 2022 at 8:45 am

    #3990080

    I work on sql server 2012 i need to get part id that have two different source type per same part .

    but part have two different source type per part

    and it must have one source type from two source type equal 8901 .

    sample data

    create table #temp
         (
         partid int,
         sourcetypeid int
         )
         insert into #temp(partid,sourcetypeid)
         values
         (1290,5012),
         (1290,5012),
         (1290,8901),
         (3501,5402),
         (3501,74430),
         (7001,8901),
         (7321,8900),
         (2040,5090),
         (2040,5400),
         (7321,7400),
         (9110,8901),
         (9110,8901)

    what i try

    select partid from #temp
     where sourcetypeid=8901
     group by partid
     having count(distinct sourcetypeid)=2

    but it return null

    expected result

  • February 28, 2022 at 3:33 pm

    #3990472

    Seems similar to the other recent question

    with parts_cte(partid) as (
        select partid 
        from #temp
        group by partid
        having count(distinct sourcetypeid)=2
               and sum(iif(sourcetypeid=8901, 1, 0))>=1)
    select t.*
    from #temp t
         join parts_cte p on t.partid=p.partid;

    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