topic Re: Query Generator Help please in Application Development and Automation Discussions

Query Generator Help please

chrisvff — Thu, 25 Oct 2018 11:08:23 GMT

I would like to run a query that I can specify a date range of sales orders and specify a min profit percentage.

for example

1.9.18 to 25.10.18 %7 (THIS NUMBER CAN CHANGE)

this will show any sales orders with individual lines or products that were discounted below the 7% gross profit threshold.

Then I will be able to edit the induvial lines on the special prices for business partners in order to get the profit percent above 10%

The special prices for business partners do not have the option to show me the % profit after discount, as far as I can see.

as always, any help would be highly apricated

Re: Query Generator Help please

Former Member — Mon, 29 Oct 2018 12:47:45 GMT

Hi,

Put Below one in Where Conditon Block:

Where Tablename.FieldName=[%0] and Tablename.FieldName=[%1] and Tablename.FieldName=[%2]

here from %0 and %1 field name is date like doc date or tax date.

and for %2 field name is Discount .

Regards,

Tejas P

Re: Query Generator Help please

chrisvff — Thu, 01 Nov 2018 11:36:15 GMT

ended up using this

SELECT CardCode, CardName, ItemCode, Dscription, DocNum, CASE WHEN T1.GrossBuyPr<>0 THEN ROUND(GrssProfit/GrossBuyPr* 100,2) ELSE ROUND(100,2) END AS GrossProfitPer FROM RDR1 T1 LEFT OUTER JOIN ORDR T2 ON T2.DocEntry = T1.DocEntry
WHERE CASE WHEN T1.GrossBuyPr<>0 THEN ROUND(T1.GrssProfit/T1.GrossBuyPr* 100,2) ELSE [%0] END <= [%0] AND T2.DocDate >= [%1] AND T2.DocDate <=[%3]