April 13, 2011 at 10:28 am
I have two tables. One is a list of receipts, and one is a list of shipments. The shipments are filled from stock that the receipts created. Each record has a tranaction number (TrnNum), which is just a sequential number added as the record is added to the table.
My objective is to match the shipment and receipt records by matching the TrnNum, with the earliest shipment matched to the earliest receipt, the second earliest shipment matched to the next earliest reciept, and so on.
Here is the sample data:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#shiptran','U') IS NOT NULL DROP TABLE #shiptran
GO
IF OBJECT_ID('TempDB..#rcpttran','U') IS NOT NULL DROP TABLE #rcpttran
GO
--===== Create the test table
CREATE TABLE #ShipTran
(
TrnNum DECIMAL(10,0),
ShpTrnDate DATETIME,
ShpItem NVARCHAR(30),
ShpQty DECIMAL(21,2),
ShpWhse NVARCHAR(4),
ShpLoc NVARCHAR(15),
CoNum NVARCHAR(20),
CoLnNum SMALLINT
)
CREATE TABLE #RcptTran
(
TrnNum DECIMAL(10,0),
RctTrnDate DATETIME,
RctItem NVARCHAR(30),
RctQty DECIMAL(21,2),
RctWhse NVARCHAR(4),
RctLoc NVARCHAR(15),
PoNum NVARCHAR(20),
)
--===== Insert the test data into the test table
INSERT INTO #ShipTran
(TrnNum, ShpTrnDate, ShpItem, ShpQty, ShpWhse, ShpLoc, CoNum, CoLnNum)
SELECT 10075796, '02/16/2011', 'TWS0600-579', 1, 'MAIN', 'PumpShip', 'OR00033194', 1 UNION ALL
SELECT 10075797, '02/16/2011', 'TWS0600-579', 1, 'MAIN', 'PumpShip', 'OR00033194', 2 UNION ALL
SELECT 10075798, '02/17/2011', 'TWS0600-579', 1, 'MAIN', 'PumpShip', 'OR00033194', 3
INSERT INTO #RcptTran
(TrnNum, RctTrnDate, RctItem, RctQty, RctWhse, RctLoc, PoNum)
SELECT 9981437, '02/07/2011', 'TWS0600-579', 1, 'MAIN', 'PumpShip', 'OR00033194' UNION ALL
SELECT 9981463, '02/09/2011', 'TWS0600-579', 1, 'MAIN', 'PumpShip', 'OR00033194' UNION ALL
SELECT 10011029, '02/09/2011', 'TWS0600-579', 1, 'MAIN', 'PumpShip', 'OR00033194'
--==== SELECT the records
SELECT* FROM #ShipTran
SELECT* FROM #RcptTran
The output should look like this:
ShpTrn ShpTrnDateShpItem ShpQty ShpWhseShpLoc CoNum CoLnNum RctTrn RctTrnDateRctItem RctQtyRctWhseRctLoc PoNum
------ ---------- ----------- ------ ------- -------- ---------- ------- ------- ---------- ----------- ------ ------- --------- ----------
100757962/16/2011TWS0600-579-1 MAIN PumpShipOR000331941 99814372/7/2011 TWS0600-5791 MAIN PumpShip OR00033194
100757972/16/2011TWS0600-579-1 MAIN PumpShipOR000331942 99814632/9/2011 TWS0600-5791 MAIN PumpShip OR00033194
100757982/17/2011TWS0600-579-1 MAIN PumpShipOR000331943 100110292/9/2011 TWS0600-5791 MAIN PumpShip OR00033194
What is the best way to do this? I am pulling from a small set of records, so performance isn't as important as accuracy.
Thank you.
April 13, 2011 at 2:28 pm
This seems to work with your test data:
--==== SELECT the records
;
WITH cteShipItems
AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY #ShipTran.ShpItem ORDER BY #ShipTran.ShpTrnDate) AS item_ship_order
FROM
#ShipTran
),
cteRcptItems
AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY #RcptTran.RctItem ORDER BY #RcptTran.RctTrnDate) AS item_rcpt_order
FROM
#RcptTran
)
SELECT
SI.TrnNum AS ShpTrn,
SI.ShpTrnDate,
SI.ShpItem,
SI.ShpQty,
SI.ShpWhse,
SI.ShpLoc,
SI.CoNum,
SI.CoLnNum,
RI.TrnNum AS RctTrn,
RI.RctTrnDate,
RI.RctItem,
RI.RctQty,
RI.RctWhse,
RI.RctLoc,
RI.PoNum
FROM
cteRcptItems AS RI
JOIN cteShipItems AS SI
ON RI.item_rcpt_order = SI.item_ship_order AND
RI.RctItem = SI.ShpItem
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2011 at 8:30 am
Sorry it took me so long to get back to you. Thank you very much, Jack. It works perfectly.
April 14, 2011 at 8:34 am
Glad I could help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply