useing while loop - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

useing while loop

Author

Topic

carlossiwaily
Starting Member

24 Posts

Posted - 2006-04-23 : 16:39:58

Hi,

how can i loop through each record and perform some calculation. the sp i use is shown below.

CREATE PROCEDURE sp_getStatHO
@accountid int,
@startdate datetime,
@enddate datetime,
@bankid int

AS

SET DATEFORMAT dmy
DECLARE @balance Money

select Account_id, cast(paid_in_amount as decimal) as paid_in_amount,cast(paid_out_amount as decimal) as paid_out_amount, tran_date,bank_name,account_name,transactiontype,transactionNo,bank_ref_id,

if paid_in_amount > 0
Begin
@balance = @balance + paid_in_amount
end
Else If paid_out_amount > 0
begin
@balance = @balance + paid_in_amount
End

FROM tbltran
WHERE tran_date >= @startdate
AND tran_date <= @enddate and account_id = @accountid and bank_name = @bankid and deleted = 0
ORDER BY tran_date

I want achive the following

Account_id, paid_in_amount,paid_out_amount, balance
1 500 0 500
2 0 200 300

thanks for you help

carlossiwaily

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-23 : 23:47:51

no cursor...use case

select Account_id, cast(paid_in_amount as decimal) as paid_in_amount,
cast(paid_out_amount as decimal) as paid_out_amount,
tran_date,bank_name,account_name,transactiontype,transactionNo,bank_ref_id,
case when paid_in_amount > 0 then @balance + paid_in_amount
when paid_out_amount > 0 then @balance + paid_in_amount
End as 'balance' FROM tbltran
WHERE tran_date >= @startdate
AND tran_date <= @enddate and account_id = @accountid and bank_name = @bankid and deleted = 0
ORDER BY tran_date

--edit
oh you need to get a running total, just saw the if there

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-23 : 23:48:51

try this

select 	Account_id, cast(paid_in_amount as decimal) as paid_in_amount, cast(paid_out_amount as decimal) as paid_out_amount, 
       	tran_date, bank_name, account_name, transactiontype, transactionNo, bank_ref_id, b.balance
FROM 	tbltran t inner join 
(
	select	Account_id, sum(paid_in_amount - paid_out_amount) as balance
	from	tbltran
	where	tran_date	>= @startdate
	and	tran_date	<= @enddate
	and	account_id 	= @accountid 
	and 	bank_name 	= @bankid 
	and 	deleted 	= 0
) b
on	t.Account_id = b.Account_ID
WHERE 	tran_date >= @startdate
AND 	tran_date <= @enddate 
and 	account_id = @accountid 
and 	bank_name = @bankid 
and 	deleted = 0
ORDER BY tran_date

carlossiwaily
Starting Member

24 Posts

Posted - 2006-04-24 : 07:59:54

hi,

I need to display the balance after each transaction on the account. just like bank statment.

thanks

carlossiwaily

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-24 : 08:02:58

Post your table structure, some sample data and the result that you want

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-24 : 08:31:42

sounds like a variation on the 'running total' problem....example of which can be found in the FAQ or by searching previous posts on this site.
I agree with khtan....(more detailed) sample input/output data is a big help.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-04-24 : 08:53:20

Running totals? Opportunity to advertise my article: http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

carlossiwaily
Starting Member

24 Posts

Posted - 2006-04-24 : 08:58:06

[code]
Table Structure

TRID int
ACCOUNT_ID int
PAID_IN_amount money
PAID_OUT_amount money
TRAN_DATE smalldatetime
BANK_REF_ID nvarchar 50
BANK_Name nvarchar 50
Account_name nvarchar 50
Extra_Notes nvarchar 600
Enter_date datetime
transactiontype nvarchar 50
transactionNo nvarchar 50
deleted int
bab_NO int

--------------------

CREATE PROCEDURE sp_getStatHO
@accountid int,
@startdate datetime,
@enddate datetime,
@bankid int

AS

SET DATEFORMAT dmy

select Account_id, cast(paid_in_amount as decimal) as paid_in_amount,cast(paid_out_amount as decimal) as paid_out_amount, tran_date,bank_name,account_name,transactiontype,transactionNo,bank_ref_id

FROM tbltran

WHERE tran_date >= @startdate
AND tran_date <= @enddate and account_id = @accountid and bank_name = @bankid and deleted = 0
ORDER BY tran_date

-----
want to achive the following result.

Tran_date ___paid_in_amount ___paid_out_amount balance
12/03/2006 400 0 400

13/03/2006 0 200 200

14/04/2006 0 100 100

As you can see the balance colunm is calculated based on the value of paid_in_amount or paid_out_amount and the balance from the Previous record. I think i need to have some sort of while loop
[/code]

Thanks for your help

carlossiwaily

carlossiwaily
Starting Member

24 Posts

Posted - 2006-04-24 : 17:09:03

please i need help on above post.

carlossiwaily

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-04-25 : 02:40:46

You have already recieved help. Have you read a faq or the article I posted link to?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 03:15:23

try this. If it is still not what you want, please post your table structure and same sample data. Having these information provided will help us to help you get to the solution that you want


select Account_id, cast(paid_in_amount as decimal) as paid_in_amount, cast(paid_out_amount as decimal) as paid_out_amount,    
       tran_date, bank_name, account_name, transactiontype, transactionNo, bank_ref_id
       balance = ( select sum(paid_in_amount - paid_out_amount) from tbltran x 
                      where x.account_id = t.account_id and x.bank_name = t.bank_name and deleted = 0 and x.tran_date <= t.tran_date)
FROM   tbltran t
WHERE  tran_date  >= @startdate
AND    tran_date  <=  @enddate   
and    account_id = @accountid 
and    bank_name  = @bankid 
and    deleted    = 0  
ORDER BY tran_date

carlossiwaily
Starting Member

24 Posts

Posted - 2006-04-25 : 14:15:23

Hi,

I have posted my table structures and example of how i want the result. please have a look.

mmarovic : i read your post but did not help my case.

thanks

carlossiwaily

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources