adding 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

SQL Server Development (2000)

adding query

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-04-24 : 22:16:21

I want to make a query that looks at a table and adds things together. here is my table make up. I have a table that data gets inserted. with; phone number, a positve or negative number, and type.

so it looks like

555555555 36 activatecost
555555555 -36 activatecost

what I would like to do is to get a query that will, if it sees the example above show

5555555555 0 activation cost, and of course if it only has a postive show the positive cost.

can such a beast like this be created? what functions like this should done. do I need to find Unique, then go back through and add and subtract? there is not to many only about twenty or so a month.

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-24 : 22:59:27

Could you post more examples of your data and an expanded result set?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-25 : 01:02:59

[code]-- prepare sample data
declare @sample table (phone varchar(15), cost int, code varchar(200))

insert @sample
select '555555555', 36, 'activatecost' union all
select '555555555', -36, 'activatecost' union all
select '111111111', 45, 'activatecost' union all
select '444444444', -9, 'activatecost'

--stage the data
declare @stage table (phone varchar(15), cost int, code varchar(200))

insert @stage
select phone,
0,
code
from @sample
group by phone,
code
having min(cost) + max(cost) = 0

-- Show the expected output
SELECT *
from @stage

union all

select s1.*
from @sample as s1
left join @stage as s2 on s2.phone = s1.phone and s2.code = s1.code
where s2.phone is null[/code]
Or have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80857

Peter Larsson
Helsingborg, Sweden

nhaas
Yak Posting Veteran

90 Posts

Posted - 2007-04-25 : 12:02:26

thank you for the held, the additional link helped alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources