Epicor BAQ - calculated fields

Question

Hope you are doing well and help me with below query about BAQ.

Please see example below - current results

+-------+------+--------------+------+
| Order | Part | Ship By Date | Wave |
+-------+------+--------------+------+
|  1231 | A    | 11/04/2018   |  333 |
|  1231 | A    | 11/04/2018   |  257 |
|  2522 | C    | 11/04/2018   |  333 |
|  2556 | A    | 11/04/2018   |    0 |
+-------+------+--------------+------+

I need to find the way using calculated fields or other options in BAQ to see only one wave no. for each order line. Something like show me top wave when ship by date and order no. are the same else 0 end

+-------+------+--------------+------+
| Order | Part | Ship By Date | Wave |
+-------+------+--------------+------+
|  1231 | A    | 11/04/2018   |  333 |
|  2522 | C    | 11/04/2018   |  333 |
|  2556 | A    | 11/04/2018   |    0 |
+-------+------+--------------+------+

Hope you will be able to help, many thanks in advance.

Answer 1

I don't have Epicor 10 readily available to me, but I had achieved something like this by creating subqueries.

You will need to utilize (what I believe Epicor calles) inner queries. You inner subquery will be need to be set as a Top option with a sort option of wave descending and returning the four fields listed in your example. You will then join the table to itself with the order, part, and shipby fields as the key values. This should work.

If you have access to an instance of SSMS, you could try something like:

SELECT DISTINCT Order, Part, ShipByDate, Wave
FROM [Table] t
WHERE Wave = (SELECT TOP 1 WAVE FROM [Table] t2 WHERE t.Order = t2.Order and t.Part = t2.Part and t.ShipByDate = t2.ShipByDate ORDER BY Wave DESC)

Answer 2

Hopefully this idea helps. In Epicor 10, you should be able to create a Common Table Expression (CTE). From your example, I would create 2 CTEs. One for each of the result sets. For the second CTE, select MAX(Order) group by Ship By Date. Next, create a new TopLevel to join both CTEs on Order number to obtain your desired results.