Finance | Project Operations, Human Resources, ...

Fetch Least sales price record from sales trade agreements

(0) Share

Report

Posted on by D365 beginner

642

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.

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Suggested answer

WillWU 22,363 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
D365 beginner 642 on at

Like (0)

Report
Copy link

Link copied!

Hi Will Wu,

Thanks for the reply.

how can I use the same in X++ ?

Was this reply helpful? Yes No
WillWU 22,363 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No