Split/Extract Returns from Qty Field

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

Analysis Services (2000)

Split/Extract Returns from Qty Field

Author

Topic

bbrendan
Starting Member

3 Posts

Posted - 2004-10-22 : 11:23:10

HI All,

I have a fact table which contacts one field called QTY. This field contacts both credits and debits like

ISBN QTY
12344 1
12344 -5
12344 10
12344 -2
12344 1

How can I create a measure or a calculated member of this to break it out into two columns like

ISBN Debit Credit
12344 1 0
12344 0 -5
12344 10 0
12344 0 -2
12344 1 0

thanks

Kristen
Test

22859 Posts

Posted - 2004-10-23 : 00:03:10

[code]
SELECT ISBN,
[Debit] = CASE WHEN QTY > 0 THEN QTY ELSE 0 END,
[Credit] = CASE WHEN QTY < 0 THEN QTY ELSE 0 END
FROM Mytable
[/code]
In similar fashion you coudl make a VIEW onto the table with these extra "calcuated" columns; or you could add a couple of Computed Columns to the table (but in my experience Computed Columns in a table have been Bad News, and I would strongly caution against them)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources