question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked GuoxiongYuan-7218 answered

Update Lowest RN

How can I update ONLY the lowest value of rn in my sample DDL below?

 Declare @@policedata Table
 (
     ordernumber varchar(100)
     ,sc varchar(50)
     ,rn int
 )
    
 Insert Into @@PoliceData Values
 ('abc123', NULL, 1), ('abc123', NULL, 2), ('abc123', NULL, 3)
 ,('lmn123', NULL, 1), ('lmn123', NULL, 2)
    
 Declare @@supdata Table
 (
     ordernumber varchar(100)
     ,sc varchar(50)
     ,sc1 varchar(50)
     ,odate datetime2
 )
    
 Insert Into @@supdata Values
 ('abc123', '10', '0', '2020-01-20 10:31:58.9370000')
 ,('lmn123', '0', '40', '2020-01-20 10:31:58.9370000')
    
 UPDATE pd
 SET pd.sc = sd.sscc
 FROM @@policedata
 JOIN (
         Select
         ordernumber
         ,SUM(Coalesce(case when CAST(sc As Decimal(16,4)) = 0 THEN Cast(sc1 As Decimal(16,4)) End, Cast(sc1 As Decimal(16,4))) As sscc
         FROM @@supdata 
         WHERE CAST(odate As Date) BETWEEN '2020-01-01' AND '2020-01-30'
         GROUP BY ordernumber) sd
 ON pd.OrderNumber = sd.ordernumber
 --this is where i'm not sure what to write
 WHERE MIN(pd.rn)

MS SQL Server 2016

sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is the output you expect based on your sample data?

0 Votes 0 ·

@GuoxiongYuan-7218 - the lowest rn for each should be updated with either sc or sc1 (whichever is not 0 or not null) this is my desired result

('abc123', '10', 1)
,('lmn123', '40', 1)

0 Votes 0 ·

You can say it directly:

WHERE pd.rn = (select min(rn) from @@policedata where ordernumber = pd.ordernumber)

Fix the rest of the statement according to your needs.

Maybe you can also write WHERE pd.rn = 1.

0 Votes 0 ·
Show more comments
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

Try this:

 UPDATE p
 SET p.sc = COALESCE(NULLIF(s.sc, 0), NULLIF(s.sc1, 0))
 FROM (
     SELECT *, ROW_NUMBER() OVER(PARTITION BY ordernumber ORDER BY rn ASC) AS rn_by_ordernumber
     FROM @@policedata 
 ) AS p
 INNER JOIN @@supdata AS s ON p.ordernumber = s.ordernumber
 WHERE p.rn_by_ordernumber = 1;
    
 SELECT * FROM @@policedata;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered RyanAbbey-0701 commented

Maybe this. (I simplified the SUM in the derived table, because it did not compile, and it seemed unnecessarily complex.) I'm assuming that rn is per ordernumber.

; WITH CTE_policedata AS (
         SELECT sc, ordernumber, 
                rowno = row_number() OVER(PARTITION BY ordernumber ORDER BY rn)
         FROM   @@policedata
     )
     UPDATE pd
     SET pd.sc = sd.sscc
     FROM CTE_policedata  pd
     JOIN (
             Select
             ordernumber, SUM(Cast(sc1 As Decimal(16,4))) As sscc
             FROM @@supdata 
             WHERE CAST(odate As Date) BETWEEN '2020-01-01' AND '2020-01-30'
             GROUP BY ordernumber) sd
     ON pd.ordernumber = sd.ordernumber
   WHERE pd.rowno = 1

`

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ErlandSommarskog - What my SUM() was doing was updating to the value of sc, but if sc = 0 then updating to the value of sc1.

When I run your query it updates lmn123 twice but does not update abc123? Below is the result set I get.
ordernumber sc rn abc123 0.0000 1 abc123 0.0000 2 abc123 0.0000 3 lmn123 40.0000 1 lmn123 40.0000 2

My desired update is to ONLY update the lowest rn for each ordernumber

0 Votes 0 ·

My desired update is to ONLY update the lowest rn for each ordernumber

Oops! I forgot to add the WHERE clause. My apologies! I've edited my original post.

0 Votes 0 ·

@ErlandSommarskog - we are very very close now!

But i need to first want to check the value of @@supdata.sc if that is null or 0 then I want to update to @@supdata.sc1

0 Votes 0 ·
Show more comments