How to update priority level from A1 to A4 Based on source Type Distributor AND Source Code ?

Question

How to update priority level from A1 to A4 Based on source Type Distributor and Source Code ?

I work on SQL Server 2012 I face issue I can't update Priority level From A1 to A4

Based Source Code And Source Type Distributor

SO I need to make query do update cases as below

update m set PriorityLevel = 'A1' from #NotMappedSource
Where distinct source type=3 and source type Distributor
have distinct source name bigger than 1
and this will be A1 for priority level

update m set PriorityLevel = 'A2' from #NotMappedSource
Where distinct source type=3 and source type Distributor
have distinct source name = 1
and this will be A2 for priority level

update m set PriorityLevel = 'A3' from #NotMappedSource
Where distinct source type=2 and source type Distributor or not
and NOT have source name is 0 for Distributor
and this will be A3 for priority level

update m set PriorityLevel = 'A4' from #NotMappedSource
Where distinct source type=1 and source type is Distributor or not
and source name is 0 for Distributor
and this will be A3 for priority level

create table #NotMappedSource
(
SourceCode int,
SourceType nvarchar(50),
SourceName nvarchar(50),
PriorityLevel nvarchar(10)
)
insert into #NotMappedSource(SourceCode,SourceType,SourceName,PriorityLevel)
values
(1300,'Distributor','Z1Source',NULL),
(1300,'Distributor','Z2Source',NULL),
(1300,'Supplier','Z2Source',NULL),
(1300,'Aggregator','Z3Source',NULL),

(1400,'Distributor','Z1Source',NULL),  
(1400,'Supplier','Z1Source',NULL),  
(1400,'Aggregator','Z1Source',NULL),  
  
(1500,'Supplier','Z2Source',NULL),  
(1500,'Aggregator','Z2Source',NULL),  
  
(1550,'Supplier','Z2Source',NULL),  
(1550,'Distributor',NULL,NULL),  
  
(1600,'Supplier','Z3Source',NULL),  
(1800,'Aggregator','Z2Source',NULL),  
(1900,'Distributor',NULL,NULL)  

Expected Result

SourceCode SourceType SourceName PriorityLevel  
1300 Distributor Z1Source A1  
1300 Distributor Z2Source A1  
1300 Supplier Z2Source A1  
1300 Aggregator Z3Source A1  
1400 Distributor Z1Source A2  
1400 Supplier Z1Source A2  
1400 Aggregator Z1Source A2  
1500 Supplier Z2Source A3  
1500 Aggregator Z2Source A3  
1550 Supplier Z2Source A3  
1550 Distributor NULL A3  
1600 Supplier Z3Source A4  
1800 Aggregator Z2Source A4  
1900 Distributor NULL A4  

for more details

Answer 1

Tty:

    ;WITH cte  
    as(  
    SELECT sourcecode,count(distinct sourcetype) r1  
    FROM #NotMappedSource  
    group by sourcecode)  
    ,cte2 as(  
    SELECT sourcecode,count(distinct sourcename) r2  
    FROM #NotMappedSource  
    where sourcetype='Distributor'  
    group by sourcecode)  
        
    update m   
    set PriorityLevel=case when r1=3  and r2>1 then 'A1'  
                           when r1=3  and r2=1 then 'A2'  
                    when r1=2  and r2=0 then 'A3'  
                    when r1=1  and r2=0 then 'A4' end  
    from #NotMappedSource m  
    join cte c1 on m.sourcecode=c1.sourcecode  
    join cte2 c2 on m.sourcecode=c2.sourcecode  

Output:

Regards
Echo

---

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.

Answer 2

if any distinct source type equal 2 and and not have distributor
and have value source name it must be A3
BECAUSE it not have distributor and have 0 on source names
because it not exist

if any distinct source type equal 1 and and not have distributor
and have value source name it must be A4
BECAUSE it not have distributor and have 0 on source names
because it not exist

Answer 3

Try:

    ;WITH cte
     as(
     SELECT sourcecode,count(distinct sourcetype) r1
     FROM #NotMappedSource
     group by sourcecode)
     ,cte2 as(
     SELECT sourcecode,count(distinct sourcename) r2
     FROM #NotMappedSource
     where sourcetype='Distributor'
     group by sourcecode)

     update m 
     set PriorityLevel=case when r1=3  and r2>1 then 'A1'
                            when r1=3  and r2=1 then 'A2'
                     when r1=2  and (r2=0 or r2 is null) then 'A3'
                     when r1=1 and (r2=0 or r2 is null) then 'A4' end
     from #NotMappedSource m
     join cte c1 on m.sourcecode=c1.sourcecode
     left join cte2 c2 on m.sourcecode=c2.sourcecode