November 7, 2011 at 7:12 am
Suppose I have a transaction record like:
TransactionID LedgerID Period Amount Paid
1 100 1 500 300
2 100 2 200 150
3 100 3 100 100
The total amount for 3 periods = 800 and the total paid is 550. My task is to take the total paid and offset it from the very first transaction to calculate the balance
i.e For TransactionID = 1 Amount = 500 and total paid for the ledger is 550 so 500 can be offsetted here leaving a balance of 0
For TransactionID = 2 the Amount is 200, and we only have a paid amount of 50 left after offsetting TransactionID 1 so the balance of TransactionID 2 = 200 - 50 = 150
And for TransactionID = 3 we dont have any paid amount left so balance = 100 - 0 = 100
Is there any simple way of achieving this result using t-sql query?
The other option I thought of using was a ratio of totalpaid/totalamount * individual amount but i end up having fraction of a cent (Assuming all the amount and paid are in cents), so offsetting is the approach i want to take.
Any help will be appreciated
November 7, 2011 at 7:50 am
Sounds like a running total problem. Check out this article from Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url] Make sure you read all the way and pay close attention to the quirky update method. Think that is exactly what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 8:24 am
rka (11/7/2011)
Suppose I have a transaction record like:TransactionID LedgerID Period Amount Paid
1 100 1 500 300
2 100 2 200 150
3 100 3 100 100
The total amount for 3 periods = 800 and the total paid is 550. My task is to take the total paid and offset it from the very first transaction to calculate the balance
i.e For TransactionID = 1 Amount = 500 and total paid for the ledger is 550 so 500 can be offsetted here leaving a balance of 0
For TransactionID = 2 the Amount is 200, and we only have a paid amount of 50 left after offsetting TransactionID 1 so the balance of TransactionID 2 = 200 - 50 = 150
And for TransactionID = 3 we dont have any paid amount left so balance = 100 - 0 = 100
Is there any simple way of achieving this result using t-sql query?
The other option I thought of using was a ratio of totalpaid/totalamount * individual amount but i end up having fraction of a cent (Assuming all the amount and paid are in cents), so offsetting is the approach i want to take.
Any help will be appreciated
I don't really understand your business logic, so this isn't quite correct.
--Let's create a test environment to play with!
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
CREATE TABLE #testEnvironment(
TransactionID INT, LedgerID INT, Period INT, Amount INT, Paid INT)
--Now fill the test environment up with the sample data
INSERT INTO #testEnvironment(TransactionID, LedgerID, Period, Amount, Paid)
SELECT 1, 100, 1, 500, 300
UNION ALL SELECT 2, 100, 2, 200, 150
UNION ALL SELECT 3, 100, 3, 100, 100
--OK, on to the problem. First we need a temporary storage space for the result-set
IF object_id('tempdb..#Results') IS NOT NULL
BEGIN
DROP TABLE #Results
END
--Second, let's work out the total paid and stick it in the temporary result-set
SELECT TransactionID, LedgerID, Period, Amount, Paid, 0 AS Balance,
totalPaid
INTO #Results
FROM #testEnvironment
CROSS APPLY (SELECT SUM(Paid) AS totalPaid FROM #testEnvironment) a
DECLARE @row INT = 1, @RowCount INT = 1
--Set the balance for the first transaction ID
UPDATE #Results
SET Balance = totalPaid - Amount
WHERE TransactionID = 1
--Work out the balance for the remaining transaction IDs
WHILE @RowCount > 0
BEGIN
SET @row = @row + 1
UPDATE nxt
SET Balance = nxt.Amount - prv.Balance
FROM #Results nxt
INNER JOIN #Results prv
ON prv.TransactionID = @row-1
WHERE nxt.TransactionID = @row
SET @RowCount = @@ROWCOUNT
END
--Display result-set
SELECT TransactionID, LedgerID, Period, Amount, Paid, Balance
FROM #Results
This returns the following.
TransactionID LedgerID Period Amount Paid Balance
------------- ----------- ----------- ----------- ----------- -----------
1 100 1 500 300 50
2 100 2 200 150 150
3 100 3 100 100 -50
As I said, I'm aware this doesn't match your requested result, but does it help you to figure out what you need to do?
If not, I could do with a bigger sample data-set so I can work out what you're trying to do.
November 8, 2011 at 6:26 am
I was expecting the result to be like:
TransactionID LedgerID Period Amount Paid Balance
------------- ----------- ----------- ----------- ----------- -----------
1 100 1 500 500 0
2 100 2 200 50 150
3 100 3 100 0 100
Still haven't figured out how this can be achieved. Basically I am trying to apportion totalpaid against the individual transaction from the very first transaction
Rule:
We know that:
- totalamount = 800
- totalpaid = 550
Now we start creating running total (substraction) from the first transaction
i.e. First Transaction has Amount = 500. TotalPaid for the Ledger = 550. So we can take 500 out of 550 and put it against the first transaction to say it is paid, so balance = 0
Left totalpaid is 50 which is brought forward to the next transaction, so we apply 50 to this 2nd transaction and the balance left is 150
For 3rd transaction, we don't have any balance left as we applied 500 & 50 to 1st and 2nd transactions respectively, so paid remains as 0 and balance remains as 100
November 8, 2011 at 7:50 am
Can you put together some ddl, sample data (insert statements) and desired output based on your sample data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2011 at 10:40 pm
Finally managed to resolve this. The full solution is:
--------------------------------------
-- CREATE DATABASE
--------------------------------------
CREATE DATABASE TEST
GO
USE [TEST]
GO
--------------------------------------
-- DROP TABLE
--------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_Apportion]') AND type in (N'U'))
DROP TABLE [dbo].[Test_Apportion]
GO
--------------------------------------
-- CREATE TABLE
--------------------------------------
CREATE TABLE [dbo].[Test_Apportion](
[TransactionID] [float] NULL,
[LedgerID] [float] NULL,
[PeriodID] [float] NULL,
[RateTypeID] [float] NULL,
[TransactionAmount] [float] NULL,
[PAID] [float] NULL,
[TOTALPAID] [float] NULL,
[MainRank] [float] NULL,
[TransactionRank] [float] NULL
) ON [PRIMARY]
--------------------------------------
-- INSERT DATA
--------------------------------------
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (100, 1000, 3, 33, 83.5, 84.26)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (101, 1000, 3, 33, 0.58, 84.26)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (102, 1000, 3, 33, 0.18, 84.26)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (110, 1000, 4, 16, 179.75, 179.75)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (111, 1000, 4, 16, 0.27, 179.75)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (112, 1000, 4, 16, -0.27, 179.75)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (120, 1000, 4, 33, 83.5, 83.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (121, 1000, 4, 33, 0.74, 83.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (122, 1000, 4, 33, 0.84, 83.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (123, 1000, 4, 33, 0.13, 83.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (124, 1000, 4, 33, -0.74, 83.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (125, 1000, 4, 33, -0.84, 83.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (126, 1000, 4, 33, -0.13, 83.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (130, 1000, 5, 16, 186.22, 186.22)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (140, 1000, 5, 33, 86.5, 86.5)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (150, 1000, 6, 33, 86.5, 1.98)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (160, 2000, 4, 23, 0.15, 1.59)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (161, 2000, 4, 23, 1.46, 1.59)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (162, 2000, 4, 23, 0.01, 1.59)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (170, 2000, 4, 10, 179.75, 178.99)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (171, 2000, 4, 10, 0.27, 178.99)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (172, 2000, 4, 10, 1.52, 178.99)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (180, 2000, 4, 34, 83.5, 84.34)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (181, 2000, 4, 34, 0.13, 84.34)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (182, 2000, 4, 34, 0.71, 84.34)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (190, 2000, 4, 51, 15.64, 15.58)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (191, 2000, 4, 51, 0.02, 15.58)
INSERT [dbo].[Test_Apportion] ([TransactionID], [LedgerID], [PeriodID], [RateTypeID], [TransactionAmount], [TOTALPAID]) VALUES (192, 2000, 4, 51, 0.13, 15.58)
------------------------------------
-- Original Result
------------------------------------
Select *
From dbo.Test_Apportion
Order By MainRank, TransactionRank;
------------------------------------
-- Implement Ranks
------------------------------------
-- Common Table Express Definition
With cte_ranks
AS
(SELECT
TransactionID
,PeriodID
,RateTypeID
,LedgerID
,TransactionAmount AS Amount
,PAID AS Paid
,TOTALPAID AS TotalPaid
,DENSE_RANK() OVER (ORDER BY LedgerID, PeriodID, RateTypeID ASC) AS MainRank -- don't do partition here to get continuous numbering
,DENSE_RANK() Over (PARTITION BY LedgerID, PeriodID, RateTypeID ORDER BY TransactionID ASC) AS TransactionRank
FROM dbo.Test_Apportion
)
-- Update table with rank values
Update b
SET b.mainrank = a.MainRank, b.transactionrank = a.TransactionRank
From cte_ranks a
Inner Join
dbo.Test_Apportion b
On a.TransactionID = b.TransactionID;
------------------------------------
-- Apply Apportion
------------------------------------
-- Declare variables
DECLARE @mainrank int
DECLARE @transactionrank int
DECLARE @totalpaid money
-- Initialise the variables
SET @mainrank = 0
SET @transactionrank = 0
SET @totalpaid = 0
---------< MAIN LOOP >----------
WHILE @mainrank <= (SELECT MAX(mainrank) FROM dbo.Test_Apportion)
BEGIN
-- assign totalpaid value for each main loop into a variable
SET @totalpaid = (SELECT top 1 totalpaid FROM dbo.Test_Apportion WHERE mainrank = @mainrank)
---------< TRANSACTION LOOP >----------
WHILE @transactionrank <= (SELECT MAX(transactionrank) FROM dbo.Test_Apportion WHERE mainrank = @mainrank)
BEGIN
-- update paid field in apportion table
UPDATE a
SET a.Paid = (CASE
WHEN @totalpaid > a.TransactionAmount AND a.TransactionAmount > 0 THEN a.TransactionAmount
WHEN @totalpaid < a.TransactionAmount AND @totalpaid > 0 THEN @totalpaid
WHEN @totalpaid = a.TransactionAmount THEN a.TransactionAmount
ELSE 0
END)
FROM dbo.Test_Apportion a
WHERE mainrank = @mainrank
AND transactionrank = @transactionrank
-- Now reduce the amount that is offsetted from totalpaid
SET @totalpaid = @totalpaid -(SELECT paid
FROM dbo.Test_Apportion
WHERE MainRank = @mainrank
AND transactionrank = @transactionrank
)
-- move to next transaction record
SET @transactionrank = @transactionrank + 1
END
-- move to next main record
SET @mainrank = @mainrank + 1
-- initialise the next level transaction rank back to 1
SET @transactionrank = 1
END;
------------------------------------
-- Expected Result
------------------------------------
Select *
From dbo.Test_Apportion
Order By MainRank, TransactionRank;
November 9, 2011 at 2:47 am
When 2012 ships (or in the current public CTP 3) you could solve the problem without any looping:
CREATE TABLE #TranRecord
(
TransactionID INTEGER NOT NULL,
LedgerID INTEGER NOT NULL,
Period TINYINT NOT NULL,
Amount MONEY NOT NULL,
Paid MONEY NOT NULL
)
INSERT #TranRecord
(
TransactionID,
LedgerID,
Period,
Amount,
Paid
)
SELECT 1, 100, 1, $500, $300 UNION ALL
SELECT 2, 100, 2, $200, $150 UNION ALL
SELECT 3, 100, 3, $100, $100
SELECT
q3.TransactionID,
q3.LedgerID,
q3.Period,
q3.Amount,
q3.Paid,
balance = CASE WHEN q3.balance > $0 THEN q3.balance ELSE $0 END
FROM
(
-- Compute balance
SELECT
q2.*,
balance = q2.Amount - CASE WHEN q2.credit > 0 THEN q2.credit ELSE $0 END
FROM
(
-- Compute remaining credit
SELECT
q1.*,
credit = q1.total_paid - ISNULL(q1.total_amount, $0)
FROM
(
-- Add total paid per ledger and lag(1) running total on amount
SELECT
tr.*,
total_paid = SUM(tr.Paid) OVER (
PARTITION BY tr.LedgerID),
total_amount = SUM(tr.Amount) OVER (
PARTITION BY tr.LedgerID
ORDER BY tr.TransactionID
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM #TranRecord AS tr
) AS q1
) AS q2
) AS q3
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply