October 12, 2023 at 6:43 pm
Hello Experts,
I'm currently attempting to create an inventory report using data from inventory transactions. I have an inventory table that resembles the one below, and I'm working on generating a report that identifies which claim transactions are associated with purchase orders. It's important to note that in some cases, a single claim transaction quantity can be split and associated with two different purchase orders due to the nature of our pack size ordering process. I've made an initial query attempt, but I'm struggling to properly split these transactions. Any assistance or guidance would be greatly appreciated. I have attached sample data and expected result.
Here is the query I am trying.
CREATE TABLE tempInventory (
Date DATE,
Sponsor VARCHAR(255),
Pharmacy VARCHAR(255),
Drug VARCHAR(255),
TransactionType VARCHAR(255),
TransactionValue VARCHAR(255),
Quantity INT,
QuantityOnHand INT
);
-- Insert the sample data
INSERT INTO tempInventory (Date, Sponsor, Pharmacy, Drug, TransactionType, TransactionValue, Quantity, QuantityOnHand)
VALUES
('2023-01-01', 'CO', 'P1', 'D1', 'Claim', 'C1', -30, -30),
('2023-01-02', 'CO', 'P1', 'D1', 'Claim', 'C2', -60, -90),
('2023-01-03', 'CO', 'P1', 'D1', 'Claim', 'C3', -30, -120),
('2023-01-04', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O1', 100, -20),
('2023-01-05', 'CO', 'P1', 'D1', 'Claim', 'C4', -30, -50),
('2023-01-06', 'CO', 'P1', 'D1', 'Claim', 'C5', -60, -110),
('2023-01-06', 'CO', 'P1', 'D1', 'Claim', 'C6', -120, -230),
('2023-01-07', 'CO', 'P1', 'D1', 'Claim', 'C7', -30, -260),
('2023-01-08', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O2', 200, -60),
('2023-01-09', 'CO', 'P1', 'D1', 'Claim', 'C8', -40, -100),
('2023-01-11', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O3', 100, 0);
select * from dba..tempInventory
-- Create a temporary table to store the report
CREATE TABLE #Report (
TransactionValue1 NVARCHAR(255),
TransactionValue2 NVARCHAR(255),
LinkedQuantity INT
);
-- Declare variables for cursor
DECLARE @ClaimTransactionValue NVARCHAR(255);
DECLARE @ClaimQuantity INT;
-- Create a cursor for claim transactions
DECLARE claimCursor CURSOR FOR
SELECT TransactionValue, Quantity
from dba..tempInventory
WHERE TransactionType = 'Claim';
-- Open the cursor
OPEN claimCursor;
-- Fetch the first row from the cursor
FETCH NEXT FROM claimCursor INTO @ClaimTransactionValue, @ClaimQuantity;
-- Declare variables for purchase order
DECLARE @PurchaseOrderTransactionValue NVARCHAR(255);
DECLARE @PurchaseOrderQuantity INT;
-- Iterate through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- Create a cursor for purchase order transactions following the claim
DECLARE purchaseOrderCursor CURSOR FOR
SELECT TransactionValue, Quantity
from dba..tempInventory
WHERE TransactionType = 'PurchaseOrder'
AND Pharmacy = (SELECT Pharmacy from dba..tempInventory WHERE TransactionValue = @ClaimTransactionValue)
AND Drug = (SELECT Drug from dba..tempInventory WHERE TransactionValue = @ClaimTransactionValue)
AND Date > (SELECT Date from dba..tempInventory WHERE TransactionValue = @ClaimTransactionValue);
-- Open the purchase order cursor
OPEN purchaseOrderCursor;
-- Fetch and insert rows into the report table
FETCH NEXT FROM purchaseOrderCursor INTO @PurchaseOrderTransactionValue, @PurchaseOrderQuantity;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Determine the claim quantity to link with this purchase order
DECLARE @LinkedQuantity INT;
IF @ClaimQuantity > @PurchaseOrderQuantity
SET @LinkedQuantity = -@PurchaseOrderQuantity;
ELSE
SET @LinkedQuantity = -@ClaimQuantity;
INSERT INTO #Report (TransactionValue1, TransactionValue2, LinkedQuantity)
VALUES (@PurchaseOrderTransactionValue, @ClaimTransactionValue, @LinkedQuantity);
-- Update the claim quantity and purchase order quantity
SET @ClaimQuantity = @ClaimQuantity + @LinkedQuantity;
SET @PurchaseOrderQuantity = @PurchaseOrderQuantity + @LinkedQuantity;
-- Fetch the next purchase order from the purchase order cursor
FETCH NEXT FROM purchaseOrderCursor INTO @PurchaseOrderTransactionValue, @PurchaseOrderQuantity;
END;
-- Close and deallocate the purchase order cursor
CLOSE purchaseOrderCursor;
DEALLOCATE purchaseOrderCursor;
-- Fetch the next claim from the claim cursor
FETCH NEXT FROM claimCursor INTO @ClaimTransactionValue, @ClaimQuantity;
END;
-- Close and deallocate the claim cursor
CLOSE claimCursor;
DEALLOCATE claimCursor;
-- Select the final report
SELECT * FROM #Report order by TransactionValue1,TransactionValue2;
-- Clean up the temporary table
DROP TABLE #Report;
October 12, 2023 at 8:07 pm
This gives your expected results. I partitioned and joined on the Sponsor, Pharmacy, and Drug. You may need to change this.
WITH RunningTotals AS
(
SELECT i.[Date]
, i.Sponsor
, i.Pharmacy
, i.Drug
, i.TransactionType
, i.TransactionValue
, i.Quantity
, i.QuantityOnHand
, COALESCE(SUM(i.Quantity) OVER(PARTITION BY i.Sponsor, i.Pharmacy, i.Drug, i.TransactionType ORDER BY i.[Date] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS PrevRunningTotal
, SUM(i.Quantity) OVER(PARTITION BY i.Sponsor, i.Pharmacy, i.Drug, i.TransactionType ORDER BY i.[Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CurRunningTotal
FROM #tempInventory AS i
), Claims AS
(
SELECT rt.Date
, rt.Sponsor
, rt.Pharmacy
, rt.Drug
, rt.TransactionType
, rt.TransactionValue
, rt.Quantity
, rt.QuantityOnHand
, -rt.PrevRunningTotal AS PrevRunningTotal
, -rt.CurRunningTotal AS CurRunningTotal
FROM RunningTotals AS rt
WHERE rt.TransactionType = 'Claim'
), PurchaseOrders AS
(
SELECT rt.Date
, rt.Sponsor
, rt.Pharmacy
, rt.Drug
, rt.TransactionType
, rt.TransactionValue
, rt.Quantity
, rt.QuantityOnHand
, rt.PrevRunningTotal
, rt.CurRunningTotal
FROM RunningTotals AS rt
WHERE rt.TransactionType = 'PurchaseOrder'
)
SELECT po.TransactionValue
, c.TransactionValue
, CASE WHEN c.CurRunningTotal > po.CurRunningTotal
THEN c.PrevRunningTotal - po.CurRunningTotal
WHEN c.PrevRunningTotal < po.PrevRunningTotal
THEN po.PrevRunningTotal - c.CurRunningTotal
ELSE c.Quantity
END AS LinkedQuantity
FROM Claims AS c
INNER JOIN PurchaseOrders AS po
ON c.Sponsor = po.Sponsor
AND c.Pharmacy = po.Pharmacy
AND c.Drug = po.Drug
AND c.PrevRunningTotal < po.CurRunningTotal
AND po.PrevRunningTotal < c.CurRunningTotal
Drew
NOTE: It is a good idea to use the {;}Code button when posting code.
NOTE 2: People are loathe to download potentially infected files posted by random strangers. It's better to post scripts to create and populate a table with your expected results.
EDIT: Changed the FULL OUTER JOIN to an INNER JOIN. I used the FULL OUTER JOIN in my testing, and forgot to change it back.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2023 at 9:41 pm
Thank you so much Drew. I will try with few different scenarios.
Thanks
November 21, 2023 at 6:26 pm
Hello Expert,
The query performed perfectly, but I had to account for specific cases where it didn't work as expected. Some transactions have reversals. For instance, consider the scenario where the C0 claim transaction is executed and then reversed before an order is placed. Hence, it shouldn't be considered in the linking result. However, the C3 claim gets reversed at a later stage, allowing the original C3 transaction to link with the O1 and O2 orders. Yet, the reversed C3 claim should be ignored for linking purposes, and part of C8 should also be disregarded due to the impact of C3 on inventory. I'd really appreciate your help in handling this particular situation. I am adding the insert script and expected result below
INSERT INTO tempInventory (Date, Sponsor, Pharmacy, Drug, TransactionType, TransactionValue, Quantity, QuantityOnHand)
VALUES
('2023-01-01', 'CO', 'P1', 'D1', 'Claim', 'C0', -60, -60),
('2023-01-01', 'CO', 'P1', 'D1', 'Claim', 'C0', 60, 0), --Reversed
('2023-01-01', 'CO', 'P1', 'D1', 'Claim', 'C1', -30, -30),
('2023-01-02', 'CO', 'P1', 'D1', 'Claim', 'C2', -60, -90),
('2023-01-03', 'CO', 'P1', 'D1', 'Claim', 'C3', -30, -120),
('2023-01-04', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O1', 100, -20),
('2023-01-05', 'CO', 'P1', 'D1', 'Claim', 'C4', -30, -50),
('2023-01-06', 'CO', 'P1', 'D1', 'Claim', 'C5', -60, -110),
('2023-01-06', 'CO', 'P1', 'D1', 'Claim', 'C6', -120, -230),
('2023-01-07', 'CO', 'P1', 'D1', 'Claim', 'C7', -30, -260),
('2023-01-08', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O2', 200, -60),
('2023-01-09', 'CO', 'P1', 'D1', 'Claim', 'C8', -40, -100),
('2023-01-09', 'CO', 'P1', 'D1', 'Claim', 'C3', 30, -70), --Reversed
('2023-01-09', 'CO', 'P1', 'D1', 'Claim', 'C9', -30, -100),
('2023-01-11', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O3', 100, 0);
TransactionValue1 TransactionValue2 LinkedQuantity
O1 C1 -30
O1 C2 -60
O1 C3 -10
O2 C3 -20
O2 C4 -30
O2 C5 -60
O2 C6 -90
O3 C6 -30
O3 C7 -30
O3 C8 -10
O3 C9 -30
November 23, 2023 at 3:30 am
SQL Experts, Any help is appreciated!
November 23, 2023 at 2:41 pm
Can you elaborate a bit. Reversed=same quantity?
What is the relation of
O1 C3 -10
I only see
('2023-01-03', 'CO', 'P1', 'D1', 'Claim', 'C3', -30, -120) before
('2023-01-04', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O1', 100, -20),
November 23, 2023 at 4:13 pm
Yes, When a transaction is reversed, it involves returning the same quantity (as a negative value) back to the inventory.
Explanation for
O1 C3 -10
This result shows that even though the original transaction under transaction C3 had a quantity of 30, only a portion (-10) of that quantity participated in Purchase Order O1. The remaining quantity of the original transaction might be linked or associated with a different purchase order, such as O2.
Note: Drew's earlier response is accurate when handling scenarios without any reversed transactions. However, it's providing incorrect linking between purchase orders and transactions in cases involving reversed transactions.
Really Appreciate looking into this...
November 27, 2023 at 4:52 pm
Any help is greatly appreciated!
November 29, 2023 at 4:16 pm
Is there a primary key on Inventory table? It would help understanding what is going on.
Zidar's Theorem: The best code is no code at all...
November 29, 2023 at 4:46 pm
Hi Zidar, The order data and claim transaction data originate from separate tables and are structured as a dataset for this purpose. If having a primary key column would assist in the query, we can incorporate that. Additionally, the combination of sponsor, pharmacy, drug, and transaction value always remains unique. I'm including a new script with the primary key in case it proves helpful. Thank you for looking into this.
CREATE TABLE tempInventory (
ID int,
Date DATE,
Sponsor VARCHAR(255),
Pharmacy VARCHAR(255),
Drug VARCHAR(255),
TransactionType VARCHAR(255),
TransactionValue VARCHAR(255),
Quantity INT,
QuantityOnHand INT
);
-- Insert the sample data
INSERT INTO tempInventory (ID,Date, Sponsor, Pharmacy, Drug, TransactionType, TransactionValue, Quantity, QuantityOnHand)
VALUES
(1,'2023-01-01', 'CO', 'P1', 'D1', 'Claim', 'C1', -30, -30),
(2, '2023-01-02', 'CO', 'P1', 'D1', 'Claim', 'C2', -60, -90),
(3, '2023-01-03', 'CO', 'P1', 'D1', 'Claim', 'C3', -30, -120),
(4, '2023-01-04', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O1', 100, -20),
(5,'2023-01-05', 'CO', 'P1', 'D1', 'Claim', 'C4', -30, -50),
(6,'2023-01-06', 'CO', 'P1', 'D1', 'Claim', 'C5', -60, -110),
(7,'2023-01-06', 'CO', 'P1', 'D1', 'Claim', 'C6', -120, -230),
(8,'2023-01-07', 'CO', 'P1', 'D1', 'Claim', 'C7', -30, -260),
(9,'2023-01-08', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O2', 200, -60),
(10,'2023-01-09', 'CO', 'P1', 'D1', 'Claim', 'C8', -40, -100),
(11,'2023-01-11', 'CO', 'P1', 'D1', 'PurchaseOrder', 'O3', 100, 0);
December 4, 2023 at 5:41 pm
I apologize for the delay, was out of town for a few days.
Thank you for refreshing the script and the explanation.
I noticed that when listed in proper order, a picture emerges. It is safe to assume that each row is a single transaction. transactions come in some natural order - at different dates, but not always. There are two types of transaction - Claims and PurchaseOrders. Claims have negative values, they decrease Quantity on hand. Purchase Orders have positive values and they increase Quantity on hand.
I believe you want to associate PurchaseOrder with claims before it. Somethng like this does the trick.
WITH PurchaseOrders AS
(-- Extract PO transactions
SELECT Sponsor, Pharmacy, Drug, TransactionValue
, TransactionType, Quantity
, [Date]
FROM tempInventory
WHERE TransactionType = 'PurchaseOrder'
)
, PO_dateRanges AS
(-- Determine date ranges covered by POs
SELECT Sponsor, Pharmacy, Drug, TransactionValue
, TransactionType, Quantity
, [Date]
, PrevDate = LAG([Date],1,null) OVER (ORDER BY [Date])
FROM PurchaseOrders
)
SELECT I.Sponsor, I.Pharmacy, I.Drug, I.TransactionValue
, I.TransactionType, I.Quantity, QuantityOnHand
, I.[Date] AS TransDate
, PO.TransactionValue AS PurchaseOrder
, PO.Date AS PurchOrderDate
FROM tempInventory As I
JOIN PO_dateRanges AS PO ON 1=1
WHERE 1=1
AND I.Date<=PO.DAte AND (I.Date>PO.PrevDate OR PO.PrevDate IS NULL)
ORDER BY I.[Date] ,sponsor, pharmacy, drug, transactionvalue, QuantityOnHand
;
which returns this result:
Sponsor Pharmacy Drug TransactionValue TransactionType Quantity QuantityOnHand TransDate PurchaseOrder PurchOrderDate
------- -------- ----- ---------------- -------------------- ----------- -------------- ---------- ------------- --------------
CO P1 D1 C1 Claim -30 -30 2023-01-01 O1 2023-01-04
CO P1 D1 C2 Claim -60 -90 2023-01-02 O1 2023-01-04
CO P1 D1 C3 Claim -30 -120 2023-01-03 O1 2023-01-04
CO P1 D1 O1 PurchaseOrder 100 -20 2023-01-04 O1 2023-01-04
CO P1 D1 C4 Claim -30 -50 2023-01-05 O2 2023-01-08
CO P1 D1 C5 Claim -60 -110 2023-01-06 O2 2023-01-08
CO P1 D1 C6 Claim -120 -230 2023-01-06 O2 2023-01-08
CO P1 D1 C7 Claim -30 -260 2023-01-07 O2 2023-01-08
CO P1 D1 O2 PurchaseOrder 200 -60 2023-01-08 O2 2023-01-08
CO P1 D1 C8 Claim -40 -100 2023-01-09 O3 2023-01-11
CO P1 D1 O3 PurchaseOrder 100 0 2023-01-11 O3 2023-01-11
(11 rows affected)
Completion time: 2023-12-04T12:39:41.0673208-05:00
It is quite possible that a simpler query could be written.
🙂
Zidar's Theorem: The best code is no code at all...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply