CODEWITHSUNDEEP
sql-server
dazzathedrummer
dazzathedrummer

Reputation: 531

MS SQL case statements and referring to results

This may be quite a dumb question for somebody who has been using SQL for around 12 years but...

In the accounting database that I use, all numbers are stored as positive numbers......in other words the net value of an invoice is stored as a positive number and so is the net value of a credit note......the transactions 'TranType' is used to differentiate between the two ('Inv' for invoice, 'CRN' for credit note), so, whenever I have to write a report that uses transactional data, I always have to write a case statement...

case when TranType = 'INV' then Net else NetVal *-1 end as 'Net'

...I'm wondering if there is any way to create a column within a specific report that I can refer to without having to retype the case statement over and over again (ie so I can refer to 'Net' which gives the results of the case statement)?

thanks,

Darren

Upvotes: 1

Views: 72

Answers (2)

dazzathedrummer
dazzathedrummer

Reputation: 531

I've gone for a view in this case as this is an 'off the shelf' accounting database and I do not want to interfere with the structure.

The concept of computed columns is something that I've not come across before. I think I will investigate them further as I think they could be very useful to me.

Thanks to all.

Darren

Upvotes: 1

Szymon
Szymon

Reputation: 43023

Yes, you can do that. You can create a column like that in your CREATE TABLE or ALTER TABLE:

[TrueTotal]  AS (case when TranType = 'INV' then Net else NetVal *-1 end)

e.g.

CREATE TABLE [dbo].[mytable](
    [TranType] varchar(3) not null,
    Net decimal(20,2) not null,
    NetVal decimal(20,2) not null,
    [TrueTotal]  AS (case when TranType = 'INV' then Net else NetVal *-1 end)
)

Upvotes: 2

Related Questions