[Resolved] Split sum into separate variables

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

General SQL Server Forums

New to SQL Server Programming

[Resolved] Split sum into separate variables

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-31 : 12:28:19

I have a sp like this. The ttl_qty is the current sum variable.

CASE dbo.SourceType.CompanySourceTypeId
WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity)
ELSE SUM(dbo.ProductionEvent.AlternateQuantity)
END
AS ttl_qty, dbo.SourceType.CompanySourceTypeId,

I need to split the sum into separate variables, so when a 'PR' I need to add sum to a ttl_qty1 variable otherwise I need to add sum to a ttl_qty2 variable. How can I do this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 13:49:59

SELECT
...
ttl_qty1 = CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END
,ttl_qty2 = CASE dbo.SourceType.CompanySourceTypeId WHEN 'somethingelse' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END
..
FROM ...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-01-31 : 14:23:26

Thank you, that really helped and worked.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources