SQL Server How Can I set the right flag according to row status

mgdq6dx1  于 11个月前  发布在  其他
关注(0)|答案(1)|浏览(172)

I want to flag packages that weren't collected by customers for each relay point.

To identify a left package (not collected by customer) the status need to be ("Dropoff" or "preparation")

Requirements :
| Flag Cases | Example |
| ------------ | ------------ |
| The row needs to be the first row with status ID in (20,71) | Rows 7,34 |
| The row needs to be the first row with status ID in (20,71) Following a row with status in (50,51,90,91,30,60,10) | Rows 3,10,27.. |

The expected result :

Line NumberOrderNumberRelayPointStatusDateShipperFID Status
2XN75NULLShipment1/27/2022 15:29010
3XN75123Dropoff2/1/2022 7:15120
4XN75123Relay point Collection2/2/2022 7:50090
5XN75123Collected2/2/2022 7:50091
6XN7545678Shipper2/2/2022 16:02030
7XN76234Dropoff4/1/2022 9:16120
8XN76234Dropoff4/1/2022 9:17020
9XN77980Shipper Delivery9/22/2022 9:23050
10XN77980Preparation9/29/2022 23:00171
11XN77980Shipper Delivery10/1/2022 10:48050
12XN77980Preparation10/11/2022 16:48171
13XN811314Dropoff10/10/2022 13:45120
14XN811314Relay point Delivery10/11/2022 13:46051
15XN811314Preparation10/11/2022 15:45171
16XN811314Pickup10/12/2022 8:46060
17XN79NULLShipment9/25/2022 20:38010
18XN79979Dropoff10/1/2022 9:16120
19XN79979Relay point Collection10/3/2022 10:14090
20XN79198271Shipper10/4/2022 15:05030
21XN79154Shipper Delivery10/9/2022 10:42050
22XN79154Relay point Delivery10/9/2022 10:43051
23XN79154Pickup10/9/2022 14:08060
24XN79711Dropoff10/10/2022 13:45120
25XN79108223Shipper10/11/2022 13:45030
26XN80980Shipper Delivery9/22/2022 9:23050
27XN80980Preparation9/29/2022 23:00171
28XN80980Shipper Delivery10/1/2022 10:48050
29XN80980Preparation10/11/2022 16:48171
30XN80980Shipper Delivery10/12/2022 10:48050
31XN80980Preparation10/13/2022 16:48171
32XD30340Dropoff11/17/2022 12:29120
33XD30340Pickup11/17/2022 12:48060
34XD30340Dropoff11/20/2022 18:24120
35XD30340Preparation11/20/2022 18:25071
36XD30340Collected11/20/2022 18:26091

I tried the query as below but it doesn't give the right result.

;WITH cte
     AS (SELECT *,Row_number() OVER (partition BY ordernumber, relaypoint ORDER BY dateshipper) AS RN
         FROM   #shipperstatushistory t1
         WHERE  status IN( 'Dropoff', 'preparation' )
                AND EXISTS (SELECT 1
                            FROM   #shipperstatushistory t2
                            WHERE  status IN( 'shipper Delivery','Relay point Delivery','pickup','announcement','Shipment', 'collected', 'shipper','Relay point Collection')
                                   AND t1.ordernumber = t2.ordernumber
                                   AND t1.relaypoint = t2.relaypoint
                                   AND t2.dateshipper > t1.dateshipper)
         UNION
         SELECT*,Row_number() OVER (partition BY ordernumber, relaypoint ORDER BY dateshipper) AS RN
         FROM   #shipperstatushistory t1
         WHERE  status IN( 'Dropoff', 'preparation' )
                AND NOT EXISTS (SELECT 1
                                FROM   #shipperstatushistory t2
                                WHERE  status IN( 'shipper Delivery','Relay point Delivery','pickup','announcement','Shipment', 'collected', 'shipper','Relay point Collection')
                                       AND t1.ordernumber = t2.ordernumber
                                       AND t1.relaypoint = t2.relaypoint
                                       AND t2.dateshipper > t1.dateshipper))
SELECT t.*,
       CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END AS f
FROM   #shipperstatushistory t
       LEFT JOIN cte t2
              ON t.id = t2.id
                 AND rn = 1

My query returns wrong values on OrderNumber XN81 and XN80 and XD30

Here is a sqlFiddle

Thanks for help !

sql-server

1条答案

按热度按时间
3xiyfsfu

3xiyfsfu1#

Consider the following:

DECLARE @ShipperStatusHistory TABLE (id Int primary key identity, OrderNumber varchar(50), RelayPoint INT, Status varchar(50), DateShipper Datetime);
    
INSERT INTO @ShipperStatusHistory VALUES 
('XN75',NULL,'Shipment','2022-01-27 15:29:22.000'), ('XN75',123,'Dropoff','2022-02-01 07:15:53.000'), ('XN75',123,'Relay point Collection','2022-02-02 07:50:29.000'),
('XN75',123,'collected','2022-02-02 07:50:45.000'), ('XN75',45678,'shipper','2022-02-02 16:02:37.000'), ('XN76',234,'Dropoff','2022-04-01 09:16:51.000'),
('XN76',234,'Dropoff','2022-04-01 09:17:06.000'), ('XN77',980,'shipper Delivery','2022-09-22 09:23:35.000'), ('XN77',980,'Preparation','2022-09-29 23:00:06.000'),
('XN77',980,'shipper Delivery','2022-10-01 10:48:04.000'), ('XN77',980,'Preparation','2022-10-11 16:48:30.000'), ('XN81',1314,'Dropoff','2022-10-10 13:45:59.000'),
('XN81',1314,'Relay point Delivery','2022-10-11 13:46:59.000'), ('XN81',1314,'Preparation','2022-10-11 15:45:59.000'), ('XN81',1314,'Pickup','2022-10-12 08:46:59.000'),
('XN79',NULL,'Shipment','2022-09-25 20:38:19.000'), ('XN79',979,'Dropoff','2022-10-01 09:16:51.000'), ('XN79',979,'Relay point Collection','2022-10-03 10:14:06.000'),
('XN79',198271,'shipper','2022-10-04 15:05:30.000'), ('XN79',154,'shipper Delivery','2022-10-09 10:42:01.000'), ('XN79',154,'Relay point Delivery','2022-10-09 10:43:46.000'),
('XN79',154,'Pickup','2022-10-09 14:08:50.000'), ('XN79',711,'Dropoff','2022-10-10 13:45:59.000'), ('XN79',108223,'shipper','2022-10-11 13:45:59.000'),
('XN80',980,'shipper Delivery','2022-09-22 09:23:35.000'), ('XN80',980,'Preparation','2022-09-29 23:00:06.000'), ('XN80',980,'shipper Delivery','2022-10-01 10:48:04.000'),
('XN80',980,'Preparation','2022-10-11 16:48:30.000'), ('XN80',980,'shipper Delivery','2022-10-12 10:48:04.000'), ('XN80',980,'Preparation','2022-10-13 16:48:30.000'),
('XD30',340,'Dropoff','2022-11-17 12:29:29.000'), ('XD30',340,'Pickup','2022-11-17 12:48:43.000'), ('XD30',340,'Dropoff','2022-11-20 18:24:01.000'), ('XD30',340,'Preparation','2022-11-20 18:25:12.000'),
('XD30',340,'Collected','2022-11-20 18:26:10.000');

DECLARE @Expected TABLE (LineNumber INT, OrderNumber VARCHAR(4), RelayPoint VARCHAR(20), Status VARCHAR(50), DateShipper DATETIME, F BIT, IDStatus INT);
INSERT INTO @Expected (LineNumber, OrderNumber, RelayPoint, Status, DateShipper, F, IDStatus) VALUES
(2 ,'XN75', NULL , 'Shipment ', '1/27/2022 15:29 ', 0 , 10), (3 ,'XN75', 123 , 'Dropoff', '2/1/2022 7:15 ', 1 , 20), (4 ,'XN75', 123 , 'Relay point Collection ', '2/2/2022 7:50 ', 0 , 90),
(5 ,'XN75', 123 , 'Collected ', '2/2/2022 7:50 ', 0 , 91), (6 ,'XN75', 45678 , 'Shipper', '2/2/2022 16:02 ', 0 , 30), (7 ,'XN76', 234 , 'Dropoff ', '4/1/2022 9:16 ', 1 , 20), 
(8 ,'XN76', 234 , 'Dropoff ', '4/1/2022 9:17 ', 0 , 20), (9 ,'XN77', 980 , 'Shipper Delivery ', '9/22/2022 9:23 ', 0 , 50), (10 ,'XN77', 980 , 'Preparation ', '9/29/2022 23:00 ', 1 , 71), 
(11 ,'XN77', 980 , 'Shipper Delivery ', '10/1/2022 10:48 ', 0 , 50), (12 ,'XN77', 980 , 'Preparation ', '10/11/2022 16:48 ', 1 , 71), (13 ,'XN81', 1314 , 'Dropoff ', '10/10/2022 13:45 ', 1 , 20), 
(14 ,'XN81', 1314 , 'Relay point Delivery ', '10/11/2022 13:46 ', 0 , 51), (15 ,'XN81', 1314 , 'Preparation ', '10/11/2022 15:45 ', 1 , 71), (16 ,'XN81', 1314 , 'Pickup ', '10/12/2022 8:46 ', 0 , 60), 
(17 ,'XN79', NULL , 'Shipment ', '9/25/2022 20:38 ', 0 , 10), (18 ,'XN79', 979 , 'Dropoff ', '10/1/2022 9:16 ', 1 , 20), (19 ,'XN79', 979 , 'Relay point Collection ', '10/3/2022 10:14 ', 0 , 90), 
(20 ,'XN79', 198271 , 'Shipper ', '10/4/2022 15:05 ', 0 , 30), (21 ,'XN79', 154 , 'Shipper Delivery ', '10/9/2022 10:42 ', 0 , 50), (22 ,'XN79', 154 , 'Relay point Delivery ', '10/9/2022 10:43 ', 0 , 51), 
(23 ,'XN79', 154 , 'Pickup ', '10/9/2022 14:08 ', 0 , 60), (24 ,'XN79', 711 , 'Dropoff ', '10/10/2022 13:45 ', 1 , 20), (25 ,'XN79', 108223 , 'Shipper ', '10/11/2022 13:45 ', 0 , 30), 
(26 ,'XN80', 980 , 'Shipper Delivery ', '9/22/2022 9:23 ', 0 , 50), (27 ,'XN80', 980 , 'Preparation ', '9/29/2022 23:00 ', 1 , 71), (28 ,'XN80', 980 , 'Shipper Delivery ', '10/1/2022 10:48 ', 0 , 50), 
(29 ,'XN80', 980 , 'Preparation ', '10/11/2022 16:48 ', 1 , 71), (30 ,'XN80', 980 , 'Shipper Delivery ', '10/12/2022 10:48 ', 0 , 50), (31 ,'XN80', 980 , 'Preparation ', '10/13/2022 16:48 ', 1 , 71), 
(32 ,'XD30', 340 , 'Dropoff ', '11/17/2022 12:29 ', 1 , 20), (33 ,'XD30', 340 , 'Pickup ', '11/17/2022 12:48 ', 0 , 60), (34 ,'XD30', 340 , 'Dropoff ', '11/20/2022 18:24 ', 1 , 20), 
(35 ,'XD30', 340 , 'Preparation ', '11/20/2022 18:25 ', 0 , 71), (36 ,'XD30', 340 , 'Collected ', '11/20/2022 18:26 ', 0 , 91);

UPDATE @Expected SET LineNumber = LineNumber - 1;

SELECT s.*,
       CASE WHEN s.Status IN ('Dropoff','Preparation') AND ROW_NUMBER() OVER (PARTITION BY s.OrderNumber, CASE WHEN s.Status IN ('Dropoff','Preparation') THEN 1 ELSE 0 END ORDER BY s.DateShipper) =  1 THEN 1
            WHEN LAG(s.Status,1) OVER (PARTITION BY s.OrderNumber ORDER BY s.DateShipper) IN ('shipper Delivery', 'Relay point Delivery', 'Pickup', 'announcement', 
                                                                                              'Shipment', 'collected', 'shipper', 'Relay point Collection') 
                 AND s.Status IN ('Dropoff','Preparation') THEN 1 
            ELSE 0
       END AS Flag, e.F
  FROM @ShipperStatusHistory s
    LEFT OUTER JOIN @Expected e 
      ON s.id = e.LineNumber
 ORDER BY s.Id;

I've included your expected output as a LEFT OUTER JOIN for comparison purposes. We didn't know which IDs you intended for the second criteria, so all 8 are in there, but you can easily adjust that.

idOrderNumberRelayPointStatusDateShipperFlagF
1XN75Shipment2022-01-27 15:29:22.00000
2XN75123Dropoff2022-02-01 07:15:53.00011
3XN75123Relay point Collection2022-02-02 07:50:29.00000
4XN75123collected2022-02-02 07:50:45.00000
5XN7545678shipper2022-02-02 16:02:37.00000
6XN76234Dropoff2022-04-01 09:16:51.00011
7XN76234Dropoff2022-04-01 09:17:06.00000
8XN77980shipper Delivery2022-09-22 09:23:35.00000
9XN77980Preparation2022-09-29 23:00:06.00011
10XN77980shipper Delivery2022-10-01 10:48:04.00000
11XN77980Preparation2022-10-11 16:48:30.00011
12XN811314Dropoff2022-10-10 13:45:59.00011
13XN811314Relay point Delivery2022-10-11 13:46:59.00000
14XN811314Preparation2022-10-11 15:45:59.00011
15XN811314Pickup2022-10-12 08:46:59.00000
16XN79Shipment2022-09-25 20:38:19.00000
17XN79979Dropoff2022-10-01 09:16:51.00011
18XN79979Relay point Collection2022-10-03 10:14:06.00000
19XN79198271shipper2022-10-04 15:05:30.00000
20XN79154shipper Delivery2022-10-09 10:42:01.00000
21XN79154Relay point Delivery2022-10-09 10:43:46.00000
22XN79154Pickup2022-10-09 14:08:50.00000
23XN79711Dropoff2022-10-10 13:45:59.00011
24XN79108223shipper2022-10-11 13:45:59.00000
25XN80980shipper Delivery2022-09-22 09:23:35.00000
26XN80980Preparation2022-09-29 23:00:06.00011
27XN80980shipper Delivery2022-10-01 10:48:04.00000
28XN80980Preparation2022-10-11 16:48:30.00011
29XN80980shipper Delivery2022-10-12 10:48:04.00000
30XN80980Preparation2022-10-13 16:48:30.00011
31XD30340Dropoff2022-11-17 12:29:29.00011
32XD30340Pickup2022-11-17 12:48:43.00000
33XD30340Dropoff2022-11-20 18:24:01.00011
34XD30340Preparation2022-11-20 18:25:12.00000
35XD30340Collected2022-11-20 18:26:10.00000
赞(0）分享  回复(0）举报  11个月前
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com