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


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;


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?

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

1 Like

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.

try this:
add a Rownumber calculated field

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

1 Like

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

I was just about to type that =P

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.



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:

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

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




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.

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)