How can I use a IF in my querye? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How can I use a IF in my querye?

Author

Topic

tejo_pan
Starting Member

10 Posts

Posted - 2005-07-10 : 13:43:41

Dears, hello!!
I'm from Brazil an this is my first topic here in this forum.

I wold like to know, how can I use the IF/ELSE to return a value for each row of the results..

Follow the query...

=====================================================
SELECT
ClienteID,
SUM(ValorTransacaoBruto) AS Total,
CONVERT(CHAR(10), DATEADD(mm, -1, GETDATE()), 103) AS DataAnterior,
CONVERT(CHAR(10), GETDATE(), 103) AS DataAtual,
( -- HERE IS WITH ERROR
IF Total <= 1.01 SELECT Total
ELSE IF Total <= 3000 SELECT Total * 0.059
ELSE IF Total <= 10000 SELECT Total * 0.049
ELSE IF Total <= 100000 SELECT Total * 0.039
ELSE IF Total > 100000 SELECT Total * 0.029
) -- HERE END THE ERROR
FROM tbTransacoes
WHERE
(DebitoCredito = 'C')
AND
(
DataTransacao BETWEEN
CONVERT(CHAR(10), DATEADD(mm, -1, GETDATE()), 103)
AND CONVERT(CHAR(10), GETDATE(), 103)
)
GROUP BY ClienteID
ORDER BY ClienteID
GO
=============================================

Thank you!!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2005-07-10 : 13:52:23

Check out the CASE function. Your code should look like this:


(CASE WHEN Total <= 1.01 THEN Total 
ELSE (CASE WHEN Total <= 3000 THEN Total * 0.059
ELSE (CASE WHEN Total <= 10000 THEN Total * 0.049
ELSE (CASE WHEN Total <= 100000 THEN Total * 0.039
ELSE (CASE WHEN Total > 100000 THEN Total * 0.029 END)
END) END) END) END)

Sarah Berger MCSD

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-10 : 14:41:11

You don't need all those ELSE statements. Each condition of the CASE statement will be evaluated sequentially, so all you need is this:

(CASE WHEN Total <= 1.01 THEN Total
WHEN Total <= 3000 THEN Total * 0.059
WHEN Total <= 10000 THEN Total * 0.049
WHEN Total <= 100000 THEN Total * 0.039
WHEN Total > 100000 THEN Total * 0.029 END)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources