Solved

SQL CTE Recursive BOM select max date

Posted on 2014-02-27
5
476 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now