troubleshooting Question

SQL CTE Recursive BOM select max date

Avatar of maverick0728
maverick0728Flag for United States of America asked on
Microsoft SQL Server 2008SQL
5 Comments1 Solution795 ViewsLast Modified:
I am running a CTE to get a indented BOM.  When the data is retrieved I am getting all the "Material" revisions and only want the revision with the max(effectivedate).  Attached is .xlsx document with a copy of my current output.  The ones with strikethrough font I don't want to see.  

Here is my CTE:
WITH BillOfMaterials(Company, Bill, Parent, ParentRevNum, EffectiveDate, Material, MaterialRevNum, MaterialDesc, TypeCode, QtyPer, ReqQty, ViewAsAsm, MtlSeq, Lvl, Sort) AS
(

SELECT DISTINCT PartRev.Company,
CAST(PartRev.PartNum + '/' + PartRev.RevisionNum AS VARCHAR(50)) AS Bill,
PartRev.PartNum AS Parent,
PartRev.RevisionNum as ParentRevNum,
PartRev.EffectiveDate,
PartRev.PartNum AS Material,
PartRev.RevisionNum as MaterialRevNum,
CAST(p2.PartDescription AS VARCHAR(50)) AS MaterialDesc,
p2.TypeCode,
CAST(1 AS DECIMAL(18,8)) AS QtyPer,
CAST(1 AS DECIMAL(18,8)) AS ReqQty,
CAST(0 AS TINYINT) AS ViewAsAsm,
0,
0 AS Lvl,
CAST('/000' AS varchar(80)) AS Sort
FROM PartRev
INNER JOIN Part p2 ON p2.Company = PartRev.Company AND p2.PartNum = PartRev.PartNum
INNER JOIN
(select partnum, max(effectivedate) as maxeffdate
from partrev
where partnum = partrev.partnum and
Approved = 1 and
EffectiveDate <= getdate()
group by partnum) a
ON a.partnum = partrev.partnum and a.maxeffdate = partrev.effectivedate
where partrev.partnum = '123456'

UNION ALL

SELECT mtl.Company,
bom.Bill AS Bill,
mtl.PartNum AS Parent,
bom.MaterialRevNum as ParentRevNum,
pr.EffectiveDate,
mtl.MtlPartNum AS Material,
pr.RevisionNum as MaterialRevNum,
CAST(p.PartDescription AS VARCHAR(50)) AS MaterialDesc,
p.typecode,
mtl.QtyPer,
CAST(bom.ReqQty * mtl.QtyPer AS DECIMAL(18,8)) AS ReqQty,
mtl.ViewAsAsm,
mtl.MtlSeq,
Lvl + 1,
CAST(bom.Sort + '/' + RIGHT('000' + CONVERT(VARCHAR,mtl.MtlSeq), 3) AS varchar(80)) AS Sort
FROM PartMtl mtl
INNER JOIN PartRev pr
on pr.Company = mtl.Company and pr.PartNum = mtl.PartNum and pr.RevisionNum = mtl.RevisionNum and
pr.Approved = 1 and
pr.EffectiveDate <= getdate()
INNER JOIN BillOfMaterials bom
ON bom.Company = mtl.Company AND bom.Material = mtl.PartNum
INNER JOIN Part p
ON p.Company = mtl.Company AND p.PartNum = mtl.MtlPartNum
WHERE mtl.AltMethod IS NULL OR mtl.AltMethod = ''

)
SELECT bom.Company,
Bill,
Parent,
ParentRevNum,
bom.EffectiveDate,
Material,
MaterialRevNum,
MaterialDesc,
TypeCode,
QtyPer,
ReqQty,
ViewAsAsm,
MtlSeq,
Lvl,
Sort
FROM BillOfMaterials bom
order by sort, effectivedate desc, parentrevnum desc;
desired.xlsx
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros