Summarizing data to find redeployments/repeated stock transfers
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!
