Value based on sequence for each detail

shanmugaraj · February 23, 2019, 10:46am

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
for Shipment_ID 1, we have the column V : 59 which is sum of "V" value for the given Shipment_Id result data is group by shipment_id which is sum of sequence_number 4 and 8 = 29+30 = 59
Column T is present in scequence after V which is sum of sequence_number 5 and 9 = 19 +19 = 38
Column C is present in scequence after T which is sum of sequence_number 6 and 10 = 10 +11 = 21
For shipment 2, we dont have Task Type in VTC Secquence
For shipment 3, we have Task Type in V T C Secquence for no 7 ,8 , 9 with values 29 , 19, 10 which is only once and that value is sent to main table


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

bitsmed · February 23, 2019, 1:30pm

From the sample data you provided, I can only get:

Sno  Shipment_ID  Vehicle ID  DepotVisitStartTime           V   T   C
  1            1  TN1         2019-02-15 07:25:33.0000000  59  38   0
  2            1  TN1         2019-02-15 07:25:33.0000000  59  38   0
  3            1  TN1         2019-02-15 07:25:33.0000000  59  38   0
  4            1  TN1         2019-02-15 07:25:33.0000000  59  38   0
  5            2  KA2         2019-02-15 06:12:52.0000000  61   0   0
  6            2  KA2         2019-02-15 06:12:52.0000000  61   0   0
  7            3  AP3         2019-02-15 06:32:52.0000000  59  57  20
  8            3  AP3         2019-02-15 06:32:52.0000000  59  57  20
  9            3  AP3         2019-02-15 13:12:21.0000000  59  57  20

so either your sample data doesn't match your expected output, or I don't understand your request

shanmugaraj · February 23, 2019, 2:53pm

Can you share the query you have used
How come you can get
ShipID 1 - C as 0 ?
ShipID 2 - is suppose to not get any values since there is no Sequence matching VTC
ShipID 3 -where is the values coming from ?
i have update the result sample data with more information

bitsmed · February 23, 2019, 6:52pm

I'll share when I get the correct result

The reason I got 0 was, I was excluding location_code RJC and by fault also null. This is now corrected.By I now get 10. Don't you want to filter task_location to never be RJC any more?

How about task_sid 8, 10 and 13?

It's comming from task_sid 11, 15, 16, 17, 19, 21, 22, 27

I now get this result from the sample data you provided:

Sno  Shipment_ID  Vehicle ID  DepotVisitStartTime           V   T   C
  1            1  TN1         2019-02-15 07:25:33.0000000  59  38  10
  2            1  TN1         2019-02-15 07:25:33.0000000  59  38  10
  3            1  TN1         2019-02-15 07:25:33.0000000  59  38  10
  4            1  TN1         2019-02-15 07:25:33.0000000  59  38  10
  5            2  KA2         2019-02-15 06:12:52.0000000  61   0   0
  6            2  KA2         2019-02-15 06:12:52.0000000  61   0   0
  7            3  AP3         2019-02-15 06:32:52.0000000  59  57  20
  8            3  AP3         2019-02-15 06:32:52.0000000  59  57  20
  9            3  AP3         2019-02-15 13:12:21.0000000  59  57  20

shanmugaraj · February 25, 2019, 2:25pm

Here is the solution

select h.*, coalesce(d.v, 0) as v,
       coalesce(d.t, 0) as t, coalesce(d.c, 0) as c
from task_header h outer apply
     (select sum(dv.task_duration) as v,
             sum(dt.task_duration) as t,
             sum(dc.task_duration) as c
      from task_detail dv join
           task_detail dt
           on dt.Shipment_ID = dv.Shipment_ID and dt.TASK_SEQUENCE_No = dv.TASK_SEQUENCE_No + 1 join
           task_detail dc
           on dc.Shipment_ID = dt.Shipment_ID and dc.TASK_SEQUENCE_No = dt.TASK_SEQUENCE_No + 1 
      where dv.Shipment_ID = h.Shipment_ID and dv.task_type = 'V' and dt.task_type = 'T' and dc.task_type = 'C'
     ) d;

harishgg1 · December 16, 2019, 3:03pm

hi

i know this is from long ago

i just took the data from image directly

please click arrow to the left for drop create data ...

drop table #data 
go 

create table #data 
(
Shipment_ID int ,
Task_Sequence_Number int ,
Task_type varchar(1),
Task_Duration int 
) 
go 

insert into #data select 1,1,'K',20 
insert into #data select 1,2,'V',0 
insert into #data select 1,3,'L',58
insert into #data select 1,4,'V',29 
insert into #data select 1,5,'T',19 
insert into #data select 1,6,'C',10 
insert into #data select 1,7,'L',21 
insert into #data select 1,8,'V',30 
insert into #data select 1,9,'T',19 
insert into #data select 1,10,'C',11 


insert into #data select 2,2,'L',35 
insert into #data select 2,3,'V',32 
insert into #data select 2,4,'K',20 
insert into #data select 2,5,'V',0 
insert into #data select 2,6,'L',58 
insert into #data select 2,7,'V',29 


insert into #data select 3,1,'T',19 
insert into #data select 3,2,'C',10 
insert into #data select 3,3,'L',21
insert into #data select 3,4,'K',20 
insert into #data select 3,5,'V',0 
insert into #data select 3,6,'L',58 
insert into #data select 3,7,'V',29 
insert into #data select 3,8,'T',19 
insert into #data select 3,9,'C',10 
insert into #data select 3,10,'L',21 

go

please click arrow to the left for SQL

; with cte as 
(
select 
Shipment_ID,
case when Task_Type = 'V' 
     and lead(task_type) over(partition by Shipment_ID order by Shipment_ID,Task_Sequence_Number) = 'T'
     and lead(task_type,2) over(partition by Shipment_ID order by Shipment_ID,Task_Sequence_Number) = 'C'
     then  Task_Duration end as 'V'
     , 
     case when Task_Type = 'T' 
     and lag(task_type) over(partition by Shipment_ID order by Shipment_ID,Task_Sequence_Number) = 'V'
     and lead(task_type,1) over(partition by Shipment_ID order by Shipment_ID,Task_Sequence_Number) = 'C'
     then  Task_Duration end as 'T'
     , 
     case when Task_Type = 'C' 
     and lag(task_type) over(partition by Shipment_ID  order by Shipment_ID,Task_Sequence_Number) = 'T'
     and lag(task_type,2) over(partition by Shipment_ID order by Shipment_ID,Task_Sequence_Number) = 'V'
     then  Task_Duration end as 'C'
from #data 
) , cte_null as 
(
select shipment_id,ISNULL(v,0) as v ,ISNULL(t,0) as t ,ISNULL(c,0)  as c from cte 
)
select shipment_id,SUM(v) as v,SUM(t) as t, SUM(c) as c from cte_null group by Shipment_ID
go