seeking help in query.. - 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

Transact-SQL (2000)

seeking help in query..

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-01-16 : 07:38:48

I have 2 fields : ca, da
this query is working :

select (ca - da) amt
from tbl_name where condition.

but i want the following :

select (amt + ca - da) from tbl_name....

in this case the "amt" field does not exist. it is just an alias.

how can i make it work?

any help is appreciated.

thanks and regards.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 07:41:41

1 select (ca - da) + ( ca - da) from tbl_name where condition
2 select (amt + ca - da) from
(select (ca - da) as amt ,ca ,da from tbl_name where condition) as t

Madhivanan

Failing to plan is Planning to fail

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-01-17 : 00:37:23

it was my fault i didn't clearly asked what i am trying to do.

Thanks for the reply madhivanan.
the query is wrking but giving me wrong result.

i two fields da and ca has following values :
da ca
0.0 200.0
0.0 100.0
600.0 0.0
0.0 600

i want the following result set :
here 'amt' is just an alias.

da ca amt
0.0 200.0 -200
0.0 100.0 -300
600.0 0.0 300
0.0 600 -300

the first row (da - ca) = -200, amt = 0
so, da - ca + amt = -200
i.e, amt = -200

second row (da - ca) = -100, amt = -200
so, da - ca + amt = -300

and so on.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-17 : 00:41:46

the amt is cummulative ? Do you have a primary key in the table ?

KH
[spoiler]Time is always against us[/spoiler]

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-01-17 : 00:53:47

the "amt" is an alias, i name (da - ca) as amt in the select query.

i don't have any primary key in the table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-17 : 01:16:24

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-01-17 : 01:26:13

sorry madhivanan, i didn't quite understand your question.

the result set will be returned to the vb.net 2005 application. i will show it on grid.
the result set will be something like that of a bank pass-book
the two rows will calculate, keep data in another field, then the value of the third field will be used again in the calculation.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-17 : 01:40:09

You must have some other column other then da, ca right ? a date column may be ? Something to determine the sequence order for showing the data ?

DECLARE @sample TABLE
(
	da decimal(10,1),
	ca decimal(10,1)
)

INSERT INTO @sample
SELECT	  0.0, 200.0 UNION ALL
SELECT	  0.0, 100.0 UNION ALL
SELECT	600.0,   0.0 UNION ALL
SELECT	  0.0, 600

;WITH data(da, ca, row_no) AS
(
	SELECT	da, ca, row_no = row_number() OVER (ORDER BY da, ca)
	FROM	@sample
)
SELECT	da, ca, amt = (SELECT SUM(da - ca) FROM data x WHERE x.row_no <= d.row_no)
FROM	data d

/*
da           ca           amt                                      
------------ ------------ ---------------------------------------- 
          .0        100.0                                   -100.0 
          .0        200.0                                   -300.0 
          .0        600.0                                   -900.0 
       600.0           .0                                   -300.0 

(4 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-01-17 : 02:20:09

after executing the code verbatim the following error is thrown.

Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Server: Msg 195, Level 15, State 1, Line 15
'row_number' is not a recognized function name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-17 : 02:55:52

Oops sorry. You are using SQL Server 2000.

Well, do you have a primary key in the table ? Maybe an identity column ?

KH
[spoiler]Time is always against us[/spoiler]

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-01-17 : 05:17:55

it's ok khtan.
thanks for trying to help me.

no, i have no primary key.
i have no identity column.
i have a date field though, but it will have duplicate values.

i can solve the problem by maybe writing SP, or doing something in the vb.net code.

but i thought, i should write a query.
and now i am stuck.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-17 : 06:16:01

quote:
Originally posted by cyberpd

it's ok khtan.
thanks for trying to help me.

no, i have no primary key.
i have no identity column.
i have a date field though, but it will have duplicate values.

i can solve the problem by maybe writing SP, or doing something in the vb.net code.

but i thought, i should write a query.
and now i am stuck.

You can do it in VB.NET
Goto the second page of this thread and see my first response for example (Running Total)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76862

Madhivanan

Failing to plan is Planning to fail

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2008-01-18 : 00:12:07

firstly i would like to thank you both for trying to help me.

madhivanan, thanks for the link. it has good code examples.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 01:02:23

quote:
Originally posted by cyberpd

firstly i would like to thank you both for trying to help me.

madhivanan, thanks for the link. it has good code examples.

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources