value based on sequence for each detail

问题: I am using SQL Datawarehouse for my database 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...

问题:

I am using SQL Datawarehouse for my database

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.

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

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:

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

Sample data : result in column V : 59 is sum of "V" value for the given Shipment_Id result data is group by shipment_id

VTC Image for fliter and sort and group data

Expected Result

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

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


回答1:

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


回答2:

You can use lead() and lag():

select ship_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 ship_id order by task_sid) as next_task_type,
             lead(task_type, 2) over (partition by ship_id order by task_sid) as next2_task_type,
             lag(task_type) over (partition by ship_id order by task_sid) as prev_task_type,
             lag(task_type, 2) over (partition by ship_id order by task_sid) as prev2_type,
      from task_detail td
     ) 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_task_type = 'V' and prev_task_type = 'T' and task_type = 'C')
group by ship_id;
  • 发表于 2019-02-27 13:52
  • 阅读 ( 183 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除