Solved

SQL CTE Recursive BOM select max date

Posted on 2014-02-27
5
509 Views
Last Modified: 2014-02-28
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
0
Comment
Question by:maverick0728
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39893890
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
 

Author Comment

by:maverick0728
ID: 39893915
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39894180
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39894620
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
 

Author Closing Comment

by:maverick0728
ID: 39894666
works.  thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question