CASE STATEMENT

[wexas45]

Hello All, can you please help. I am trying to display margin as 0.00 if [GrossProfit] = 0 if not then do the calculation. thanks in advance


SELECT *, CASE WHERE [GrossProfit] <> 0 THEN (CONVERT(DECIMAL(17,2),([GrossProfit] / NULLIF(Price,0) * 100.0))) AS Margin
ELSE CONVERT(DECIMAL(17,2),0.0) END

FROM
(SELECT
[Booking Product],
YEAR([Booking Conf Date]) AS 'BookingYear',
MONTH([Booking Conf Date]) AS 'BookingMonth',
COUNT(DISTINCT [Booking Ref]) AS TotalBookings,
CONVERT (DECIMAL(17,2),(SUM(GBPCost))) AS Cost,
CONVERT (DECIMAL(17,2),(SUM(price))) AS Price,
CONVERT (DECIMAL(17,2),(SUM([Price]-[GBPCost]))) AS 'GrossProfit',
([Booked Adults]+[Booked Children]+[Booked Infants])AS 'PAX'

FROM
[Travelink-Test2].[dbo].[view_EUR_Accounts_Bookings_On_Components]

WHERE
BookingStatus = 'CONFIRMED'
AND [Booking Product]= 'LEIS'

GROUP BY
[GBPCost],[Price],[Booking Product],[Booking Conf Date],
[Booked Adults],[Booked Children],[Booked Infants]) X;

 

[bborissov]

the [AS] clause should be after the END of the CASE statement, and the statement is CASE WHEN not CASE WHERE:

SELECT *,
      CASE [COLOR=red]WHEN[/color] [GrossProfit] <> 0
           THEN (CONVERT(DECIMAL(17,2),([GrossProfit] / NULLIF(Price,0) * 100.0)))
      ELSE  CONVERT(DECIMAL(17,2),0.0) END [COLOR=red]AS Margin[/color]
....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.