Hi Gail,

Here is what I need to do:

ItmRecvdYR2 = ISNULL((SELECT SUM(Quantity), VendorID, LocationNo

FROM dbo.GetAllReorderScannedItemsByDate()

INNER JOIN (SELECT VendorID, LOC, DateYR2 FROM #TMP) TMPData

ON VendorID=TMPData.VendorID...

Hi Gail,

I don't understand what you mean by moving the subquery into the from clause.

Thanks.

Hi ta.bu.shi.ya.du,

In the following:

WHEN t.ItmRecvdYR2 > t.ItmRecvdYR1 THEN

CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR2 * 1.0)) * 100),0) AS VARCHAR) + '%'

if I change the rounding to...

Hello All,

Now the following statement works fine but it produces number of digits to the right of the decimal:

CAST(ROUND((((t.ItmRecvdYR1 - t.ItmRecvdYR2)/(t.ItmRecvdYR1 * 1.0)) * 100),0) AS...

Manu,

Transactions is a huge table and fld1 is indexed.

Thanks.

Hi All,

Sorry it took me longer to reply. Rounding function using "0" or "2" makes the difference. That caused the number to zero out.

Thanks.

All,

There are nothing less than zeros in SoldYR1 or SoldYR2. Datatype is Money for both.

Thank you.

Hi Lynn,

Yes there are rows where both t.SoldYR1 and t.SoldYR2 are zero. But since they are equal they fall through the condition and give me '0%'....

All,

The commented code is the one that is having divide by zero error:

-- ...

Lynn,

That certainly helps. Thank you for your time.

Lynn,

Thank you very much. That did work. Can you explain me why that worked? It took only 9 minutes.

Thanks again.

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...

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 =...

Thank you Happy. Your response has helped me to decide on 2008.

Sanjay,

Thanks for the reply. What is RTM?