SQL CTE Recursive BOM select max date

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;

Open in new window

desired.xlsx
maverick0728Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You need to modify the recursive part of your CTE to include the similar condition as your anchor part of the 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
(select partnum, max(effectivedate) as maxeffdate
from partrev
where partnum = pr.partnum and
Approved = 1 and
EffectiveDate <= getdate()
group by partnum) a1
ON a1.partnum = pr.partnum and a1.maxeffdate = pr.effectivedate
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;

Open in new window

0
maverick0728Author Commented:
I am getting the error:
Msg 4104, Level 16, State 1, Line 56
The multi-part identifier "pr.partnum" could not be bound.
Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'BillOfMaterials'.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
chaauCommented:
OK, change the query like this, it should work:
WITH LatestDates AS(
select partnum, max(effectivedate) as maxeffdate
from partrev
where Approved = 1 and
EffectiveDate <= getdate()
group by partnum),
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 LatestDates 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 LatestDates a1
ON a1.partnum = pr.partnum and a1.maxeffdate = pr.effectivedate
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;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maverick0728Author Commented:
works.  thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.