Share via

Amend Flag Description

SQL Baby 161 Reputation points
2021-07-28T03:03:27.633+00:00

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
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

Answer accepted by question author

  1. EchoLiu-MSFT 14,626 Reputation points
    2021-07-28T03:49:49.437+00:00

    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.

    Was this answer helpful?

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.