I’m attempting to get a list of materials for an approved revision of a manufactured part. The issue is that there are multiple ECO groups that have altered the material requirements so my query is returning records from previous groups. I would like to know how to restrict it to only return data from the ECO group with the most recent approved date. I’ve set up a calculated field to get the latest approved date but it returns it for each unique row. I’m pretty sure I’m missing something simple but any help is appreciated. Thanks
You would want to have a SubQuery with a calculated field to return the Max(ApprovedDate) and in the main query, return only those rows where ApprovedDate = MaxApprovedDate from the SubQuery.
Thanks for the reply. That last field in the image is a calculated field (Max(ApprovedDate)) that returns the last approved date. Wouldn’t a subquery return these same values? The problem with the highlighted rows is that the date shown is the actual last date that particular configuration (for a different ECO group) was approved.
post your query
are you looking for the last date approved for the parent part or the child part?
The last date approved for the current approved revision of the parent part. This is part of a much bigger project I’m working on but this part is giving me a hassle.
select
[Part].[PartNum] as [Part_PartNum],
[ECORev].[RevisionNum] as [ECORev_RevisionNum],
[ECOMtl].[MtlSeq] as [ECOMtl_MtlSeq],
[ECOMtl].[MtlPartNum] as [ECOMtl_MtlPartNum],
[ECOMtl].[QtyPer] as [ECOMtl_QtyPer],
(max( ECORev.ApprovedDate )) as [Calculated_lda]
from Erp.Part as Part
inner join Erp.ECORev as ECORev on
ECORev.Company = Part.Company
and ECORev.PartNum = Part.PartNum
and ( ECORev.Approved = true )
inner join Erp.ECOMtl as ECOMtl on
ECORev.Company = ECOMtl.Company
and ECORev.GroupID = ECOMtl.GroupID
and ECORev.PartNum = ECOMtl.PartNum
and ECORev.RevisionNum = ECOMtl.RevisionNum
and ECORev.AltMethod = ECOMtl.AltMethod
where (Part.PartNum = ‘G1501720.7Z31454’)
group by [Part].[PartNum],
[ECORev].[RevisionNum],
[ECOMtl].[MtlSeq],
[ECOMtl].[MtlPartNum],
[ECOMtl].[QtyPer]
Start with just the ECORev and ECOMtl tables.
select
[ECORev].[PartNum] as [ECORev_PartNum],
[ECORev].[RevisionNum] as [ECORev_RevisionNum],
[ECOMtl].[MtlSeq] as [ECOMtl_MtlSeq],
[ECOMtl].[MtlPartNum] as [ECOMtl_MtlPartNum],
[ECOMtl].[QtyPer] as [ECOMtl_QtyPer],
ECORev.ApprovedDate as [ECORev_ApprovedDate]
From Erp.ECORev as ECORev
inner join Erp.ECOMtl as ECOMtl on
ECORev.Company = ECOMtl.Company
and ECORev.GroupID = ECOMtl.GroupID
and ECORev.PartNum = ECOMtl.PartNum
and ECORev.RevisionNum = ECOMtl.RevisionNum
and ECORev.AltMethod = ECOMtl.AltMethod
where ( ECORev.Approved = 1 )
If you need to add the part table back you can do that later.
Same results. I even tried to set this up as a subquery with a calculated last date field and then in the main query set up another calculated field with Max(SubCalculatedLastDate). Same results. It should not be this difficult to say “Only show the most recent dates from a set of data”.
Is this what you are looking for?
A list of all ECORevs that are approved; Display - Part, Revision, GroupID, Approved Date
Then from the above list only display the Part, Revision, GroupID with the most recent Approved Date.
Is that correct?
1 Like
Would it work to use PartRev table instead of the ECO table. That only has one date per rev.
good point as that will then be the latest rev
1 Like
Thanks. Part Rev did the trick but I had to make a subquery with just the PartRev table and a calculate field for Max(ApprovedDate). Then I used that in the original query as a table relation to EcoRev.ApprovedDate. Probably a more elegant way to do it but I’m honestly tire of messing with it and it gives the correct results. Thanks Again for the help.
Can I ask why you would use the ECO tables at all? Why not just PartRev and PartMtl? Are your trying to get history? Or just current information?
1 Like
Don’t worry, we’ve all been there!