Increase or decr running total based on trantype

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)

Increase or decr running total based on trantype

Author

Topic

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-02-13 : 15:15:24

I need help with a query to aggregate extcost values per jobnum. Here is a simplified version of my table.

CREATE TABLE parttran (
jobnum varchar (14),
trantype varchar (7),
extcost decimal(16, 2),
)

I was all set to use the SUM aggregate function and group by jobnum till I discovered that some trantypes need to be subtracted from the total (even though the associated extcost value is a positive number) while others need to be added. Converting the data is not an option.

So my questions is, how can I construct a query that adds or subtracts extcost to/from a running total for each jobnum group depending on the trantype?

Thanks in advance,

Ahmet

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 15:42:05

Try this:


Select
jobnum,
Sum(Case When trantype = 'MyDecreaseType' Then -1 * excost Else excost End)
From parttran
Group By jobnum Order By jobnum

ahmeterispaha
Starting Member

19 Posts

Posted - 2008-02-14 : 08:37:03

Thank you Qualis. That is perfect!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources