epicor version 9.05.701
I’m creating a table of data in excel with the below SQL script…what i’m trying to do is only return the data with the latest Partrev.Effectivedate
Im trying to do this with the highlighted part on the screenshot using SELECT MAX but its not playing!
Are there any sql experts that can help me pull this together? Many thanks.
I’m far from a SQL expert, But I think you need to have a SELECT within a SELECT.
Instead of just having the partrev table in the FROM, have partrev returned via another SELECT.
I think you want something like…
SELECT part.company, … pr.revisionnumber, …
FROM part, (SELECT company, revisionnumber, MAX(effectivedate), … FROM partrev) pr
WHERE part.company = pr.company …
EDIT: if you had actually posted the text, instead of a screen shot), I could have given you the whole answer. 
Maybe you can paste the query so we can manipulate it. One thing I’m noticing is that your select MAX() has no criteria/join… So your query is looking for the MAX(effectivedate) of the entire table. something along the lines of = (SELECT MAX(b.effectivedate) from PartRev b where b.PartNum = Partrev.partnum and b.Partrev.revisionnum = partrev.revisionnum)
Build the query using BAQ Designer and copy the Query Phrase.
SELECT part.company, part.partnum, part.partdescription, part.typecode, partrev.revisionnum, partrev.effectivedate, partmtl.mtlseq, partmtl.mtlpartnum, partmtl.qtyper
FROM epicor905.dbo.part part, epicor905.dbo.partrev partrev, epicor905.dbo.partmtl partmtl
WHERE part.company = partrev.company AND part.partnum = partrev.partnum AND partrev.company = partmtl.company AND partrev.partnum = partmtl.partnum AND partrev.revisionnum = partmtl.revisionnum AND partrev.altmethod = partmtl.altmethod AND
part.company = ‘Prior’
ORDER BY part.partnum, partrev.revisionnum, partmtl.mtlseq
give this a shot?
SELECT part.company, part.partnum, part.partdescription, part.typecode, partrev.revisionnum, partrev.effectivedate, partmtl.mtlseq, partmtl.mtlpartnum, partmtl.qtyper
FROM Epicor905.dbo.part part, Epicor905.dbo.partrev partrev, Epicor905.dbo.partmtl partmtl
WHERE part.company = partrev.company AND part.partnum = partrev.partnum AND partrev.company = partmtl.company AND partrev.partnum = partmtl.partnum
AND partrev.revisionnum = partmtl.revisionnum AND partrev.altmethod = partmtl.altmethod AND
part.company = ‘Prior’
AND Partrev.EffectiveDate = (SELECT MAX(b.effectivedate) from Epicor905.dbo.PartRev b where b.PartNum = Partrev.partnum and b.revisionnum = partrev.revisionnum)
Not sure this will help you, but this is the full sql script using a subquery. Not sure you can use a subquery in your design, but I would also think you need a group by clause to invoke the MAX function.
select a1.company, a1.PartNum, a1.PartDescription, a1.TypeCode, a2.RevisionNum, a2.EffectiveDate, a3.MtlSeq, a3.MtlPartNum, a3.QtyPer
from erp.part A1
left outer join
(select company, partnum, revisionnum, max(effectivedate) as effectivedate from erp.partrev group by company, PartNum, RevisionNum) A2
on a1.Company = A2.Company and a1.PartNum = a2.PartNum
left outer join
erp.partmtl A3
on a2.Company = a3.Company and a2.PartNum = a3.PartNum and a2.RevisionNum = a3.RevisionNum
where a1.Company = ‘AA’
and a2.EffectiveDate >= ‘1/1/2013’
Hi Josh, it returned an error unfortunately
look at the quotes. its not copying right from the website
most bizarre…it copies how you’ve wrote it perfectly, but the error shows " instead of just working or showing a ’
but its still returns the same error
Either way you should replace them. delete and re-write the '.
You need to update to your company name.
Looks like it is not processing the quotes properly. Was hoping you would use the code as an example and rewrite into your editor. Looks like you are using Epicor 905 in a BAQ designer and not SQL server directly so syntax will probably need to change.
apologies for the delay in my response…i’ve been away on business and only just got back.
You were absolutely correct about deleting the ’ and retyping it…
Analysing the results, its certainly done something…as its reduced the total number of rows returned, but on the odd occassion i can’t understand why for eg. on part number 7910052000 it has returned revision A and B?..The latest revision date is revision B so i’d have hoped that should be the only one showing for that part number.
I see what you are trying to do now. It’s returning the MAX date of a given part/REV, not part. Try removing the b.revisionnum = partrev.revisionnum portion.
Edit: This may not guarantee a single rev if two revs have an effective date of the same date.
It looks to have worked a charm, i’ll just test the data some more to confirm.
Thank you for your help.
Also,…if 2 revisions do have the same effective date, it’d be fine to return both revisions
It seems to have done the trick. Thank you so much for your help.
Could i just ask one last question from an SQL educational perspective…
What are the underlined 'b’s doing? what are they used for in SQL?
Thank you again.
If you look, the table itself is named b… its just an alias for that table since there is a parent table named PartRev. So with your “(select max” is a whole new sub query. If we didn’t name it b it wouldn’t know which table you are referencing. Technically you could rename it to what ever you wanted.
Dont forget to mark it solved! Thanks.
