question

SQLBaby-8546 avatar image
0 Votes"
SQLBaby-8546 asked SQLBaby-8546 commented

Amend Flag Description

Am trying to achieve the following for each ItemID grouping:

  1. If there is a RepairType = 11 and no prior RepairType = 89 then the RepairReason for RepairType = 11 is called ‘New’

  2. If there is a RepairType = 11 and a prior RepairType = 89 then the RepairReason for RepairType = 89 is called ‘New’ BUT the RepairReason for RepairType = 11 is called ‘Activation’

Either [ItemNo] OR [TransactionDate] columns can be used to determine the repair order, that is which repair came first.

Thank you

Data before update

118470-image.png


Expected Output
118330-image.png




 --Create Test Table & Data
 DECLARE @REPAIRS TABLE (
 RowID int NULL ,
 ItemID Int NULL,
 ItemNo Int NULL,
 ItemRepairID int NULL ,
 TransactionDate DateTime NULL,
 RepairType nVarchar(50) NULL,
 RepairReason nVarchar(50) NULL
 )
    
 INSERT INTO  @REPAIRS  (RowID, ItemID, ItemNo, ItemRepairID,TransactionDate,RepairType, RepairReason)
    
 SELECT 43,12298333,1,12341443,cast('2015-03-18 10:51:25.620' as Datetime),11,'New' UNION ALL
 SELECT 44,12298333,2,12341443,cast('2015-06-26 13:22:43.923' as Datetime),61,'Cancellation' UNION ALL
 SELECT 45,12298333,3,12341443,cast('2016-10-19 11:56:17.350' as Datetime),42,'Reactivation' UNION ALL
 SELECT 46,12298333,4,12341443,cast('2018-01-08 14:41:09.740' as Datetime),72,'Latest' UNION ALL
 SELECT 47,12298333,5,12341443,cast('2018-01-08 14:44:54.960' as Datetime),72,'Latest' UNION ALL
 SELECT 48,12298333,6,12341443,cast('2019-11-20 09:08:53.373' as Datetime),21,'Latest' UNION ALL
 SELECT 49,12298335,1,12341445,cast('2015-03-18 10:51:25.913' as Datetime),11,'New' UNION ALL
 SELECT 50,12298335,2,12341445,cast('2015-06-26 13:22:43.883' as Datetime),61,'Cancellation' UNION ALL
 SELECT 51,12298335,3,12341445,cast('2016-10-19 11:56:17.330' as Datetime),42,'Reactivation' UNION ALL
 SELECT 52,12298335,4,12341445,cast('2018-01-08 14:41:09.767' as Datetime),72,'Latest' UNION ALL
 SELECT 53,12298335,5,12341445,cast('2018-01-08 14:44:54.980' as Datetime),72,'Latest' UNION ALL
 SELECT 54,12298335,6,12341445,cast('2018-03-21 12:44:57.003' as Datetime),21,'Latest' UNION ALL
 SELECT 55,12298335,7,12341445,cast('2019-11-20 09:08:53.420' as Datetime),21,'Latest' UNION ALL
 SELECT 307,12488022,1,12531132,cast('2015-06-23 15:27:00.903' as Datetime),89,'New' UNION ALL
 SELECT 308,12488022,2,12531132,cast('2016-10-19 05:30:15.763' as Datetime),61,'Cancellation' UNION ALL
 SELECT 309,12488022,3,12531132,cast('2016-10-19 11:56:03.363' as Datetime),42,'Reactivation' UNION ALL
 SELECT 310,12488022,4,12531132,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
 SELECT 311,12488022,5,12531132,cast('2019-07-14 15:36:00.763' as Datetime),11,'New' UNION ALL
 SELECT 312,12488024,1,12531134,cast('2015-06-23 15:27:01.977' as Datetime),89,'New' UNION ALL
 SELECT 313,12488024,2,12531134,cast('2016-10-19 05:30:15.770' as Datetime),61,'Cancellation' UNION ALL
 SELECT 314,12488024,3,12531134,cast('2016-10-19 11:56:03.443' as Datetime),42,'Reactivation' UNION ALL
 SELECT 315,12488024,4,12531134,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
 SELECT 316,12488024,5,12531134,cast('2017-04-30 05:30:01.723' as Datetime),11,'New' UNION ALL
 SELECT 317,12488024,6,12531134,cast('2017-08-30 14:56:09.990' as Datetime),21,'Latest' UNION ALL
 SELECT 318,12488024,7,12531134,cast('2018-01-08 14:28:30.120' as Datetime), 21,'Latest' UNION ALL
 SELECT 319,12488024,8,12531134,cast('2019-04-30 21:01:29.453' as Datetime),NULL,'Expiration' UNION ALL
 SELECT 327,12488041,1,12531151,cast('2015-06-23 15:27:10.877' as Datetime),11,'New' UNION ALL
 SELECT 328,12488041,2,12531151,cast('2016-10-19 05:30:15.840' as Datetime),61,'Cancellation' UNION ALL
 SELECT 329,12488041,3,12531151,cast('2016-10-19 11:56:03.533' as Datetime),42,'Reactivation' UNION ALL
 SELECT 330,12488041,4,12531151,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
 SELECT 331,12488041,5,12531151,cast('2017-07-26 20:50:54.970' as Datetime),89,'New' UNION ALL
 SELECT 352,12488052,1,12531162,cast('2015-06-23 15:27:13.510' as Datetime),89,'New' UNION ALL
 SELECT 353,12488052,2,12531162,cast('2016-10-19 05:30:15.883' as Datetime),61,'Cancellation' UNION ALL
 SELECT 354,12488052,3,12531162,cast('2016-10-19 11:56:03.650' as Datetime),42,'Reactivation' UNION ALL
 SELECT 355,12488052,4,12531162,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
 SELECT 356,12488052,5,12531162,cast('2018-05-03 20:28:46.147' as Datetime),11,'New' UNION ALL
 SELECT 357,12488052,6,12531162,cast('2019-11-20 09:07:17.743' as Datetime),21,'Latest' UNION ALL
 SELECT 368,12488074,1,12531184,cast('2015-06-23 15:27:19.490' as Datetime),89,'New' UNION ALL
 SELECT 369,12488074,2,12531184,cast('2016-10-19 05:30:15.963' as Datetime),61,'Cancellation' UNION ALL
 SELECT 370,12488074,3,12531184,cast('2016-10-19 11:56:03.850' as Datetime),42,'Reactivation' UNION ALL
 SELECT 371,12488074,4,12531184,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
 SELECT 372,12488074,5,12531184,cast('2018-11-12 12:41:01.997' as Datetime),11,'New' 
    
 SELECT * FROM @REPAIRS
sql-server-transact-sql
image.png (78.1 KiB)
image.png (81.5 KiB)
· 1
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.

I have tried this but is there a better approach than this:
as the tables i'll be updating have 10s of millions of rows!
Select *
FROM @REPAIRS as s1
WHERE RepairType = '11' AND EXISTS
(
Select 1
FROM @REPAIRS s2
WHERE s1.ItemID = s2.ItemID
AND RepairType = '89'
AND s1.TransactionDate > s2.TransactionDate
)

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered SQLBaby-8546 commented

Please try:

  --Create Test Table & Data
 CREATE  TABLE REPAIRS(
  RowID int NULL ,
  ItemID Int NULL,
  ItemNo Int NULL,
  ItemRepairID int NULL ,
  TransactionDate DateTime NULL,
  RepairType nVarchar(50) NULL,
  RepairReason nVarchar(50) NULL
  )
        
  INSERT INTO  REPAIRS  (RowID, ItemID, ItemNo, ItemRepairID,TransactionDate,RepairType, RepairReason)
        
  SELECT 43,12298333,1,12341443,cast('2015-03-18 10:51:25.620' as Datetime),11,'New' UNION ALL
  SELECT 44,12298333,2,12341443,cast('2015-06-26 13:22:43.923' as Datetime),61,'Cancellation' UNION ALL
  SELECT 45,12298333,3,12341443,cast('2016-10-19 11:56:17.350' as Datetime),42,'Reactivation' UNION ALL
  SELECT 46,12298333,4,12341443,cast('2018-01-08 14:41:09.740' as Datetime),72,'Latest' UNION ALL
  SELECT 47,12298333,5,12341443,cast('2018-01-08 14:44:54.960' as Datetime),72,'Latest' UNION ALL
  SELECT 48,12298333,6,12341443,cast('2019-11-20 09:08:53.373' as Datetime),21,'Latest' UNION ALL
  SELECT 49,12298335,1,12341445,cast('2015-03-18 10:51:25.913' as Datetime),11,'New' UNION ALL
  SELECT 50,12298335,2,12341445,cast('2015-06-26 13:22:43.883' as Datetime),61,'Cancellation' UNION ALL
  SELECT 51,12298335,3,12341445,cast('2016-10-19 11:56:17.330' as Datetime),42,'Reactivation' UNION ALL
  SELECT 52,12298335,4,12341445,cast('2018-01-08 14:41:09.767' as Datetime),72,'Latest' UNION ALL
  SELECT 53,12298335,5,12341445,cast('2018-01-08 14:44:54.980' as Datetime),72,'Latest' UNION ALL
  SELECT 54,12298335,6,12341445,cast('2018-03-21 12:44:57.003' as Datetime),21,'Latest' UNION ALL
  SELECT 55,12298335,7,12341445,cast('2019-11-20 09:08:53.420' as Datetime),21,'Latest' UNION ALL
  SELECT 307,12488022,1,12531132,cast('2015-06-23 15:27:00.903' as Datetime),89,'New' UNION ALL
  SELECT 308,12488022,2,12531132,cast('2016-10-19 05:30:15.763' as Datetime),61,'Cancellation' UNION ALL
  SELECT 309,12488022,3,12531132,cast('2016-10-19 11:56:03.363' as Datetime),42,'Reactivation' UNION ALL
  SELECT 310,12488022,4,12531132,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
  SELECT 311,12488022,5,12531132,cast('2019-07-14 15:36:00.763' as Datetime),11,'New' UNION ALL
  SELECT 312,12488024,1,12531134,cast('2015-06-23 15:27:01.977' as Datetime),89,'New' UNION ALL
  SELECT 313,12488024,2,12531134,cast('2016-10-19 05:30:15.770' as Datetime),61,'Cancellation' UNION ALL
  SELECT 314,12488024,3,12531134,cast('2016-10-19 11:56:03.443' as Datetime),42,'Reactivation' UNION ALL
  SELECT 315,12488024,4,12531134,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
  SELECT 316,12488024,5,12531134,cast('2017-04-30 05:30:01.723' as Datetime),11,'New' UNION ALL
  SELECT 317,12488024,6,12531134,cast('2017-08-30 14:56:09.990' as Datetime),21,'Latest' UNION ALL
  SELECT 318,12488024,7,12531134,cast('2018-01-08 14:28:30.120' as Datetime), 21,'Latest' UNION ALL
  SELECT 319,12488024,8,12531134,cast('2019-04-30 21:01:29.453' as Datetime),NULL,'Expiration' UNION ALL
  SELECT 327,12488041,1,12531151,cast('2015-06-23 15:27:10.877' as Datetime),11,'New' UNION ALL
  SELECT 328,12488041,2,12531151,cast('2016-10-19 05:30:15.840' as Datetime),61,'Cancellation' UNION ALL
  SELECT 329,12488041,3,12531151,cast('2016-10-19 11:56:03.533' as Datetime),42,'Reactivation' UNION ALL
  SELECT 330,12488041,4,12531151,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
  SELECT 331,12488041,5,12531151,cast('2017-07-26 20:50:54.970' as Datetime),89,'New' UNION ALL
  SELECT 352,12488052,1,12531162,cast('2015-06-23 15:27:13.510' as Datetime),89,'New' UNION ALL
  SELECT 353,12488052,2,12531162,cast('2016-10-19 05:30:15.883' as Datetime),61,'Cancellation' UNION ALL
  SELECT 354,12488052,3,12531162,cast('2016-10-19 11:56:03.650' as Datetime),42,'Reactivation' UNION ALL
  SELECT 355,12488052,4,12531162,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
  SELECT 356,12488052,5,12531162,cast('2018-05-03 20:28:46.147' as Datetime),11,'New' UNION ALL
  SELECT 357,12488052,6,12531162,cast('2019-11-20 09:07:17.743' as Datetime),21,'Latest' UNION ALL
  SELECT 368,12488074,1,12531184,cast('2015-06-23 15:27:19.490' as Datetime),89,'New' UNION ALL
  SELECT 369,12488074,2,12531184,cast('2016-10-19 05:30:15.963' as Datetime),61,'Cancellation' UNION ALL
  SELECT 370,12488074,3,12531184,cast('2016-10-19 11:56:03.850' as Datetime),42,'Reactivation' UNION ALL
  SELECT 371,12488074,4,12531184,cast('2016-10-19 12:06:05.707' as Datetime),NULL,NULL UNION ALL
  SELECT 372,12488074,5,12531184,cast('2018-11-12 12:41:01.997' as Datetime),11,'New' 
        
    
    
 ;WITH cte
 as(SELECT *,ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY [ItemNo],[TransactionDate] ) rr
 FROM REPAIRS
 WHERE RepairType=11 or RepairType=89)
 ,CTE2 AS(SELECT C1.*,C2.RepairType RepairType2,COUNT(c1.RepairType) OVER(PARTITION BY c1.ItemID) cc
 FROM cte c1
 LEFT JOIN cte c2 on c1.ItemID=c2.ItemID 
 and c1.rr+1=c2.rr)
    
 UPDATE cte2
 SET RepairReason=CASE WHEN RepairType=11 AND cc=1 THEN 'New'
 WHEN cc=2 AND RepairType>RepairType2 AND RepairType2 IS NOT NULL THEN 'New'
 WHEN cc=2 AND RepairType=11 AND RepairType2 IS NULL THEN 'Activation'
 ELSE RepairReason END
 FROM cte2 
    
 SELECT * FROM REPAIRS
 WHERE RepairType=11 or RepairType=89
 ORDER BY ItemID
    
 DROP TABLE REPAIRS

Output:
118458-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.



image.png (30.8 KiB)
· 3
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.

Thank you Echo.
I'll mark your answer as solution.

Do you see any issues with the way i was going to perform this update?

 UPDATE @REPAIRS
 SET RepairReason = 'Activation'
 --Select *
 FROM @REPAIRS as s1
 WHERE RepairType = '11' AND EXISTS
                         (
                         Select 1
                         FROM @REPAIRS s2
                               WHERE          s1.ItemID = s2.ItemID
                                               AND RepairType = '89'
                                               AND  s1.TransactionDate > s2.TransactionDate
                         )
    
    
 Select * FROM @REPAIRS
0 Votes 0 ·

If your original data is the same as the test data you provided (the initial value of RepairReason for RepairType=11 or RepairType=89 is New), then your code is correct.

The method I provide can get the desired output regardless of whether the original value is New or not.

Hope this can help you.

Regards
Echo

1 Vote 1 ·

Thanks for your help.

0 Votes 0 ·