How would I retrieve the PONum on a grouped by Max/Min PODetail.DueDate. The query will find the PO with the Max/Min DueDate but I want to also bring in the PONum but NOT have it grouped by PONum. Can this be done to associate the PONum with that Max/Min DueDate’s PONum?
What I usually do for that is a windowing function. In a calculated field:
Row_Number() over (partition by PODetail.Company, PODetail.PartNum order by PODetail.PoDate desc)
(note: none of that is sytax checked, so I don’t know if the fields are right, but you should get the point)
Uncheck the group by on all your fields, and add whatever info you need.
This will give you a row number, and the number 1’s are the max date. You make that a sub query, and when you bring it into the next level, you filter to show only the number 1s.
One caveat, is this ends up being kind of slow, so depending on your query and database, may lead to timeout issues.
can you give a sample of the data set you are querying and then a sample of the results you would like to see? I THINK you can do what you want, but I dont totally understand the desire.
If there is only one PO per part per date you just use the results of the query as a subquery.
you may luck out and be close already.
That’s similar to Brandon’s suggestion but impacts performance.
Basically the Parent table is JobHead and I want to link it based on the Part Number to the PODtl table where it finds all PO’s with that Part Number and then pull in either the max/min PODtl.DueDate and also bring in that associated PO Number but is not grouped by PODtl.PONum. Looking to find the earliest open PODtl.DueDate for that Part and PONum. Can this be done using a max/min group by?
Find max value and show corresponding value from different field in SQL server - Stack Overflow
If there is another way please share.
I was trying to do the SubQuery option but the field I am selecting exists in the t2 table that I need on the max function.
It will look something like this.
SELECT
*
FROM
Erp.PODetail AS t1
INNER JOIN (SELECT
Company,
PartNum,
max(DueDate) AS MaxDate
FROM
Erp.PODetail
GROUP BY
Company,
PartNum) AS t2
ON t1.Company = t2.company AND
t1.PartNum = t2.PartNum AND
t1.DueDate = t2.MaxDateSolution: you can create a sub-query that selects all the OPEN POs, showing PO, Line, Part, Date, Qty… then create a calculated field that does a RANK on the data… the calculation would be something like the followign (using real field names)
rank () over (Partition by PartNum order by DueDate)
What this will do is create a new ranking for each part number… the earliest PO for each part will be number 1.
THEN… in your sub query criteria, specify that you only want records that are Ranked as #1. This will give you the earliest PO, line, date, and Qty.
THEN create a top query that joins your JOB/Mtl to the Sub Query using the part number.
for more info on the “Rank” function see: SQL RANK() Function Explained By Practical Examples
1 Like
Your t1 table is from the same PODtl table as t2. The scenario is where t1 and t2 are different tables.
This should work but will affect performance if there are a lot of POs.
What are the tables you are joining?
Not sure you will get around a subquery here. I would be surprised if there were performance issues.
if you make the first sub-query a CTE query, then it only runs one time, and then join the TOP query to the CTE query… there should be no significant speed issues.
When I need something like this I will create a calculated field in a subquery something like:
DateField = table.date + separator + ponum + whatever else goes along with the date to make:
20210722~12345
And then do something like
max(table.date + separator + table.ponum) (convert date to string, ponum to string)
Then in the main query define a couple of calculated fields:
MaxDate = [Ice].entry(1,isnull(Subquery2.DateField,’’) , ‘~’ ) (convert to datetime)
PoNum = [Ice].entry(2,isnull(Subquery2.DateField,’’) , ‘~’ ) (convert to int)
You have to convert the date into string format starting with year (I don’t have an example handy) so the max will get the latest date. And then in the main query you’ll probably want to convert that back into a datetime field.
See if this helps.
Joe