Loop through a table and apply payments? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Loop through a table and apply payments?

Author

Topic

LearningAsIGo
Starting Member

9 Posts

Posted - 2012-06-07 : 02:52:21

Hi,

If you have some time to look at this problem, I'd be very thankful :).

Quick note: I have edited the text for clarity.

There are two tables, one that shows invoices (#Values) and one that shows payments (#MnyAssigner). All invoices are given a priority rating, and the payments have to be assigned to the invoices in order of that priority (Priority Order).

All invoices and payments are divided into EntryKinds (in this sample, the EntryKinds are 'Basic' and 'Additional.') All payments for a particular EntryKind have to be assigned to the same EntryKind in the invoice.

Additionally, all invoices are assigned to a main group. I would like to assign the payments to the invoices based on group, priority order and entry kind, but I don't know where to start.

I've put a sample #Values table (invoices) and a sample #MnyAssigner table (payments), along with an expected results sample #ValuesFinal. Some of the sample data is repetitive, but this is just a sample :).

Thank you for any help!

Note: Some of the sample data is repetitive.

Sample values:

CREATE TABLE #Values (GrpNr VARCHAR(20), OrderId INT, InvoiceId INT,PriorityOrder INT, InvDate SMALLDATETIME, EntryKind VARCHAR(10), Amt MONEY)
INSERT INTO #Values (GrpNr, OrderId, InvoiceId, PriorityOrder, InvDate, EntryKind, Amt)
SELECT  'A',  123, 1500, 1,  '2010-01-01', 'Basic', 350  UNION ALL 
SELECT  'A',  123, 1620, 2 , '2010-02-02', 'Basic', 700  UNION ALL 
SELECT  'A',	123, 1730, 3 , '2010-03-01', 'Basic', 400  UNION ALL 
SELECT	'A',	123, 1800, 4,  '2010-04-01', 'Basic', 200  UNION ALL
SELECT 	'A',	123, 1500, 1,  '2010-01-01', 'Additional', 100 UNION ALL
SELECT 	'A',	123, 1730, 3,  '2010-03-01', 'Additional', 55 UNION ALL
SELECT 	'A',	456, 2167, 1,  '2010-04-03', 'Basic', 670 UNION ALL
SELECT 	'A',	456, 2230, 2,  '2010-05-01', 'Basic', 137 UNION ALL
SELECT 	'A',	456, 2679, 3,  '2010-06-01', 'Basic', 251 UNION ALL
SELECT 	'A',	456, 3251, 4,  '2010-07-01', 'Basic', 276 UNION ALL
SELECT 	'A',	456, 2167, 1,  '2010-04-03', 'Additional', 200 UNION ALL
SELECT 	'A',	456, 2230, 2,  '2010-05-01', 'Additional', 542 UNION ALL
SELECT 	'A',	456, 2679, 3,  '2010-06-01', 'Additional', 313 UNION ALL
SELECT 	'A',	789, 3450, 1,  '2010-02-01', 'Basic', 350 UNION ALL
SELECT 	'A',	789, 3451, 2,  '2010-03-05', 'Basic', 100 UNION ALL
SELECT 	'A',	372, 5671, 1,  '2010-07-06', 'Basic', 200 UNION ALL
SELECT 	'A',	372, 5671, 1,  '2010-07-06', 'Additional', 100 UNION ALL 
SELECT  'B',  111, 1500, 1,  '2010-01-01', 'Basic', 350  UNION ALL 
SELECT  'B',  111, 1620, 2 , '2010-02-02', 'Basic', 700  UNION ALL 
SELECT  'B',	111, 1730, 3 , '2010-03-01', 'Basic', 400  UNION ALL 
SELECT	'B',	111, 1800, 4,  '2010-04-01', 'Basic', 200  UNION ALL
SELECT 	'B',	111, 1500, 1,  '2010-01-01', 'Additional', 100 UNION ALL
SELECT 	'B',	111, 1730, 3,  '2010-03-01', 'Additional', 55 UNION ALL
SELECT 	'B',	444, 2167, 1,  '2010-04-03', 'Basic', 670 UNION ALL
SELECT 	'B',	444, 2230, 2,  '2010-05-01', 'Basic', 137 UNION ALL
SELECT 	'B',	444, 2679, 3,  '2010-06-01', 'Basic', 251 UNION ALL
SELECT 	'B',	444, 3251, 4,  '2010-07-01', 'Basic', 276 UNION ALL
SELECT 	'B',	444, 2167, 1,  '2010-04-03', 'Additional', 200 UNION ALL
SELECT 	'B',	444, 2230, 2,  '2010-05-01', 'Additional', 542 UNION ALL
SELECT 	'B',	444, 2679, 3,  '2010-06-01', 'Additional', 313 UNION ALL
SELECT 	'B',	777, 3450, 1,  '2010-02-01', 'Basic', 350 UNION ALL
SELECT 	'B',	777, 3451, 2,  '2010-03-05', 'Basic', 100 UNION ALL
SELECT 	'B',	333, 5671, 1,  '2010-07-06', 'Basic', 200 UNION ALL
SELECT 	'B',	333, 5671, 1,  '2010-07-06', 'Additional', 100



		

CREATE TABLE #MnyAssigner (GrpNr VARCHAR(20), OrderId INT, EntryKind VARCHAR(10), Amt MONEY)
INSERT INTO #MnyAssigner (GrpNr, OrderId, EntryKind, Amt) 
SELECT	'A',	123, 'Basic', 1420 UNION ALL
SELECT	'A',  123, 'Additional', 150 UNION ALL
SELECT	'A',	456,  'Basic', 1000 UNION ALL
SELECT	'A',	456, 'Additional', 500 UNION ALL
SELECT	'A',	789, 'Basic', 400 UNION ALL 
SELECT	'B',	111, 'Basic', 1420 UNION ALL
SELECT	'B',  111, 'Additional', 150 UNION ALL
SELECT	'B',	444,  'Basic', 1000 UNION ALL
SELECT	'B',	444, 'Additional', 500 UNION ALL
SELECT	'B',	777, 'Basic', 400

Expected Results:


CREATE TABLE #ValuesFinal (GrpNr VARCHAR(20), OrderId INT, InvoiceId INT,PriorityOrder INT, InvDate SMALLDATETIME, EntryKind VARCHAR(10), Amt MONEY, FinalAmt MONEY)
INSERT INTO #ValuesFinal (GrpNr, OrderId, InvoiceId, PriorityOrder, InvDate, EntryKind, Amt, FinalAmt)
SELECT 	'A',	123, 1500, 1,  '2010-01-01', 'Basic', 350, 0  UNION ALL 
SELECT 	'A',	123, 1620, 2 , '2010-02-01', 'Basic', 700, 0  UNION ALL 
SELECT 	'A',	123, 1730, 3 , '2010-03-01', 'Basic', 400, 30  UNION ALL 
SELECT 	'A',	123, 1800, 4,  '2010-04-01', 'Basic', 200, 200  UNION ALL
SELECT 	'A',	123, 1500, 1,  '2010-01-01', 'Additional', 100, 0 UNION ALL
SELECT 	'A',	123, 1730, 3,  '2010-03-01', 'Additional', 55, 5 UNION ALL
SELECT 	'A',	456, 2167, 1,  '2010-04-03', 'Basic', 670, 0 UNION ALL
SELECT 	'A',	456, 2230, 2,  '2010-05-01', 'Basic', 137, 0 UNION ALL
SELECT 	'A',	456, 2679, 3,  '2010-06-01', 'Basic', 251, 58 UNION ALL
SELECT 	'A',	456, 3251, 4,  '2010-07-01', 'Basic', 276, 276 UNION ALL
SELECT 	'A',	456, 2167, 1,  '2010-04-03', 'Additional', 200, 50 UNION ALL
SELECT 	'A',	456, 2230, 2,  '2010-05-01', 'Additional', 542, 542 UNION ALL
SELECT 	'A',	456, 2679, 3,  '2010-06-01', 'Additional', 313, 313 UNION ALL
SELECT 	'A',	789, 3450, 1,  '2010-02-01', 'Basic', 350, 0 UNION ALL
SELECT 	'A',	789, 3451, 2,  '2010-03-05', 'Basic', 100, 50 UNION ALL
SELECT 	'A',	372, 5671, 1,  '2010-07-06', 'Basic', 200, 200 UNION ALL
SELECT 	'A',	372, 5671, 1,  '2010-07-06', 'Additional', 100, 100 UNION ALL 
SELECT 	'B',	111, 1500, 1,  '2010-01-01', 'Basic', 350, 0  UNION ALL 
SELECT 	'B',	111, 1620, 2 , '2010-02-01', 'Basic', 700, 0  UNION ALL 
SELECT 	'B',	111, 1730, 3 , '2010-03-01', 'Basic', 400, 30  UNION ALL 
SELECT 	'B',	111, 1800, 4,  '2010-04-01', 'Basic', 200, 200  UNION ALL
SELECT 	'B',	111, 1500, 1,  '2010-01-01', 'Additional', 100, 0 UNION ALL
SELECT 	'B',	111, 1730, 3,  '2010-03-01', 'Additional', 55, 5 UNION ALL
SELECT 	'B',	444, 2167, 1,  '2010-04-03', 'Basic', 670, 0 UNION ALL
SELECT 	'B',	444, 2230, 2,  '2010-05-01', 'Basic', 137, 0 UNION ALL
SELECT 	'B',	444, 2679, 3,  '2010-06-01', 'Basic', 251, 58 UNION ALL
SELECT 	'B',	444, 3251, 4,  '2010-07-01', 'Basic', 276, 276 UNION ALL
SELECT 	'B',	444, 2167, 1,  '2010-04-03', 'Additional', 200, 50 UNION ALL
SELECT 	'B',	444, 2230, 2,  '2010-05-01', 'Additional', 542, 542 UNION ALL
SELECT 	'B',	444, 2679, 3,  '2010-06-01', 'Additional', 313, 313 UNION ALL
SELECT 	'B',	777, 3450, 1,  '2010-02-01', 'Basic', 350, 0 UNION ALL
SELECT 	'B',	777, 3451, 2,  '2010-03-05', 'Basic', 100, 50 UNION ALL
SELECT 	'B',	333, 5671, 1,  '2010-07-06', 'Basic', 200, 200 UNION ALL
SELECT 	'B',	333, 5671, 1,  '2010-07-06', 'Additional', 100, 100

--Select all three tables 

SELECT * FROM #Values 
SELECT * FROM #MnyAssigner
SELECT * FROM #ValuesFinal

--Clean everything up

--DROP TABLE #MnyAssigner
--DROP TABLE #Values
--DROP table #ValuesFinal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 00:07:37

[code]
SELECT v.*,COALESCE(v2.PrevAmt,0),
CASE WHEN COALESCE(v2.PrevAmt,0) > a.Amt THEN (a.Amt-COALESCE(v2.PrevAmt,0)) + (COALESCE(v2.PrevAmt,0) + v.Amt) - a.Amt WHEN COALESCE(v2.PrevAmt,0) + v.Amt > a.Amt THEN COALESCE(v2.PrevAmt,0) + v.Amt - a.Amt ELSE 0 END AS FinalAmt
FROM #Values v
CROSS APPLY (SELECT SUM(Amt) AS PrevAmt
FROM #Values
WHERE GrpNr = v.GrpNr
AND OrderId = v.OrderId
AND EntryKind = v.EntryKind
AND PriorityOrder < v.PriorityOrder
)v2
CROSS APPLY (SELECT Amt
FROM #MnyAssigner
WHERE GrpNr = v.GrpNr
AND OrderId = v.OrderId
AND EntryKind = v.EntryKind
)a
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningAsIGo
Starting Member

9 Posts

Posted - 2012-06-09 : 12:16:08

Thank you for the great solution and for your time!

That is so much better than the cursor I was about to start writing.

quote:

Originally posted by visakh16


SELECT v.*,COALESCE(v2.PrevAmt,0),
CASE WHEN COALESCE(v2.PrevAmt,0) > a.Amt THEN  (a.Amt-COALESCE(v2.PrevAmt,0)) + (COALESCE(v2.PrevAmt,0) + v.Amt) -  a.Amt WHEN COALESCE(v2.PrevAmt,0) + v.Amt > a.Amt THEN COALESCE(v2.PrevAmt,0) + v.Amt -  a.Amt ELSE 0 END AS FinalAmt
FROM #Values v
CROSS APPLY (SELECT SUM(Amt) AS PrevAmt
             FROM #Values
             WHERE GrpNr = v.GrpNr
             AND OrderId = v.OrderId
             AND EntryKind = v.EntryKind
             AND PriorityOrder < v.PriorityOrder
             )v2
CROSS APPLY (SELECT Amt
             FROM #MnyAssigner 
             WHERE GrpNr = v.GrpNr
             AND OrderId = v.OrderId
             AND EntryKind = v.EntryKind
             )a

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 13:38:15

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources