BAQ return one Date for the last PUR-STK transaction on a Part

Epicor ERP 10
,
1

Hi everyone,
I am sure I have done this before but maybe the fact that it is Friday and it has been a long week is taking it’s toll.
I have a BAQ and I am trying to retrieve the last PUR-STK transaction from the PartTrans table, in other words each part has one last transaction date, but whatever I try I seem to be getting all the PUR-STK dates.
I am using a calculated field in a subquery as below;

image

image
image857×63 2.07 KB

This is my calculated field expression;

max(case when PartTran.TranType = 'PUR-STK' then PartTran.TranDate else null end)

If anyone can jog my memory, or maybe I’m just imagining that I have done something similar before?

2

try this
do not put your condition there, but it on the table PartTran.
and max the date .

1 Like
3

Hi Pierre,
Thanks for getting back to me so quickly. I have done as you suggest, but I’m still getting them all. I will continue to play around with it.

4

try this:
add a Rownumber calculated field
ROW_NUMBER() OVER(PARTITION BY TranType ORDER BY Date DESC)

then in the main query add the condition the calculated field = 1 (should get the first record only…)

1 Like
5

The display query should be something like this for the Last Rec date subquery.

select Company, TranType, PartNum, MAX(TranDate)
from Erp.PartTran
where TranType = 'PUR-STK'
group by Company, TranType, PartNum
3 Likes
6

I was just about to type that =P

7

Hi Pierre,
I tried your solution, I think I am almost there but I am getting results where the row number only equals one once for every transaction type. below is the output without the ‘PUR-STK’ condition. When I add that I only get the one row back.

ROW_NUMBER()OVER(PARTITION BY PartTran.TranType ORDER BY PartTran.TranDate DESC)

image

8

I figured out that I need the lowest row number where the Transaction Type is PUR-STK., but I can’t work out how to set that condition. Everything I have tried so far just errors out :frowning:

9

The example @knash showed is what your subquery should be. If you want the same for other tran types, they’ll need to be in their own subqueries.

And because the devil.is in the datail, you’ll need to ask yourself what you mean by “last”. Since receipts can be back dated.

The most recent transaction? Then go with MAX(TranNum)

The one with the most recent receipt date? The go with MAX(TranDate)

1 Like
10

Yes, like knash’s example - grouping and max trandate is what I have used in the past.
But… since playing around a little this morning I’m thinking I’ll investigate row_numbers a bit more. Reason, SysDate and TranDate don’t always match, possible issues when they don’t.
Ref screenshots

image
image801×283 105 KB

image

image

2 Likes
11

I got it working using Ken’s example, thanks to Calvin for highlighting the area where I could get different results from what I am expecting. You are right Bruce, depending on which way it’s done the results can be different. I would be interested to know if you get it working using the row_number method.

12

It should work for most people
BUT…because I’m on a test system right now with MS SQL 2008 version, the “…ORDER BY Date DESC” part fails for me.

If you feel adventurous, your could try the attached BAQ.
Warning it resembles something Rube Goldberg might do.
Seems to return the results I was looking for though… by using SysRevID ( after converting them to integers ). As you can guess, it’s a REALLY slow day here today.

CUS-PartLastTran.baq (48.5 KB)

image