CODEWITHSUNDEEP
sqlsyntaxformula
Wil-Liam
Wil-Liam

Reputation: 49

Sql - Case expression with And, and Or

I could do some help with the following formula. It currently works, but I want to add another part to it, which I cannot figure out how to do

The current formula is:

,CASE
WHEN 
ABS(((t1.[RateAmount] - t1.[Amount]) / NULLIF (t1.[Amount],0) *100)) >1  
OR ABS(((t2.volume - t1.TotalVolume) / NULLIF (t1.TotalVolume, 0) *100)) >5 
THEN 1 ELSE 0 
END AS OverallThresholdDifference

However I need to add in to the volume line, if over 5 AND a difference of 2000 and my mind has gone blank

Any help here would be really appreciated

Cheers

Upvotes: 0

Views: 38

Answers (2)

Cloud
Cloud

Reputation: 237

 WHERE t2.volume > 5 AND OverallThresholdDifference > 2000

TO get the OverallThresholdDifference in your where clause, you will need to derive the entire query.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

add another when

CASE
WHEN 
ABS(((t1.[RateAmount] - t1.[Amount]) / NULLIF (t1.[Amount],0) *100)) >1  
OR ABS(((t2.volume - t1.TotalVolume) / NULLIF (t1.TotalVolume, 0) *100)) >5 
THEN 1 
when
ABS(((t1.[RateAmount] - t1.[Amount]) / NULLIF (t1.[Amount],0) *100)) >5  
and ABS(((t2.volume - t1.TotalVolume) / NULLIF (t1.TotalVolume, 0) *100)) >2000 
THEN 2 

ELSE 0 
END AS OverallThresholdDifference

Upvotes: 1

Related Questions