June 27, 2014 at 8:18 am
Hi
I've been trying to solve this for hours and clearly could do with some help! I am basically trying to update a table which reflects account transactions. Accounts get paid in full but occasionally balance payments can be reversed and I want to update the table to show this - I need to show which period the account was previously paid in full.
I've created a simplified version of the scenario and below are a couple of examples of things I've tried that do not work. I understand why they do not work but I'm struggling to figure out how to update the 'PeriodPrevPaidInFull' field.
Thanks
Adrian
create table Trans
(
AccNo int,
Transaction_Period_Index int,
PeriodOpeningBalance money,
DebtBalance money,
PeriodPaidInFull int NULL,
PeriodPrevPaidInFull int NULL,
)
go
INSERT INTO [dbo].[Trans]
(AccNo
,Transaction_Period_Index
,PeriodOpeningBalance
,DebtBalance
,PeriodPaidInFull
,PeriodPrevPaidInFull
)
VALUES
(2482592,4,167,0,NULL,NULL)
,(2482642,4,202,0,NULL,NULL)
,(2482642,5,0,202,NULL,NULL)
,(2482642,8,202,0,NULL,NULL)
,(2482642,9,0,202,NULL,NULL)
,(2482642,10,202,0,NULL,NULL)
,(2482645,10,202,0,NULL,NULL)
GO
--Select * from Trans
--UPDATE PaidInFull. (This bit is ok).
UPDATE Trans SET PeriodPaidInFull =
Transaction_Period_Index WHERE DebtBalance <= 0.00
--NOW I NEED TO UPDATE PeriodPrevPaidInFull
--Below ARE SOME EXAMPLES WHICH DON'T WORK....
--SELECT FAILS IF MORE THAN ONE RECORD.
UPDATE Trans SET PeriodPrevPaidInFull =
(SELECT t1.Transaction_Period_Index FROM Trans t1,Trans
WHERE t1.AccNo = Trans.AccNo
AND t1.DebtBalance <= 0.00
AND Trans.DebtBalance > 0.00
AND Trans.PeriodOpeningBalance = 0.00
AND t1.Transaction_Period_Index < Trans.Transaction_Period_Index)
--ONLY UPDATES THE MOST RECENT TRANSACTION PERIOD.
UPDATE Trans SET PeriodPrevPaidInFull =
(SELECT
QueryPeriodPrevPIF.Transaction_Period_Index
from
(
select ROW_NUMBER() OVER (PARTITION BY tPrevious.AccNo ORDER BY tPrevious.Transaction_Period_Index DESC) AS 'RN'
,tPrevious.AccNo
,tPrevious.Transaction_Period_Index
FROM Trans tNewer,Trans tPrevious
WHERE tPrevious.AccNo = tNewer.AccNo
AND tPrevious.DebtBalance <= 0.00
AND tNewer.DebtBalance > 0.00
AND tNewer.PeriodOpeningBalance = 0.00
AND tPrevious.Transaction_Period_Index < tNewer.Transaction_Period_Index
)AS QueryPeriodPrevPIF
where QueryPeriodPrevPIF.RN = 1
AND QueryPeriodPrevPIF.AccNo = Trans.AccNo
AND Trans.DebtBalance > 0.00
AND Trans.PeriodOpeningBalance = 0.00
AND Trans.Transaction_Period_Index > QueryPeriodPrevPIF.Transaction_Period_Index
)
June 27, 2014 at 8:30 am
Is it possible to restructure your table? The reason you are really struggling with this is because your data is not properly normalized. You are trying to store totals on a line by line basis and constantly updating existing transaction data. It would be a lot easier if you split this into some additional tables.
Account
Loan/Debt
Payment
This would make things a lot easier and at some point the auditing of information is clear. The way you are doing this you are changing the value of a debt as a payment is made. How do you know how much the original amount was when you update it?
_______________________________________________________________
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/
June 27, 2014 at 8:37 am
Hi
It's a simplified example and i do have more detailed data i.e. the transactions and payments. Nevertheless I need the data in this format, as it is supposed to read similar to a bank statement.
Thanks
June 27, 2014 at 8:53 am
WADRIAN68 (6/27/2014)
HiIt's a simplified example and i do have more detailed data i.e. the transactions and payments. Nevertheless I need the data in this format, as it is supposed to read similar to a bank statement.
Thanks
I would do the calculations directly from the base tables instead of trying to do it after the fact. Would save you tons of headaches.
_______________________________________________________________
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/
June 27, 2014 at 9:00 am
thanks but that's not an option, the base tables just show payments, positive or negative, what I'm trying to do is create a statement style table. Which I've done but I just want to flag on the table when an account is paid in full and when a subsequent reverse payment moves that account back into the red.
June 27, 2014 at 9:11 am
WADRIAN68 (6/27/2014)
thanks but that's not an option, the base tables just show payments, positive or negative, what I'm trying to do is create a statement style table. Which I've done but I just want to flag on the table when an account is paid in full and when a subsequent reverse payment moves that account back into the red.
Presumably you are running query that pulls this data right? I would add this logic into that query instead of pulling the data and then trying to figure out what happened.
_______________________________________________________________
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/
June 27, 2014 at 9:12 am
You might be able to do it from this table but you need something to use as an ordering column. There is nothing in your current table to know what is "first" or "previous"
_______________________________________________________________
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/
June 27, 2014 at 9:15 am
The Transaction_Period_Index is the ordering column. it shows the period when any activity occured on an account.
June 27, 2014 at 9:19 am
WADRIAN68 (6/27/2014)
The Transaction_Period_Index is the ordering column. it shows the period when any activity occured on an account.
So the order within a period doesn't matter?
What is the output you expect from 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/
June 29, 2014 at 5:38 am
Hi
Firstly, if I understand your question correctly, no the order within a period doesn't matter as each record is already an aggregation of lower level transactions.
Secondly, I have arrived at a solution over the weekend, which I'll detail below and this demonstrates the output I'm after.
Thirdly, the solution I arrived at uses a cursor based approach. Now I know that is usually met with shock, horror, and derision but it's what I had to resort to in the absence of an alternative. I remain very keen to learn if there is a better set-based approach and so on.
And finally, thanks for your interest. Here is the solution (the creation of the table is different to my earlier example, I'm using a temp table to reduce the records the cursor needs to process, so have included an identity column).
drop table Trans
create table Trans
(
TransSummID int identity,
AccNo int,
Transaction_Period_Index int,
PeriodOpeningBalance money,
DebtBalance money,
PeriodPaidInFull int NULL,
PeriodPrevPaidInFull int NULL,
)
go
INSERT INTO [dbo].[Trans]
(AccNo
,Transaction_Period_Index
,PeriodOpeningBalance
,DebtBalance
,PeriodPaidInFull
,PeriodPrevPaidInFull
)
VALUES
(2482592,4,167,0,NULL,NULL)
,(2482642,4,202,0,NULL,NULL)
,(2482642,5,0,202,NULL,NULL)
,(2482642,8,202,0,NULL,NULL)
,(2482642,9,0,202,NULL,NULL)
,(2482642,10,202,0,NULL,NULL)
,(2482645,10,202,0,NULL,NULL)
GO
--UPDATE PaidInFull.
UPDATE Trans SET PeriodPaidInFull =
Transaction_Period_Index WHERE DebtBalance <= 0.00
--TO UPDATE PeriodPrevPaidInFull WE'LL USE A CURSOR BASED APPROACH. IN THIS EXAMPLE THE TEMP TABLE IS USED AS A WAY TO PROCESS ONLY THOSE RECORDS WE MAY WANT TO UPDATE.
IF OBJECT_ID('tempdb..#tmpPay') IS NOT NULL
DROP TABLE #tmpPay
select * into #TmpPay from Trans where periodpaidinfull is not null --JUST THE RECORDS WE MAY NEED TO UPDATE THE PeriodPrevPaidInFull FIELD.
SET NOCOUNT ON;
DECLARE @AccNo int, @PeriodPaidInFull int, @outerloop int, @innerloop int
DECLARE caseCursor CURSOR FOR
SELECTAccNo
,PeriodPaidInFull
FROM TmpPay
OPEN caseCursor
FETCH NEXT FROM caseCursor INTO @AccNo,@PeriodPaidInFull
set @outerloop = @@FETCH_STATUS
WHILE @outerloop = 0
BEGIN
UPDATE #TmpPay SET #TmpPay.PeriodPrevPaidInFull =
(
SELECT
QMostRecentLastPeriod.MostRecentPeriodPIF
FROM
(SELECT
t1.AccNo
,MAX(t1.PeriodPaidInFull) as MostRecentPeriodPIF
FROM TmpPay t1
WHERE t1.AccNo = @AccNo
AND t1.PeriodPaidInFull < @PeriodPaidInFull
GROUP BY t1.AccNo
)AS QMostRecentLastPeriod
)
WHERE #TmpPay.AccNo = @AccNo
AND #TmpPay.PeriodPaidInFull = @PeriodPaidInFull
FETCH NEXT FROM caseCursor INTO @AccNo,@PeriodPaidInFull
set @outerloop = @@FETCH_STATUS
END
CLOSE caseCursor
DEALLOCATE caseCursor
--NOW UPDATE PeriodPrevPaidInFull
UPDATE Trans SET PeriodPrevPaidInFull =
(
SELECT #tmpPay.PeriodPrevPaidInFull FROM #tmpPay
WHERE #tmpPay.TransSummID = Trans.TransSummID
)
--Look at the output.
select * from Trans
June 30, 2014 at 7:42 am
Glad you found a working solution. Even more glad you are willing to see if somebody can come up with another approach to get rid of the cursor.
In this example I replaced your entire looping construct including the temp table with a single update statement. There may be a more efficient way to do this but this should be way better than a cursor.
drop table Trans
create table Trans
(
TransSummID int identity,
AccNo int,
Transaction_Period_Index int,
PeriodOpeningBalance money,
DebtBalance money,
PeriodPaidInFull int NULL,
PeriodPrevPaidInFull int NULL,
)
go
INSERT INTO [dbo].[Trans]
(AccNo
,Transaction_Period_Index
,PeriodOpeningBalance
,DebtBalance
,PeriodPaidInFull
,PeriodPrevPaidInFull
)
VALUES
(2482592,4,167,0,NULL,NULL)
,(2482642,4,202,0,NULL,NULL)
,(2482642,5,0,202,NULL,NULL)
,(2482642,8,202,0,NULL,NULL)
,(2482642,9,0,202,NULL,NULL)
,(2482642,10,202,0,NULL,NULL)
,(2482645,10,202,0,NULL,NULL);
UPDATE Trans SET PeriodPaidInFull =
Transaction_Period_Index WHERE DebtBalance <= 0.00;
--This cte and subsequent update replaces your entire cursor.
with MyTrans as
(
select * from Trans
)
update MyTrans set PeriodPrevPaidInFull =
case when DebtBalance > 0.00 then NULL
else
(select top 1 PeriodPaidInFull
from Trans t
where t.DebtBalance <= 0.00
and t.Transaction_Period_Index < MyTrans.Transaction_Period_Index
and t.AccNo = MyTrans.AccNo
order by t.PeriodPaidInFull desc)
end
select * from Trans
_______________________________________________________________
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/
July 1, 2014 at 4:13 am
Hi
That is a really fantastic solution, it works perfectly and I've learnt that changes to a cte are cascaded to the source table, I will have to read up more on CTEs.
Thanks so much!
Regards
Adrian
July 1, 2014 at 7:22 am
WADRIAN68 (7/1/2014)
HiThat is a really fantastic solution, it works perfectly and I've learnt that changes to a cte are cascaded to the source table, I will have to read up more on CTEs.
Thanks so much!
Regards
Adrian
You are welcome. Glad that works for you. 🙂
_______________________________________________________________
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/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply