Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Need advice on query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, August 25, 2014 6:46 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, October 8, 2014 7:18 AM 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.
Post #1607043
 Posted Monday, August 25, 2014 7:55 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:48 PM Points: 6,576, Visits: 17,300
 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;`
Post #1607077
 Posted Monday, August 25, 2014 8:29 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Tuesday, December 6, 2016 1:22 PM Points: 4,804, Visits: 7,361
 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"
Post #1607097

 Permissions