Group sort - need sql expression

[ElTech]

I want to sort by the following formula but need an sql expression. any help will be great

if {@OriginalQty} = 0 then
0.00 else (Sum ({Items_Sold.Quantity}, {Items_Sold.ItemNumber})%{@OriginalQty})


The code for @OriginalQty is
{Items_Sold.QtyOnHand}+(Sum ({Items_Sold.Quantity}, {Items_Sold.ItemNumber}))

 

[lbass]

What version of CR are you using?

-LB

 

[ElTech]

I'm using 8.5. I also have version 10 but prefer to use 8.5 because is more compatible with other older software i'm using.

Also, this is for group sorting.

 

[lbass]

Create a SQL expression:

[{%sumsold}:]

(select sum(A.`Quantity`) from Items_Sold A where
A.`ItemNumber` = Items_Sold.`ItemNumber`)

Then create a formula {@soldpercent}:

if {Items_Sold.QtyOnHand} + {%sumsold} = 0 then 0 else
{%sumsold} % ({Items_Sold.QtyOnHand} + {%sumsold})

Place this in the detail section and insert a maximum on it. You should now be able to go to topN/groupsort and choose "maximum of {@soldpercent}" for the sort field. This assumes that {Items_Sold.QtyOnHand} has the same value for all records in the ItemNumber group.

-LB