每个细节基于顺序的值 [英] value based on sequence for each detail
问题描述
我正在为数据库使用SQL Datawarehouse
I am using SQL Datawarehouse for my database
按Shipment_id顺序按TASK_SEQUENCE_NUMBER顺序查找给定明细组的V，T，C，其中TASK_TYPE的顺序为V然后T然后C.
Find the V,T,C in sequence for a given detail group by Shipment_id order by TASK_SEQUENCE_NUMBER where the order of TASK_TYPE is V then T then C.
如果顺序没有V，T，C的值，则该值为0 如果V，T，C不止一次，我们可以对这些值求和并显示
This value can be 0 if there is no values for V,T,C in sequence In case of V,T,C is more than once, we can sum the values and display
下面是具有预期结果的表架构和数据，必须将其合并到Task_Main表中具有更多列的存储过程中.
below is the table schema and data with the result expected which has to be incorporated in a stored procedure with more columns in the Task_Main Table.
CREATE TABLE [dbo].[Task_Detail](
[Task_Sid] [int] NULL, [Shipment_ID] [decimal](18, 0) NULL,
[TASK_SEQUENCE_No] [decimal](18, 0) NULL,
[TASK_TYPE] [varchar](1) NULL, [TASK_DURATION] [decimal](18, 0) NULL, [LOCATION_CODE] [varchar](15) NULL, [TaskStart] [bigint] NULL ) ON [PRIMARY]
CREATE TABLE [dbo].[Task_Header](
[Task_Sno] [int] NULL, [Shipment_ID] [decimal](18, 0) NULL,
[Vehicle_Id] [nchar](10) NULL, [DepotVisitStartTime] [datetime2](7) NULL, [Time_V] [bigint] NULL, [Time_T] [bigint] NULL, [Time_C] [bigint] NULL ) ON [PRIMARY]
INSERT [dbo].[Task_Detail] ([Task_Sid], [Shipment_ID], [TASK_SEQUENCE_No], [TASK_TYPE], [TASK_DURATION], [LOCATION_CODE], [TaskStart]) VALUES
(1, CAST(1 AS Decimal(18, 0)), CAST(1 AS Decimal(18, 0)), N'K', CAST(20 AS Decimal(18, 0)), N'LC39', 20),
(2, CAST(1 AS Decimal(18, 0)), CAST(7 AS Decimal(18, 0)), N'L', CAST(21 AS Decimal(18, 0)), N'10019514', 21),
(3, CAST(1 AS Decimal(18, 0)), CAST(3 AS Decimal(18, 0)), N'L', CAST(58 AS Decimal(18, 0)), N'LC38', 58),
(4, CAST(1 AS Decimal(18, 0)), CAST(6 AS Decimal(18, 0)), N'C', CAST(10 AS Decimal(18, 0)), NULL, 10),
(5, CAST(1 AS Decimal(18, 0)), CAST(8 AS Decimal(18, 0)), N'V', CAST(30 AS Decimal(18, 0)), N'10019514', 30),
(6, CAST(1 AS Decimal(18, 0)), CAST(10 AS Decimal(18, 0)), N'C', CAST(11 AS Decimal(18, 0)), N'RJC', 11),
(7, CAST(1 AS Decimal(18, 0)), CAST(9 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'10019027', 19)
, (8, CAST(2 AS Decimal(18, 0)), CAST(7 AS Decimal(18, 0)), N'V', CAST(29 AS Decimal(18, 0)), N'10018952', 29)
, (9, CAST(2 AS Decimal(18, 0)), CAST(4 AS Decimal(18, 0)), N'K', CAST(20 AS Decimal(18, 0)), NULL, 20)
, (10, CAST(2 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)), N'V', CAST(0 AS Decimal(18, 0)), N'302', 0)
, (11, CAST(3 AS Decimal(18, 0)), CAST(12 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'10019514', 19)
, (12, CAST(3 AS Decimal(18, 0)), CAST(13 AS Decimal(18, 0)), N'E', CAST(11 AS Decimal(18, 0)), N'10019514', 11)
, (13, CAST(2 AS Decimal(18, 0)), CAST(3 AS Decimal(18, 0)), N'V', CAST(32 AS Decimal(18, 0)), N'', 32)
, (14, CAST(3 AS Decimal(18, 0)), CAST(4 AS Decimal(18, 0)), N'K', CAST(20 AS Decimal(18, 0)), N'10018952', 20)
, (15, CAST(3 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)), N'V', CAST(0 AS Decimal(18, 0)), N'LC57', 0)
, (16, CAST(3 AS Decimal(18, 0)), CAST(8 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'10019514', 19)
, (17, CAST(3 AS Decimal(18, 0)), CAST(9 AS Decimal(18, 0)), N'C', CAST(10 AS Decimal(18, 0)), N'10019027', 10)
, (18, CAST(3 AS Decimal(18, 0)), CAST(3 AS Decimal(18, 0)), N'L', CAST(21 AS Decimal(18, 0)), N'LC38', 21)
, (19, CAST(3 AS Decimal(18, 0)), CAST(11 AS Decimal(18, 0)), N'V', CAST(30 AS Decimal(18, 0)), N'10019514', 30)
, (20, CAST(3 AS Decimal(18, 0)), CAST(10 AS Decimal(18, 0)), N'L', CAST(21 AS Decimal(18, 0)), N'10019514', 21)
, (21, CAST(3 AS Decimal(18, 0)), CAST(1 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'LC39', 19)
, (22, CAST(3 AS Decimal(18, 0)), CAST(2 AS Decimal(18, 0)), N'C', CAST(10 AS Decimal(18, 0)), N'302', 10)
, (23, CAST(1 AS Decimal(18, 0)), CAST(2 AS Decimal(18, 0)), N'V', CAST(0 AS Decimal(18, 0)), N'302', 0)
, (24, CAST(1 AS Decimal(18, 0)), CAST(4 AS Decimal(18, 0)), N'V', CAST(29 AS Decimal(18, 0)), N'10018952', 29)
, (25, CAST(1 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'LC57', 19)
, (26, CAST(3 AS Decimal(18, 0)), CAST(6 AS Decimal(18, 0)), N'L', CAST(58 AS Decimal(18, 0)), N'10019514', 58)
, (27, CAST(3 AS Decimal(18, 0)), CAST(7 AS Decimal(18, 0)), N'V', CAST(29 AS Decimal(18, 0)), N'10019514', 29)
, (28, CAST(2 AS Decimal(18, 0)), CAST(6 AS Decimal(18, 0)), N'L', CAST(58 AS Decimal(18, 0)), N'10018952', 58)
, (29, CAST(2 AS Decimal(18, 0)), CAST(2 AS Decimal(18, 0)), N'L', CAST(35 AS Decimal(18, 0)), NULL, 35)
INSERT [dbo].[Task_Header] ([Task_Sno], [Shipment_ID], [Vehicle_Id], [DepotVisitStartTime], [Time_V], [Time_T], [Time_C]) VALUES
(1, CAST(1 AS Decimal(18, 0)), N'TN1 ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(2, CAST(1 AS Decimal(18, 0)), N'TN1 ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(3, CAST(1 AS Decimal(18, 0)), N'TN1 ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(4, CAST(1 AS Decimal(18, 0)), N'TN1 ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(5, CAST(2 AS Decimal(18, 0)), N'KA2 ', CAST(N'2019-02-15T06:12:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(6, CAST(2 AS Decimal(18, 0)), N'KA2 ', CAST(N'2019-02-15T06:12:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(7, CAST(3 AS Decimal(18, 0)), N'AP3 ', CAST(N'2019-02-15T06:32:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(8, CAST(3 AS Decimal(18, 0)), N'AP3 ', CAST(N'2019-02-15T06:32:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(9, CAST(3 AS Decimal(18, 0)), N'AP3 ', CAST(N'2019-02-15T13:12:21.0000000' AS DateTime2), NULL, NULL, NULL)
使用过滤器的预期结果:
Expected Result with filter:
按Shipment_ID排序数据，TASK_SEQUENCE_NUMBER 过滤位置代码<>'RJC' TaskMaster和TaskDetails的联接为Shipment_id TASK_TYPE在给定序列中应为V，仅与T相邻，与T相邻，与C相邻，仅考虑输出
Sort Data by Shipment_ID , TASK_SEQUENCE_NUMBER filter where Location_code <> 'RJC' Join of TaskMaster and TaskDetails is Shipment_id TASK_TYPE should be V in a given sequence, next immediate with T , next immediate with C only considered for output
样本数据:第V列中的结果:59是给定Shipment_Id结果数据的"V"值之和
Sample data : result in column V : 59 is sum of "V" value for the given Shipment_Id result data is group by shipment_id
预期结果
Sno Shipment_ID Vehicle ID DepotVisitStartTime V T C
1 1 TN1 2019-02-15 07:25:33.0000000 59 38 21
2 1 TN1 2019-02-15 07:25:33.0000000 59 38 21
3 1 TN1 2019-02-15 07:25:33.0000000 59 38 21
4 1 TN1 2019-02-15 07:25:33.0000000 59 38 21
5 2 KA2 2019-02-15 06:12:52.0000000 0 0 0
6 2 KA2 2019-02-15 06:12:52.0000000 0 0 0
7 3 AP3 2019-02-15 06:32:52.0000000 29 19 10
8 3 AP3 2019-02-15 06:32:52.0000000 29 19 10
9 3 AP3 2019-02-15 13:12:21.0000000 29 19 10
这些列是下面文章的延续，我想有一个StoredProceudre将数据插入Task_Master表中 总日期和数量在给定ID的同一张表中
These columns are continuation of below post and i want to have an StoredProceudre to insert data into Task_Master table Aggregate Date and quantity in same table for given ID
推荐答案
@Gordon给出的代码似乎是正确的，只是更改了主要逻辑中的列名，希望对您有所帮助！
Code given by @Gordon seems to be mostly correct, just changing the column names in main logic, hope this helps!
select Shipment_ID,
sum(case when task_type = 'V' then task_duration else 0 end) as v_duration,
sum(case when task_type = 'T' then task_duration else 0 end) as t_duration,
sum(case when task_type = 'C' then task_duration else 0 end) as c_duration
from (select td.*,
lead(task_type) over (partition by Shipment_ID order by TASK_SEQUENCE_No) as next_task_type,
lead(task_type, 2) over (partition by Shipment_ID order by TASK_SEQUENCE_No) as next2_task_type,
lag(task_type) over (partition by Shipment_ID order by TASK_SEQUENCE_No) as prev_task_type,
lag(task_type, 2) over (partition by Shipment_ID order by TASK_SEQUENCE_No) as prev2_type
from task_detail td --WHERE Location_Code <>'RJC'
) td
where (task_type = 'V' and next_task_type = 'T' and next2_task_type = 'C') or
(prev_task_type = 'V' and task_type = 'T' and next_task_type = 'C') or
(prev2_type = 'V' and prev_task_type = 'T' and task_type = 'C')
group by Shipment_ID;
添加获得的结果的图像.结果
Adding the image of the result obtained.Results
这篇关于每个细节基于顺序的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
