November 4, 2008 at 8:55 am
Hello All,
Do you find anything wrong with this statement?:
(CASE WHEN t.ItmRecvdYR1 > t.ItmRecvdYR2 THEN
CONVERT(VARCHAR,(((t.ItmRecvdYR1 - t.ItmRecvdYR2)/t.ItmRecvdYR1) * 100)) + '%'
WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN CONVERT(VARCHAR,(((t.ItmRecvdYR1 - t.ItmRecvdYR2)
/t.ItmRecvdYR2) * 100)) + '%' ELSE '0%' END) AS [RecvdDiff%]
I thought it would evaluate (t.ItmRecvdYR1 - t.ItmRecvdYR2) first, then it would divide the result with t.ItmRecvdYR2) and then it would multiply that result with 100. But it is not doing that.
Thanks.
November 4, 2008 at 9:07 am
Two questions. What are the data types of the columns in the expression? What value are you getting currently?
November 4, 2008 at 9:12 am
Try this:
(
CASE WHEN t.ItmRecvdYR1 > t.ItmRecvdYR2
THEN CONVERT(VARCHAR,((((t.ItmRecvdYR1) - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100)) + '%'
WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1
THEN CONVERT(VARCHAR,((((t.ItmRecvdYR1) - t.ItmRecvdYR2)/(t.ItmRecvdYR2 * 1.0)) * 100)) + '%' ELSE '0%' END
) AS [RecvdDiff%]
November 4, 2008 at 11:16 am
Thank you Lynn.
Adding * 1.0 did not do a thing. I am getting the result of "0%" everytime. For example, if I have t.ItmRecvdYR1 = 2653 and t.ItmRecvdYR3 = 2216 then the difference is 437. I should get ((2653 - 2216) / 2653)) * 100 which should be 16.47% but I am getting 0%.
November 4, 2008 at 11:33 am
Just to be sure, post the actual code you used.
November 4, 2008 at 12:05 pm
Lynn,
In fact, when I added "* 1.0", it took over 45 minutes to run the SP while without that it takes over 9 minutes. Here is the code:
SELECT t.Loc + ' ' + upper(l.abbreviation)[Abbr],t.VendorID,
DATEPART(YEAR,t.DateYR1) AS Year1,DATEPART(YEAR,t.DateYR2) AS Year2,t.ItmRecvdYR1,
t.ItmRecvdYR2, t.ItmRecvdYR1 - t.ItmRecvdYR2 AS RecvdDiff,
(CASE WHEN t.ItmRecvdYR1 > t.ItmRecvdYR2 THEN
CONVERT(VARCHAR,(((t.ItmRecvdYR1 - t.ItmRecvdYR2)/t.ItmRecvdYR1) * 100)) + '%'
WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN CONVERT(VARCHAR,(((t.ItmRecvdYR1 - t.ItmRecvdYR2)
/t.ItmRecvdYR2) * 100)) + '%' ELSE '0%' END) AS [RecvdDiff%]
FROM #TMP t
inner join reportsdata..locations l on l.locationno=t.loc
Here is the resultset:
00001 NWHIDNOSTIMAG20072008261534-2730%
00001 NWHIDPARRAGON200720088072489-16820%
00001 NWHIDSELECTPU200720080000%
00001 NWHIDTEXASBOO200720081327227088-138160%
00001 NWHIDTRULLCIN200720080264-264-100%
00001 NWHIDTXBKAUDI20072008265322164370%
00001 NWHIDTXBKSOFT2007200822994344-20450%
00001 NWHIDTXBKSTAP20072008560521166-155610%
00001 NWHIDTXBMARKD20072008971908810%
00001 NWHIDVENTURA200720082102276-20660%
00001 NWHIFPROPERRE200720080000%
Thanks.
November 4, 2008 at 12:09 pm
Try this in its place and let us know if it works:
SELECT
t.Loc + ' ' + upper(l.abbreviation)[Abbr],
t.VendorID,
DATEPART(YEAR,t.DateYR1) AS Year1,
DATEPART(YEAR,t.DateYR2) AS Year2,
t.ItmRecvdYR1,
t.ItmRecvdYR2,
t.ItmRecvdYR1 - t.ItmRecvdYR2 AS RecvdDiff,
(CASE WHEN t.ItmRecvdYR1 > t.ItmRecvdYR2 THEN
CONVERT(VARCHAR,(((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100)) + '%'
WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN CONVERT(VARCHAR,(((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR2 * 1.0)) * 100)) + '%'
ELSE '0%' END) AS [RecvdDiff%]
FROM
#TMP t
inner join reportsdata..locations l
on (l.locationno=t.loc)
November 4, 2008 at 12:34 pm
Lynn,
Thank you very much. That did work. Can you explain me why that worked? It took only 9 minutes.
Thanks again.
November 4, 2008 at 12:42 pm
Original snippet from your code:
(t.ItmRecvdYR1 - t.ItmRecvdYR2)/t.ItmRecvdYR1
This is integer arithmetic. ItmRecvdYR1 - ItmRecvdYR2 is less than ItmRecvdYR1, ie (10 - 5) / 5 = 0.
By multiplying one of the values in this part of your calculation by 1.0 (notice the decimal point and 0) it forced SQL Server to convert the integers to floats and complete real arithmetic, ie (10 -5) / (5 * 1.0) = 0.5.
You also could have cast one of the integer values to a float or decimal type and done the same thing, such as
(t.ItmRecvdYR1 - t.ItmRecvdYR2)/cast(t.ItmRecvdYR1 as decimal(18,1))
Does this help?
November 4, 2008 at 12:58 pm
Lynn,
That certainly helps. Thank you for your time.
November 4, 2008 at 1:34 pm
This is integer arithmetic. ItmRecvdYR1 - ItmRecvdYR2 is less than ItmRecvdYR1, ie (10 - 5) / 5 = 0.
By multiplying one of the values in this part of your calculation by 1.0 (notice the decimal point and 0) it forced SQL Server to convert the integers to floats and complete real arithmetic, ie (10 -5) / (5 * 1.0) = 0.5.
Lynn, I think your calculator is broken :hehe:
November 4, 2008 at 1:48 pm
No, my typing is. The denominator for my calculations should have been 10 not 5. Nice catch!
November 4, 2008 at 2:15 pm
ramadesai108,
How many rows in your final result set? 45 minutes seems like an awfully long time. Also, what is that case calculation supposed to show? As it stands right now, it seems like it will give bad information depending on what R1 and R2 are. Consider, if R1 is 10 and R2 is 4, you will have:
(10-4) / 10.0 = .6
Where as if R1 is 4 and R2 is 10, you will have:
(10-4) / 4.0 = 1.5
Also, your datepart syntax seems off (I would think YEAR would be yyyy) and you are not specifying a size of your converted varchar's. Not sure if any of that affects the performance of your query, but I figured it was worth mentioning given the time this takes.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply