need to add computated column from an existing tab

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)

need to add computated column from an existing tab

Author

Topic

Ronesh
Starting Member

33 Posts

Posted - 2009-08-31 : 10:20:37

Dear all,
i have little problem while creating report.

i do have following table.

table1

date particulare dr cr

2005/01/01 test 100
2005/01/02 test 1000
2005/03/05 test 200
2005/03/25 test 5000
2005/05/01 test 6000

now i need a report like as below

date particulare dr cr balance

2005/01/01 test 100 100
2005/01/02 test 1000 1100(i.e. 1000+100)
2005/03/05 test 200 900(i.e. 1100-200)
2005/03/25 test 5000 -4100(i.e. 900-5000)
2005/05/01 test 6000 1900(i.e. 4100+6000)

please help me
i need it quickly.
i have been trying to get this but couldn't.
so,please help me any one.
looking forward for quick and positive response.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-31 : 13:29:04

try this:


select t.[date]
       ,t.particular
       ,t.dr
       ,t.cr
       ,ca.balance
from [table1] as t
cross apply (
       select sum(cr) balance
       from [table1]
       where [date] <= t.[date]
       ) ca

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 03:22:37

How do you identity if a value is credit or debit?

Madhivanan

Failing to plan is Planning to fail

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-01 : 03:56:47

How do you know 200 and 5000 are debit?

Rahul Shinde

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 04:52:54

quote:
Originally posted by ra.shinde

How do you know 200 and 5000 are debit?

Rahul Shinde

That was my question too

Madhivanan

Failing to plan is Planning to fail

Ronesh
Starting Member

33 Posts

Posted - 2009-09-01 : 04:59:21

the row contains the both column both debit and credit.
if credit then the debit is null and vice versa.
i have created the view to get the report as under
date particular credit debit

now i need one more column 'Balance' as i have stated previously.

the Balance column has the summarized value of each transaction with reference to its previous value.

looking forward for positive response.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 05:05:40

Post proper sample data

Madhivanan

Failing to plan is Planning to fail

Ronesh
Starting Member

33 Posts

Posted - 2009-09-01 : 05:22:17

the source table

date particular debit credit
2001/01/01 test null 5000
2001/01/02 test 1000 null
2001/01/02 test 500 null
2001/01/02 test null 200
2001/01/05 test 100 null

now i need

date particular debit credit Balance
2001/01/01 test null 5000 5000
2001/01/02 test 1000 null 4000
2001/01/02 test 500 null 3500
2001/01/02 test null 200 3700
2001/01/05 test 100 null 3600

thanks a lot to you all for you have given the quick response.

looking for positive response.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-01 : 05:45:06

Do you have time also in date column? or Only Date with default time?

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-01 : 06:02:34

SELECT date, particular, debit, credit,
(SELECT SUM( -isnull(TB2.debit,0) + isnull(TB2.credit,0))
FROM @tb TB2
WHERE
TB2.date <= TB1.date)
FROM @tb TB1

Rahul Shinde

Ronesh
Starting Member

33 Posts

Posted - 2009-09-01 : 06:58:26

Dear Rahul,
i saw your script.thank you very much.
but i couldn't understand it.
let me show you the whole sceneraio

create table table1
(
date datetime,
particular varchar(15),
debit money,
credit money
)
insert into table1
values('2001/01/02','test',null,1000)]
go
insert into table1
values('2001/01/02','test',100,null)
go
insert into table1
values('2001/01/02','test',null,500)
go
insert into table1
values('2001/01/03','test',1000,null)
go

select * from table1
date particular debit credit
2001-01-01 00:00:00.000 test 1000.00 NULL
2001-01-02 00:00:00.000 test NULL 1000.00
2001-01-03 00:00:00.000 test 1000.00 NULL

now i want the report

date particular debit credit balance
2001-01-01 00:00:00.000 test 1000.00 NULL -1000
2001-01-02 00:00:00.000 test NULL 1000.00 0(-1000+1000)
2001-01-03 00:00:00.000 test 1000.00 NULL 1000(0+1000)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-09-01 : 07:05:43

Your table creation and population script does not match output data. You shown 4 inserts but in the output there are only 3 rows, that too not matching sample data.

Do you have identity column in the table? or any other unique key column?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Ronesh
Starting Member

33 Posts

Posted - 2009-09-01 : 07:15:19

Dear Harsh,
actually the result set is incorrect.
i just wanted to show you the sample data.
no i don't have any identity column.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-09-01 : 07:22:31

Assuming you are using SQL 2005, something like this will work. However, keep in mind that this approach only work if you are maintaining time part along with date, otherwise SQL Server has no way to know the order in which transactions took place.

declare @table1 table 
(
[date] datetime,
particular varchar(15),
debit money,
credit money
)
insert into @table1
values('2001/01/02','test',null,1000)

insert into @table1
values('2001/01/02','test',100,null)

insert into @table1
values('2001/01/02','test',null,500)

insert into @table1
values('2001/01/03','test',1000,null)

with temp
as
(
select row_number() over (order by [date]) as [ID], * from @table1
)
select [date], particular, debit, credit, 
(SELECT SUM( -isnull(t2.debit,0) + isnull(t2.credit,0)) 
FROM temp t2
WHERE 
t2.[ID] <= t1.[ID]) as Balance
 from temp t1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Ronesh
Starting Member

33 Posts

Posted - 2009-09-01 : 07:34:25

actually i am very new to sql field.
so please dont get annoyed.
i run the script given by you Harsh.
i guess you mean to say temp as the @table1.
and i changed temp to @table1.
now i got the error,

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-09-01 : 07:39:17

Run the solution as it is without changing table name or anything. It should work without any errors.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Ronesh
Starting Member

33 Posts

Posted - 2009-09-01 : 07:39:32

the same error even if i dont change temp to @table1.

Ronesh
Starting Member

33 Posts

Posted - 2009-09-01 : 07:43:24

Thank you very much Harsh.Thank you very very much.
You are genious.
It worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources