Conditional calculated field how to

Question

Conditional calculated field how to

PSSQL

Mr or Mrs. 500

Points: 570
More actions
February 26, 2010 at 2:52 am

#103905

I am trying to apply the following rule to a calculated amount in a SQL SELECT, but the "Abs(Amount >0)" and "PaymentType" part does not work. I am thinking about those results are boolean, and are not compatible with the multiplication.
The idea is to add to the Amount field the TotalVAT field only in the records where the Amount field is a positive number, and the PaymentType is of type "CASH". In all the other records the TotalVAT field must be set to 0.
SELECT (Amount + TotalVAT * Abs(Amount > 0) * Abs(PaymentType <> 'CASH')) AS Total

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide more details. The best waa would be to post ready to use sample data (including table def, sample data and expected result). For details please see the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PSSQL Mr or Mrs. 500 Points: 570 More actions · Answer 2

Hello lmu92,

Thanks for your interest, please note that the SELECT command of my other post is exactly what I want to use.

Of course in the SELECT part, and in the WHERE part are more fields and conditions, but for a mean of simplicity and ease of reading, I expose only the problematic part.

Imagine a simple SQL like

SELECT Field1, Field2, Field3, (Amount + TotalVAT * Abs(Amount > 0) * Abs(PaymentType <> 'CASH')) AS Total

FROM TableX

WHERE Date >= @d1 AND Date <= @d2

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

See it if the following solution works.

SELECTField1, Field2, Field3,

Amount + ( CASE WHEN Amount > 0 AND PaymentType = 'CASH' THEN TotalVAT ELSE 0 END ) AS Total

FROM TableX

WHEREDate >= @d1 AND Date <= @d2

--Ramesh

LutzM SSC Guru Points: 107049 More actions · Answer 4

I have to repeat my previous post:

Please provide more details. The best way would be to post ready to use sample data (including table def, sample data and expected result). For details please see the first link in my signature.

Please note that we can't look over your shoulder so we don't see what you see.

To restate what you want to use doesn't really help since that query won't work.

You could try something like:

SELECT someCol,

SUM(CASE WHEN condition1 AND condition2 THEN value1 ELSE value2 END) AS result

FROM table

GROUP BY someCol

If you need more precise information please provide more details.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PSSQL Mr or Mrs. 500 Points: 570 More actions · Answer 5

Hello Ramesh,

Thanks for your help, this is exactly what I needed.

Thanks again.