Inventory - linking transactions to Purchase Order

1

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. 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

Any assistance or guidance would be greatly appreciated. I have put the scripts for sample data and expected result.

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 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);

Expected result -

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

Query I am trying

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 c.TranDate <= po.TranDate)
AND po.PrevRunningTotal < c.CurRunningTotal

2

I do not think your results for O3 are correct as C3 is reversed before O3 so will use the remaining C6s.

Unfortunately the only way I can think of doing this, in a set based manner, is to brute force the results with a number table. (ie It may be more efficient to use a loop in the application layer.)

With Create a Tally Function (fnTally) – SQLServerCentral

WITH Ins
AS
(
	SELECT T.Sponsor, T.Pharmacy, T.Drug, T.TransactionValue
		,ROW_NUMBER() OVER (PARTITION BY T.Sponsor, T.Pharmacy, T.Drug ORDER BY T.[Date], T.TransactionValue, N.N) AS rn
	FROM #tempInventory T
		JOIN fnTally(1,10000) N
			ON N.N <= -T.Quantity
	WHERE T.Quantity < 0
)
, Outs
AS
(
	SELECT T.Sponsor, T.Pharmacy, T.Drug, T.TransactionType, T.TransactionValue
		,ROW_NUMBER() OVER (PARTITION BY T.Sponsor, T.Pharmacy, T.Drug ORDER BY T.[Date], T.TransactionValue, N.N) AS rn
	FROM #tempInventory T
		JOIN fnTally(1,10000) N
			ON N.N <= T.Quantity
	WHERE T.Quantity > 0
)
SELECT O.TransactionValue AS TransactionValue1
	,I.TransactionValue AS TransactionValue2
	,-COUNT(1) AS LinkedQuantity
FROM Outs O
	JOIN Ins I
		ON O.Sponsor = I.Sponsor
			AND O.Pharmacy = I.Pharmacy
			AND O.Drug = I.Drug
			AND O.rn = I.rn
WHERE O.TransactionType = 'PurchaseOrder'
GROUP BY O.TransactionValue, I.TransactionValue
ORDER BY TransactionValue1, TransactionValue2;
3

Hi Ifor,
I'm truly grateful for your assistance—thank you immensely. While this query generally performs well, there are a couple of exceptions it doesn't handle properly in real data:

  1. There was an oversight on my part in not including decimal quantities in the sample data. Consequently, the query doesn't handle these instances correctly.
  2. I've noticed instances where purchase orders are erroneously linked to claim transactions that occurred after the purchase order. This shouldn't occur.
    I would be extremely grateful if you could assist in resolving these issues. Your help would be greatly appreciated.

Thanks
Kris

4

You may just have to use loops as I cannot think of a set based manner of doing this is SQL. Someone else may have a better grip on this problem.