SQL select statement - 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 2005 Forums

Transact-SQL (2005)

SQL select statement

Author

Topic

pssumesh2003
Starting Member

35 Posts

Posted - 2009-10-14 : 00:54:04

Hi every body

I am a new in sql server 2005. in my project
i have 2 columns deposit,withdrawal. i want to insert into another table
deposit,withdrawal,balance(2000) using select statement

i want set balance in a way that
value of balance in previous row + value of deposit in same row Deposit - value of deposit in same row Withdral

please give the sample query

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-14 : 01:29:29

post the complete table Structure. How do you find the previous row? by transaction date? which balance will you take if there are more than one transaction on a single date?. probably there will be one more column like VoucherNo/InvoiceNo.etc.

pssumesh2003
Starting Member

35 Posts

Posted - 2009-10-14 : 02:21:31

Hi Sanoj

i have 2 columns deposit,withdrawal. i want to insert into another temp table
deposit,withdrawal,balance(2000) using select statement
I try
insert into #newtable(deposit,withdrawal,balance) select deposit,withdrawal,balance+deposit-withdrawal.
but it did'nt give the desierd output

i want set balance in a way that
value of balance in previous row + value of deposit in same row Deposit - value of deposit in same row Withdral

please give the sample query

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-14 : 02:41:24

Deposit Withdrawal Balance
100 50 50
200 25 225
125 200 150
150 50 250

For example,how do you take the Previous Balance of the 3rd record? Will it be 225? If yes, you are relying on the defaul order on which SQL server sorts the record.The Order is not guaranteed on a heap table when you query it next time unless you specify a sort column.

pssumesh2003
Starting Member

35 Posts

Posted - 2009-10-14 : 02:49:39

dear Sanoj

yes, i want out put like that in temp table.
but how to insert into that table ?

Kabila
Starting Member

33 Posts

Posted - 2009-10-14 : 03:14:58

First You move table output into #tmp table @ what order you what
create table #tmp(id int identity(1,1),deposit numeric(9,2),withdrawal numeric(9,2))
insert into #tmp(deposit,withdrawal)
select 5000,2000
union all
select 1000,2000
union all
select 2000,1000
union all
select 3000,1000

with cte as
(
select top 1 id, deposit,withdrawal ,cast(deposit-withdrawal as numeric(9,2)) as balance from #tmp order by id
union all
Select a.id,a.deposit,a.withdrawal, cast(balance+a.deposit-a.withdrawal as numeric(9,2)) as balance
from #tmp a ,cte b
where a.id=b.id+1
) select deposit,withdrawal ,deposit,withdrawal from cte;

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-14 : 03:23:44

create Table #AccTrans(deposit money,withdrawal money)

Insert into #AccTrans
SELECT 100,50 UNION ALL
SELECT 200,25 UNION ALL
SELECT 125,200 UNION ALL
SELECT 150,50

create Table #AccBal(deposit money,withdrawal money,Balance Money)
go
With CTE AS
(Select row_number() over(order by (select 1)) as SrNo,deposit,withdrawal from #AccTrans)
insert into #AccBal(deposit,withdrawal,Balance)
Select
deposit,
withdrawal,
(Select sum(deposit) from CTE B Where B.srNo<=A.srNo)-(Select sum(withdrawal) from CTE B Where B.srNo<=A.srNo) AS Bal
From CTE A

Select * from #AccTrans
Select * from #AccBal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources