View - Selecting next row column value and running balance 2

[jgurgen]

my view is set up like a check register.

Problem 1:
I have a transactiondate field and a nextTdate field where the transactiondate is the date the item was entered into the system and the nextTdate is to be based off of the next rows transactiondate. If no next row exists then use todaysdate.

Problem 2:
Im trying to do a running balance in the view. Each BuyAmountPayment should subtract off of the originalamount - previous payments made. This way when the balance is 0 they are all paid. How do i do a running balance in a view?

My View

SELECT M.LoanID, P.HistoryCounter, M.OriginalBuyAmount, M.DatePaidOff, M.OpenDate, P.BuyAmountPayment, P.TransactionDate, P.TransactionCode,
P.PayDownInterest,
(SELECT P2.TransactionDate
FROM dbo.iSplit_BuyAmount_Payments P2
WHERE P2.LoanID = P.LoanID AND P2.HistoryCounter = P.HistoryCounter) AS NextTDate,
M.OriginalBuyAmount - P.BuyAmountPayment AS Balance, dbo.iSplit_InterestRate.BuyAmountRate * .01 AS InterestRate
FROM dbo.iSplit_BuyAmount_Main M INNER JOIN
dbo.iSplit_BuyAmount_Payments P ON M.LoanID = P.LoanID INNER JOIN
dbo.iSplit_InterestRate ON M.LoanID = dbo.iSplit_InterestRate.LoanID
WHERE (P.PayDownInterest = 1)

 

[gmmastros]

Running totals is best handled using a cursor. There are several example on the internet the describe why the cursor solution is best. Unfortunately, cursors cannot be used in a view. If you can implement this through a stored procedure, then the cursor solution should work well. Take a look here:

http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jgurgen]

i need to put the running total in a report in crytal reports thats why im trying to do it in the view. There is no other way to do it in a view?

 

[gmmastros]

Look at the Correlated Query Solution section of the link I provided. You should be able to implement that method within a view (you'll need to remove the NOCOUNT lines). This is not the most efficient way to get your results.

I haven't used crystal reports in years, but I'm fairly certain you can call stored procedures from Crystal. I'm sure others can demonstrate how to do that.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jgurgen]

thansk i got the running total working with a minor problem now. The first resut is NULL where it should be OriginalBuyAmount

(SELECT M.OriginalBuyAmount - SUM(P3.BuyAmountPayment)
FROM dbo.iSplit_BuyAmount_Payments P3
WHERE P3.LoanID=P.LoanID and P3.HistoryCounter < P.HistoryCounter) AS Balance,

 

[jgurgen]

NM Running total complete

(SELECT M.OriginalBuyAmount - SUM(P3.BuyAmountPayment)
FROM dbo.iSplit_BuyAmount_Payments P3
WHERE P3.LoanID=P.LoanID and P3.HistoryCounter <= P.HistoryCounter AND P3.PayDownInterest=1) AS Balance,

works now

Still need help on next tran date

 

[gmmastros]

Maybe ???

Change:
P3.HistoryCounter < P.HistoryCounter

To:
P3.HistoryCounter <[!]=[/!] P.HistoryCounter

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[johnscott76]

Here's a little test script I set up to demonstrate this in a view. Just copy & paste into Query Analyzer and execute.

Hope this helps!

Use tempdb
Go

If Exists( Select * From sysobjects Where Name = 'RT' And Type = 'U') Drop Table RT
If Exists( Select * From sysobjects Where Name = 'vRT' And Type = 'V') Drop View vRT
Go

Create Table RT
(
  RTKey
    int Not Null
    Identity(1,1)
  ,
  RTDate
    datetime Not Null
  ,
  RTDesc
    varchar(100) Not Null
  ,
  RTAmount
    money Not Null
)
Go

Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-01', 'Deposit', 1000)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-02', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-03', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-04', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-05', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-06', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-07', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-08', 'Deposit', 1500)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-09', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-10', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-11', 'Withdrawal', -100)
Insert Into RT (RTDate, RTDesc, RTAmount) Values ('2007-01-12', 'Deposit', 1500)
Go

Select *
  From RT
Go

Create View vRT
As
  Select RTKey, RTDate, RTDesc, RTAmount,
         ( Select Sum(RTAmount)
             From RT As RunningTotal
             Where RT.RTKey >= RunningTotal.RTKey
         ) As RunningTotal
   From RT
Go

Select *
  From vRT
Go

 

[jgurgen]

Worked well thanks,

How do i select the next rows transactiondate

(SELECT P2.TransactionDate
FROM dbo.iSplit_BuyAmount_Payments P2
WHERE P2.LoanID = P.LoanID AND P2.HistoryCounter=
(SELECT TOP 1 P4.HistoryCounter
FROM dbo.iSplit_BuyAmount_Payments P4
WHERE P4.LoanID = P2.LoanID AND P2.HistoryCounter <= P4.HistoryCounter AND P4.PayDownInterest=1
ORDER BY P4.HistoryCounter DESC) AND P2.PayDownInterest=1)
AS ABCDEFG,

but this is returning multiple rows for each line causing an error.

 

[jgurgen]

UPDATE ON the nexttran amount pelase help

SELECT     M.LoanID, P.HistoryCounter, M.OriginalBuyAmount, M.DatePaidOff, M.OpenDate, P.BuyAmountPayment, P.TransactionDate, P.TransactionCode, 
                      P.PayDownInterest,
/************************** PROBLEM START  *********************************/
			(SELECT Case WHEN (Select TOP 1 B.TransactionDate 
					FROM dbo.iSplit_BuyAmount_Payments B 
					WHERE B.LoanID=A.LoanID AND B.HistoryCounter>A.HistoryCounter AND B.PayDownInterest=1) is null
				Then (Select GetDate()) 
				ELSE (Select TOP 1 B.TransactionDate 
					FROM dbo.iSplit_BuyAmount_Payments B 
					WHERE B.LoanID=A.LoanID AND B.HistoryCounter=A.HistoryCounter AND B.PayDownInterest=1)
				END AS NextDate
			FROM dbo.iSplit_BuyAmount_Payments A 
			WHERE A.PayDownInterest=1 and A.HistoryCounter = P.HistoryCounter),
/************************** PROBLEM END *********************************/
                          (SELECT P2.TransactionDate
                            FROM dbo.iSplit_BuyAmount_Payments P2
                            WHERE P2.LoanID = P.LoanID AND P2.HistoryCounter < P.HistoryCounter AND P2.PayDownInterest=1) AS NextTDate, 
			(SELECT M.OriginalBuyAmount - SUM(P3.BuyAmountPayment)
			FROM dbo.iSplit_BuyAmount_Payments P3
			WHERE P3.LoanID=P.LoanID and P3.HistoryCounter <= P.HistoryCounter AND P3.PayDownInterest=1) AS Balance,
                      	I.BuyAmountRate * .01 AS InterestRate
FROM         dbo.iSplit_BuyAmount_Main M INNER JOIN
                      dbo.iSplit_BuyAmount_Payments P ON M.LoanID = P.LoanID INNER JOIN
                      dbo.iSplit_InterestRate I ON M.LoanID = I.LoanID
WHERE     (P.PayDownInterest = 1)

RESULT OF QUERY

(2 row(s) affected)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Select Case WHEN (Select TOP 1 B.TransactionDate 
		FROM dbo.iSplit_BuyAmount_Payments B 
		WHERE B.LoanID=A.LoanID AND B.HistoryCounter>A.HistoryCounter AND B.PayDownInterest=1) is null
	Then (Select GetDate()) 
	ELSE (Select TOP 1 B.TransactionDate 
		FROM dbo.iSplit_BuyAmount_Payments B 
		WHERE B.LoanID=A.LoanID AND B.HistoryCounter>A.HistoryCounter AND B.PayDownInterest=1)
END AS NextDate
from dbo.iSplit_BuyAmount_Payments A 
where A.PayDownInterest=1

REUTRNS CORRECTLY
2007-01-05 00:00:00.000
2007-01-07 00:00:00.000
2007-01-12 00:00:00.000
2007-01-12 16:30:54.530

 

[jgurgen]

FIXED!!!!!!!!!!

SELECT     M.LoanID, P.HistoryCounter, M.OriginalBuyAmount, M.DatePaidOff, M.OpenDate, P.BuyAmountPayment, P.TransactionDate, P.TransactionCode, 
                      P.PayDownInterest,
			(Select Case WHEN (Select TOP 1 B.TransactionDate 
			FROM dbo.iSplit_BuyAmount_Payments B 
			WHERE B.LoanID=A.LoanID AND B.HistoryCounter>A.HistoryCounter AND B.PayDownInterest=1) is null
			Then (Select GetDate()) 
			ELSE (Select TOP 1 B.TransactionDate 
			FROM dbo.iSplit_BuyAmount_Payments B 
			WHERE B.LoanID=A.LoanID AND B.HistoryCounter>A.HistoryCounter AND B.PayDownInterest=1)
			END
			from dbo.iSplit_BuyAmount_Payments A 
			where A.PayDownInterest=1 AND A.HistoryCounter=P.HistoryCounter and A.LoanID=P.LoanID) AS NextDate,
			(SELECT M.OriginalBuyAmount - SUM(P3.BuyAmountPayment)
			FROM dbo.iSplit_BuyAmount_Payments P3
			WHERE P3.LoanID=P.LoanID and P3.HistoryCounter <= P.HistoryCounter AND P3.PayDownInterest=1) AS Balance,
                      	I.BuyAmountRate * .01 AS InterestRate			
FROM         dbo.iSplit_BuyAmount_Main M INNER JOIN
                      dbo.iSplit_BuyAmount_Payments P ON M.LoanID = P.LoanID INNER JOIN
                      dbo.iSplit_InterestRate I ON M.LoanID = I.LoanID
WHERE     (P.PayDownInterest = 1)