August 25, 2014 at 6:46 am
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.
August 25, 2014 at 7:55 am
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;
GO
DECLARE @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 SP
OUTER APPLY @SAMPLE_REVENUE SR;
August 25, 2014 at 8:29 am
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; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply