TSQL - Loop finances Items to payments
Hi Experts,
I need a program that will loop though all the possible payments someone makes.
So I have a InvoicePayment table:
I have a InvoiceItem table:
For this purpose please only for the matching:
Where InvoicePayment.paymentID = InvoiceItem.InvoiceNr
To Insert Into PaymentApplied table with colums:
ItemID, PaymentID, PaymentApplied
So this payment got applied to this itemID
If this post is confusing please say so, otherwise it looks almost like several that have been answered and I will need more clarity.
Please help and thanks
I need a program that will loop though all the possible payments someone makes.
So I have a InvoicePayment table:
PaymentID Payment
71 384.29
74 194.47
77 250
79 359.36
81 10.46
85 359.47
87 304.47
I have a InvoiceItem table:
itemID InvoiceNr Total
33194 74 350
47882 74 8
33195 75 10
47883 75 16.15
33196 76 295
47884 76 3.93
37960 77 445
37961 78 445
33197 79 185
33198 79 17.05
47885 79 32.3
39254 80 445
37596 81 430
33199 83 145
47886 83 4.1
33200 84 250
47887 84 30.39
33201 85 24.95
33202 85 24.95
47888 85 9.71
37962 86 430
33416 87 990
37963 88 430
39255 89 430
For this purpose please only for the matching:
Where InvoicePayment.paymentID = InvoiceItem.InvoiceNr
To Insert Into PaymentApplied table with colums:
ItemID, PaymentID, PaymentApplied
So this payment got applied to this itemID
If this post is confusing please say so, otherwise it looks almost like several that have been answered and I will need more clarity.
Please help and thanks
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Why inserting it into a new table? You can query it always on the fly. Sounds pretty redundant.
Also there is no loop needed. In T-SQL we use set-based approaches. Any loop while be a performance problem.
So simply use:
Also there is no loop needed. In T-SQL we use set-based approaches. Any loop while be a performance problem.
So simply use:
DECLARE @Payments TABLE
(
PaymentID INT ,
Payment FLOAT
);
INSERT INTO @Payments
VALUES ( 71, 384.29 ),
( 74, 194.47 ),
( 77, 250 ),
( 79, 359.36 ),
( 81, 10.46 ),
( 85, 359.47 ),
( 87, 304.47 );
DECLARE @Invoces TABLE
(
itemID INT ,
InvoiceNr INT ,
Total FLOAT
);
INSERT INTO @Invoces
VALUES ( 33194, 74, 350 ),
( 47882, 74, 8 ),
( 33195, 75, 10 ),
( 47883, 75, 16.15 ),
( 33196, 76, 295 ),
( 47884, 76, 3.93 ),
( 37960, 77, 445 ),
( 37961, 78, 445 ),
( 33197, 79, 185 ),
( 33198, 79, 17.05 ),
( 47885, 79, 32.3 ),
( 39254, 80, 445 ),
( 37596, 81, 430 ),
( 33199, 83, 145 ),
( 47886, 83, 4.1 ),
( 33200, 84, 250 ),
( 47887, 84, 30.39 ),
( 33201, 85, 24.95 ),
( 33202, 85, 24.95 ),
( 47888, 85, 9.71 ),
( 37962, 86, 430 ),
( 33416, 87, 990 ),
( 37963, 88, 430 ),
( 39255, 89, 430 );
SELECT *
FROM @Invoces I
INNER JOIN @Payments P ON I.InvoiceNr = P.PaymentID;
Just inserting the rows is very straightforward:
INSERT INTO #PaymentApplied ( ItemID, PaymentID, PaymentApplied )
SELECT ii.ItemID, ip.PaymentID, ip.Payment AS PaymentApplied
FROM #InvoicePayment ip
INNER JOIN #InvoiceItem ii ON
ii.InvoiceNr = ip.paymentID
WHERE
--ip.Payment IS NOT NULL AND --uncomment if needed
--don't allow duplicate insert for the same payment
NOT EXISTS(
SELECT 1
FROM #PaymentApplied pa
WHERE
pa.ItemID = ii.ItemID AND
pa.PaymentID = ip.PaymentID
)
/* create and load test tables
CREATE TABLE #InvoicePayment
(
PaymentID int NOT NULL,
Payment decimal(9, 2) NULL,
Has_Been_Applied bit NOT NULL
);
CREATE TABLE #InvoiceItem
(
ItemID int NOT NULL,
InvoiceNr int NULL,
Total decimal(9, 2) NULL
);
CREATE TABLE #PaymentApplied
(
ItemID int NOT NULL,
PaymentID int NOT NULL,
PaymentApplied decimal(9, 2) NOT NULL
);
INSERT INTO #InvoicePayment
VALUES ( 71, 384.29 ),
( 74, 194.47 ),
( 77, 250 ),
( 79, 359.36 ),
( 81, 10.46 ),
( 85, 359.47 ),
( 87, 304.47 );
INSERT INTO #InvoiceItem
VALUES ( 33194, 74, 350 ),
( 47882, 74, 8 ),
( 33195, 75, 10 ),
( 47883, 75, 16.15 ),
( 33196, 76, 295 ),
( 47884, 76, 3.93 ),
( 37960, 77, 445 ),
( 37961, 78, 445 ),
( 33197, 79, 185 ),
( 33198, 79, 17.05 ),
( 47885, 79, 32.3 ),
( 39254, 80, 445 ),
( 37596, 81, 430 ),
( 33199, 83, 145 ),
( 47886, 83, 4.1 ),
( 33200, 84, 250 ),
( 47887, 84, 30.39 ),
( 33201, 85, 24.95 ),
( 33202, 85, 24.95 ),
( 47888, 85, 9.71 ),
( 37962, 86, 430 ),
( 33416, 87, 990 ),
( 37963, 88, 430 ),
( 39255, 89, 430 );
*/
INSERT INTO #PaymentApplied ( ItemID, PaymentID, PaymentApplied )
SELECT ii.ItemID, ip.PaymentID, ip.Payment AS PaymentApplied
FROM #InvoicePayment ip
INNER JOIN #InvoiceItem ii ON
ii.InvoiceNr = ip.paymentID
WHERE
--ip.Payment IS NOT NULL AND --uncomment if needed
--don't allow duplicate insert for the same payment
NOT EXISTS(
SELECT 1
FROM #PaymentApplied pa
WHERE
pa.ItemID = ii.ItemID AND
pa.PaymentID = ip.PaymentID
)
/* create and load test tables
CREATE TABLE #InvoicePayment
(
PaymentID int NOT NULL,
Payment decimal(9, 2) NULL,
Has_Been_Applied bit NOT NULL
);
CREATE TABLE #InvoiceItem
(
ItemID int NOT NULL,
InvoiceNr int NULL,
Total decimal(9, 2) NULL
);
CREATE TABLE #PaymentApplied
(
ItemID int NOT NULL,
PaymentID int NOT NULL,
PaymentApplied decimal(9, 2) NOT NULL
);
INSERT INTO #InvoicePayment
VALUES ( 71, 384.29 ),
( 74, 194.47 ),
( 77, 250 ),
( 79, 359.36 ),
( 81, 10.46 ),
( 85, 359.47 ),
( 87, 304.47 );
INSERT INTO #InvoiceItem
VALUES ( 33194, 74, 350 ),
( 47882, 74, 8 ),
( 33195, 75, 10 ),
( 47883, 75, 16.15 ),
( 33196, 76, 295 ),
( 47884, 76, 3.93 ),
( 37960, 77, 445 ),
( 37961, 78, 445 ),
( 33197, 79, 185 ),
( 33198, 79, 17.05 ),
( 47885, 79, 32.3 ),
( 39254, 80, 445 ),
( 37596, 81, 430 ),
( 33199, 83, 145 ),
( 47886, 83, 4.1 ),
( 33200, 84, 250 ),
( 47887, 84, 30.39 ),
( 33201, 85, 24.95 ),
( 33202, 85, 24.95 ),
( 47888, 85, 9.71 ),
( 37962, 86, 430 ),
( 33416, 87, 990 ),
( 37963, 88, 430 ),
( 39255, 89, 430 );
*/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial