Finance | Project Operations, Human Resources, ...

Fetch Least sales price record from sales trade agreements

(0) Share

Report

Posted on by D365 beginner

580

Hi Experts,

I have a requirements that I have some customer accounts with multiple sales prices for an item in the same period. I need to take the least sales price record from the table PriceDiscAdmTrans. This is to be added in a report.

1. Customer A with sales price 100

2. Customer A with sales price 200

3. Customer B with sales price 150

Output should be Customer A with 100 & Customer C with 150. how can I get this output using select statement?.

Thanks in advance.

All responses (3)

Answers (0)

WillWU 22,352 on at

Like (0)

Report

RE: Fetch Least sales price record from sales trade agreements

Hi AX beginner,

The ROW_NUMBER doesn't exist in X++.

You could create a view with a computed column as the data source of the SSRS, please have a look at Martin's blog:

dev.goshoom.net/.../
D365 beginner 580 on at

Like (0)

Report

RE: Fetch Least sales price record from sales trade agreements

Hi Will Wu,

Thanks for the reply.

how can I use the same in X++ ?
Suggested answer

WillWU 22,352 on at

Like (0)

Report
RE: Fetch Least sales price record from sales trade agreements

Hi AX Beginnner,

See the T-SQL:

SELECT accountcode, AMOUNT FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY accountcode ORDER BY amount asc) AS num FROM PriceDiscAdmTrans) t WHERE num=1