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

ahmed salah 3,216 Reputation points
2021-02-18T02:02:42.807+00:00

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

69326-paintforissue.png

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,105 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
{count} votes

3 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,576 Reputation points
    2021-02-18T03:23:19.513+00:00

    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:
    69249-image.png

    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.


  2. ahmed salah 3,216 Reputation points
    2021-02-18T04:09:34.2+00:00

    69297-a3anda4.png

    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


  3. EchoLiu-MSFT 14,576 Reputation points
    2021-02-18T05:51:56.793+00:00

    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
    
    0 comments No comments