Solved

SQL CTE Recursive BOM select max date

Posted on 2014-02-27
5
487 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
  • 2
  • 2
5 Comments
 
LVL 24

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 142

Expert Comment

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

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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 …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 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