I have a BAQ that returns the list of open jobs, along with some details. One of these details is the potential job value, based on the demand-linked unit price, times the job run quantity. The only problem is that a job may be linked to more than one sales order line, and so the job may have more than one unit price. In reality, we only care about the unit price associated with the very first sales order release (first by date not by rel num).
My goal is to return a job number, then from a subquery pull in the very first release by date, regardless of the order line. From that release’s order line, I need to also pull the unit price. I keep getting both order lines in my final query when I only want one line, the line that contains the release that is earliest. I know this is because I have OrderLine in my groupby clause, but I can’t link to the line to get my unit price unless my line number is included. This feels like a catch 22 situation and I am just not thinking about the right way to setup the queries.
In the attached BAQ I left the line in my group by, so the results should return all your open jobs, along with the first release in each line for that job. Eventually, I want to return a calculated value that is job run qty * unit price. I just need to figure out how to pull in only one unit price for the one line I care about. LateToStartJobs.baq (90.7 KB)
I appreciate any ideas. Thank you for your time!
Nate
EDIT: I just realized we might have two different lines with releases on the same date. I’ll have to think about this more closely.
What you can do is make a calculated field on your top level like this.
Row_Number() over (partition by JobHead.JobNumber orderby OrderRel.Date)
I guessed at the fields, but you should get the picture.
Then in your subquery criteria on the top level (or whatever level you are doing this), filter to only show the rows where that field is 1. This will drop off the duplicates rows.
Let me know if that doesn’t make sense, or you need more help.
Also, I didn’t open your query to see what you were doing… so if I’m wildly off, let me know.
The other option if you only need one field (the unit price?) is a single sub select.
Set up your subquery to return only the unit price, then sort by release date and return just top 1. Then in the subquery criteria, set up links to the other query where you need this field.
Then in that field, in your can use this subquery as a single field.
This way will be more efficient that the windowing function way, but can only be used if you need a single value.
Finally, I would think that you would want the value of the job multiplied by the quantity and price for each demand. I would probably use the numbers from the job prod table to multiply out the values for each demand record, and sum up by job, instead of using the value from just a single order. But that’s just me.
This is the wrench that made me just restart what I was doing. Sometimes management asks for something specific. If you aren’t careful, you can end up down a 6-hour rabbit hole that you have to dig out of before you can start over again.
I am going back to the drawing board and starting with adding up my job value based on linked demand. Not some idealized version of the job value. Thanks for helping me think it through @Banderson!
Thanks for helping me think it through