Summarizing data to find redeployments/repeated stock transfers

[NKumar]

Hi all, I am trying to find the quantity of stock that is redeployed (dispatched again to other DC) from a DC after being received.

The end requirement should look similar to this table. The material 11111 with BatchID ABC was first sent from 8881 to 7771 (Quanity 150). After being received at 7771, it was again sent to 4451 (Quantity 100) which isn't advised.

(In case this view is not possible, please propose another view that conveys similar information)

MaterialID	BatchID	Origin	Destination	Quantity	2_Origin	2_Destination	Dwell Time	Redeployed Quantity
11111	ABC	8881	7771	150	7771	4451	22	100

The data contains two transaction tables: 1. TransportDetails 2. MaterialDetails

TransportDetails_Joined table contains the list of transport orders with material, source, destination & date details.

First 7 columns from OrderID to Quantity give details about the primary transport order, colums 8 through 14 give details about the redeployment (the material is sent to another location after being received.) For e.g., looking at the first row we see that material 11111 is moved from 8881 to 7771, and then sent to 4451 from 7771.

(This is a wide table with 14 columns, please scroll horizontally)

TransportDetails_Joined
OrderID	MaterialID	Origin	Destination	ShippingDate	ReceiptDate	Quantity	2_OrderID	2_MaterialID	2_Origin	2_Destination	2_ShippingDate	2_ReceiptDate	2_Quantity
3123456	11111	8881	7771	09/26/2023	09/28/2023	150	3812345	11111	7771	4451	10/20/2023	10/24/2023	100
3123456	11111	8881	7771	09/26/2023	09/28/2023	150	3900001	11111	7771	6641	10/29/2023	11/02/2023	480

This TransportDetails table was created by me after doing a self join of the following original TransportDetails table with a few conditions like (left MaterialID = right MaterialID), (left destination = right source), (left ReceiptDate < right ShippingDate) .

OrderID	MaterialID	Origin	Destination	ShippingDate	ReceiptDate	Quantity
3123456	11111	8881	7771	09/26/2023	09/28/2023	150
3123456	11111	8881	7771	09/26/2023	09/28/2023	150
3812345	11111	7771	4451	10/20/2023	10/24/2023	100
3900001	11111	7771	6641	10/29/2023	11/02/2023	480
3750001	11111	2321	5420	08/08/2023	08/10/2023	250
3780002	11112	3593	6750	07/07/2023	07/10/2023	125

MaterialDetails table provides information about the batch that was transferred in each OrderID. 

MaterialDetails
OrderID	MaterialID	BatchID	Destination	PostingDate	Quantity
3123456	11111	ABC	7771	09/25/2023	150
3812345	11111	ABC	4451	10/19/2023	100
3900001	11111	GGG	6641	10/28/2023	480

I want to create a measure/calcualted column or some other method to summarize data such that it indicates the actual quantity redeployed when the BatchIDs match. We need to achieve this using MaterilDetails table and TransportDetails_Joined or the original TransportDetails table. We can create any number of intermediate tables if required.

The final table should look something like this. Redeployed Quantity needs to be found for each material & batchID combination. Dwell time is the time period between the first transfer's receipt date and the second transfer's shipping date = (2_ShippingDate) - (ReceiptDate)

(In case this view is not possible, please propose another view that conveys similar information)

MaterialID	BatchID	Origin	Destination	Quantity	2_Origin	2_Destination	Dwell Time	Redeployed Quantity
11111	ABC	8881	7771	150	7771	4451	22	100

I've been trying so hard to summarize the data and can't find a neat approach. Please help me with creating this summary. Any help is greatly appreciated. Thanks in advance!

[NKumar]

This post was originally marked as spam and now it has been moved back. Commenting here so that it gets more views.