Author Message vigneshkumart50 Valued Member Group: General Forum Members Points: 55 Visits: 184 Hi, I have two columns like below parsename('\$'+ Convert(varchar,Convert(money,Q.[Profit Cost]-R.[Shared Dollars]),1),2) as [Pre Profit], parsename('\$'+ Convert(varchar,Convert(money,R.[New Cost+Shared Dollars]-R.[Shared Dollars]),1),2) as [Post Profit] Formula to be applied: When [Pre Profit] value is zero then its value should be 1. I tried the below case statement and it doesn't work. case when Q.[Profit Cost]-R.[Shared Dollars] = 0 then 1 else ((R.[New Cost+Shared Dollars]-R.[Shared Dollars]) / (Q.[Profit Cost]-R.[Shared Dollars])) end as [Percentage] Really appreciate any help on this. Eirikur Eiriksson SSCertifiable Group: General Forum Members Points: 6693 Visits: 17683 vigneshkumart50 (8/25/2014)Hi, I have two columns like below parsename('\$'+ Convert(varchar,Convert(money,Q.[Profit Cost]-R.[Shared Dollars]),1),2) as [Pre Profit], parsename('\$'+ Convert(varchar,Convert(money,R.[New Cost+Shared Dollars]-R.[Shared Dollars]),1),2) as [Post Profit] Formula to be applied: When [Pre Profit] value is zero then its value should be 1. I tried the below case statement and it doesn't work. case when Q.[Profit Cost]-R.[Shared Dollars] = 0 then 1 else ((R.[New Cost+Shared Dollars]-R.[Shared Dollars]) / (Q.[Profit Cost]-R.[Shared Dollars])) end as [Percentage] Really appreciate any help on this.Here is a quick mock-up with some pseudo data and some quick code, you can use this to better define the question as at first glance, I cannot see anything wrong in your code.`USE tempdb;GODECLARE @SAMPLE_PROFIT TABLE ( SP_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL ,[Profit Cost] DECIMAL(18,5) NOT NULL );DECLARE @SAMPLE_REVENUE TABLE ( SR_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL ,[Shared Dollars] DECIMAL(18,5) NOT NULL ,[New Cost+Shared Dollars] DECIMAL(18,5) NOT NULL );INSERT INTO @SAMPLE_PROFIT([Profit Cost]) VALUES (100.00),(200.00),(300.00),(400.00),(500.00);INSERT INTO @SAMPLE_REVENUE([Shared Dollars],[New Cost+Shared Dollars])VALUES (300.00,100.00),(300.00,300.00),(300.00,400.00),(300.00,500.00),(300.00,500.00);SELECT SP.SP_ID ,SR.SR_ID ,SP.[Profit Cost] ,SR.[Shared Dollars] ,SR.[New Cost+Shared Dollars] ,SP.[Profit Cost] - SR.[Shared Dollars] AS [Pre Profit] ,SR.[New Cost+Shared Dollars] - SR.[Shared Dollars] AS [Post Profit] ,CASE WHEN (SP.[Profit Cost] - SR.[Shared Dollars]) = 0 THEN 1 ELSE (SR.[New Cost+Shared Dollars] - SR.[Shared Dollars]) / (SP.[Profit Cost] - SR.[Shared Dollars]) END AS [Percentage]FROM @SAMPLE_PROFIT SPOUTER APPLY @SAMPLE_REVENUE SR;` MyDoggieJessie SSCarpal Tunnel Group: General Forum Members Points: 4862 Visits: 7363 What exactly isn't working? Are you getting an error? Could be that one of your values in the CASE is NULL... ______________________________________________________________________________"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)