Help with conditional statement

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

Transact-SQL (2000)

Help with conditional statement

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2008-03-05 : 09:17:24

Hi Guys

I was hoping someone could help me?

I have the following SELECT statement:

SELECT branchno AS Dept, SUM(adults) AS Adults, SUM(children) AS Kids, SUM(pax) AS Pax, SUM(grossprofit) AS Comm, SUM(tar_saleprice) AS TotalCost, SUM(CASE WHEN suboperator = 'DISCAT' OR suboperator = 'DISCNA' THEN grossprofit ELSE 0 END) AS Discount, (SUM(grossprofit)/(SUM(saleprice)+SUM(CASE WHEN suboperator = 'DISCAT' OR suboperator = 'DISCNA' THEN grossprofit ELSE 0 END)))*100 AS CommPerc

Which gives me the following results:

Branch1 | 2 | 2 | 4 | 205.00 | 2585.00 | -142.00 | 8.39%

This is fine when as in the example above the 'Comm' is higher than 0 however when it is a 0 value I get an error when trying to divide the result to provide the column CommPerc.

I want to be able to check if the 'Comm' column <> 0 and if it isn't then go ahead with the calculation otherwise return a zero value.

Any ideas?

PS. Sorry if this doesn't seem clear...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-05 : 09:22:41

Make use of derived table.

Something like this:

select 
...,
somecol/case when comm = 0 then 1 else comm end as somecol,
...
from
(select ...,SUM(grossprofit) AS Comm,...
from ...
) t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 09:24:31

or if you want null, then

somecol/nullif(comm,0)

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-05 : 09:24:46

Advice: use a derived table to make this simpler ... see the second (non-date) example in tip #2 here:

http://weblogs.sqlteam.com/jeffs/archive/2007/12/20/simplify-sql-with-variables-and-derived-tables.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Leigh79
Starting Member

28 Posts

Posted - 2008-03-05 : 09:44:53

Hi Guys

Thanks for you help, I've now got this working!

The new query is:

SELECT tar_branchno, SUM(tar_adults) AS Adults, SUM(tar_children) AS Kids, SUM(tar_pax) AS Pax,
SUM(tar_grossprofit) AS Comm, SUM(tar_saleprice) AS SalePrice, SUM(tar_disc) AS Disc,
CASE WHEN SUM(tar_grossprofit) = 0 THEN 0 ELSE (SUM(tar_grossprofit)/(SUM(tar_saleprice)+SUM(tar_disc)))*100 END AS Perc
FROM
(
SELECT tar_branchno, tar_adults, tar_children, tar_pax, tar_grossprofit, tar_saleprice,
CASE WHEN tar_suboperator = 'DISCAT' OR tar_suboperator = 'DISCNA' THEN tar_grossprofit ELSE 0 END AS tar_disc
FROM tar_downloads_new
WHERE tar_transactiondate = '29/01/2008' AND tar_booked = '29/01/2008'
)
tbl
GROUP BY tbl.tar_branchno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources