February 22, 2019 at 8:15 pm
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_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]
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]
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)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)
Expected Result :
Sort Data by Shipment_ID , TASK_SEQUENCE_NUMBER
Filter where Location_code <> 'RJC'
Join of TaskMaster and TaskDetails is Shipment_id
Crirteria : TASK_TYPE should be V in a given sequence, next immediate with T , next immediate with C only considered for output where the TASK_SEQUENCE_NUMBER is in order.
Sample data : result in column V : 59 is sum of "V" value for the given Shipment_Id result data is group by shipment_id
Below is the file value and the filter in picture format
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
February 24, 2019 at 6:20 am
Here is one way of doing this
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Task_Header') IS NOT NULL DROP TABLE dbo.Task_Header;
IF OBJECT_ID(N'dbo.Task_Detail') IS NOT NULL DROP TABLE dbo.Task_Detail;
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
);
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
);
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
);
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
);
;WITH BASE_DATA AS
(
SELECT DISTINCT
TH.Shipment_ID
,TH.Vehicle_Id
,TD.Task_Sid
,TD.TASK_SEQUENCE_No
,TD.TASK_TYPE
,TD.TASK_DURATION
,TD.LOCATION_CODE
,TD.TaskStart
FROM dbo.Task_Header TH
INNER JOIN dbo.Task_Detail TD
ON TH.Shipment_ID = TD.Shipment_ID
)
,GROUP_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY BD.Shipment_ID
ORDER BY BD.TASK_SEQUENCE_No ASC
) AS TASK_RID
,CONCAT(BD.TASK_TYPE
,LEAD(BD.TASK_TYPE,1,NULL) OVER
(
PARTITION BY BD.Shipment_ID
ORDER BY BD.TASK_SEQUENCE_No ASC
)
,LEAD(BD.TASK_TYPE,2,NULL) OVER
(
PARTITION BY BD.Shipment_ID
ORDER BY BD.TASK_SEQUENCE_No ASC
)
) AS TYPE_PATTERN
,BD.Shipment_ID
,BD.Vehicle_Id
,BD.Task_Sid
,BD.TASK_SEQUENCE_No
,BD.TASK_TYPE
,BD.TASK_DURATION
,BD.LOCATION_CODE
,BD.TaskStart
FROM BASE_DATA BD
)
SELECT
GD.TASK_RID
,GD.TYPE_PATTERN
,GD.Shipment_ID
,GD.Vehicle_Id
,GD.Task_Sid
,GD.TASK_SEQUENCE_No
,GD.TASK_TYPE
,GD.TASK_DURATION
,GD.LOCATION_CODE
,GD.TaskStart
,GD.TYPE_PATTERN
,CASE
WHEN GD.TYPE_PATTERN = N'VTC' THEN 1
WHEN LAG(GD.TYPE_PATTERN,1,NULL) OVER
(
PARTITION BY GD.Shipment_ID
ORDER BY GD.TASK_SEQUENCE_No
) = N'VTC' THEN 1
WHEN LAG(GD.TYPE_PATTERN,2,NULL) OVER
(
PARTITION BY GD.Shipment_ID
ORDER BY GD.TASK_SEQUENCE_No
) = N'VTC' THEN 1
ELSE 0
END AS IN_GROUP
FROM GROUP_DATA GD
Output
TASK_RID TYPE_PATTERN Shipment_ID Vehicle_Id Task_Sid TASK_SEQUENCE_No TASK_TYPE TASK_DURATION LOCATION_CODE TaskStart IN_GROUP
--------- ------------ ------------ ---------- ----------- ----------------- --------- -------------- --------------- ---------- -----------
1 KVL 1 TN1 1 1 K 20 LC39 20 0
2 VLV 1 TN1 23 2 V 0 302 0 0
3 LVT 1 TN1 3 3 L 58 LC38 58 0
4 VTC 1 TN1 24 4 V 29 10018952 29 1
5 TCL 1 TN1 25 5 T 19 LC57 19 1
6 CLV 1 TN1 4 6 C 10 NULL 10 1
7 LVT 1 TN1 2 7 L 21 10019514 21 0
8 VTC 1 TN1 5 8 V 30 10019514 30 1
9 TC 1 TN1 7 9 T 19 10019027 19 1
10 C 1 TN1 6 10 C 11 RJC 11 1
1 LVK 2 KA2 29 2 L 35 NULL 35 0
2 VKV 2 KA2 13 3 V 32 32 0
3 KVL 2 KA2 9 4 K 20 NULL 20 0
4 VLV 2 KA2 10 5 V 0 302 0 0
5 LV 2 KA2 28 6 L 58 10018952 58 0
6 V 2 KA2 8 7 V 29 10018952 29 0
1 TCL 3 AP3 21 1 T 19 LC39 19 0
2 CLK 3 AP3 22 2 C 10 302 10 0
3 LKV 3 AP3 18 3 L 21 LC38 21 0
4 KVL 3 AP3 14 4 K 20 10018952 20 0
5 VLV 3 AP3 15 5 V 0 LC57 0 0
6 LVT 3 AP3 26 6 L 58 10019514 58 0
7 VTC 3 AP3 27 7 V 29 10019514 29 1
8 TCL 3 AP3 16 8 T 19 10019514 19 1
9 CLV 3 AP3 17 9 C 10 10019027 10 1
10 LVT 3 AP3 20 10 L 21 10019514 21 0
11 VTE 3 AP3 19 11 V 30 10019514 30 0
12 TE 3 AP3 11 12 T 19 10019514 19 0
13 E 3 AP3 12 13 E 11 10019514 11 0
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply