Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need advice on query Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 6:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:04 PM
Points: 2,191, Visits: 5,926
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;

Post #1607077
Posted Monday, August 25, 2014 8:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,952, Visits: 7,170
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse