Recursive CTE BAQ - Help Needed

[dgreenEA (Dylan Green)]

We are building a BAQ that needs to have two union/recursion sections. The first section builds a date table based on the results of 7 different queries and unions them together. The second needs to be a running total similar to what is mentioned in the below post:

BAQ Running Total

Any way to create a running total and show it next to each record on a BAQ? I have a BAQ that pulls in all jobs for a resource and sorts it by a custom “start time sequence” field. I want to do a running total of the estimated production hours next to each job to show how many hours away the estimated completion is.

Currently, the first recursion works perfectly. However, I am having tons of trouble with the second. I keep getting different errors when I try to get it to run. Current errors and BAQ below:

AGDB_BI_SpplyDmnd02Rec.baq (200.1 KB)

I have tried working with a union all and I have tried using the parenthesis options in the subquery list to encapsulate the two but I get different errors there.

Any help is greatly appreciated.

[dgreenEA (Dylan Green)]

I have found this article from @Banderson that seems to do the same thing I’m trying to do for the first union statement:

CTE with multiple unions not merging all columns

right, what I suggested will do that. What’s not working when you try it? In your example if it’s only returning jan and may (the ones with 2 values) it’s because you have an inner join. Make it an outer join. [image] edit and I see what you mean about the dates., Use a case statement in a calculated field. This is a really bad example, but basically this is the structure. If your first date is null, use the second date, if the second date is null use the third date, else use the first date…

Hopefully following this will allow me to build the union statement I want afterward.

[dgreenEA (Dylan Green)]

Alright, I got the errors to go away, but the BAQ won’t iterate. It just gives me the initial value listed. Could someone give me a crash course on how to iterate through something? I’ve gotten this to work in the past with an indented BOM. Now what I’m looking to do is show my demand against production and iterate the difference between those against the current on-hand qty of each part. This should show me whether or not my build plan is on track for the year or not.

[utaylor (Utah Taylor)]

Dylan, I have wanted to try this again after I got the BOM listing going.

I haven’t used recursion as much in SQL aside from that. I need to practice more with it.

Sorry I can’t help at this time.

Looking forward to any help that is posted here so that I may learn too.

I have used a lot of microsoft links and other forums (stack) as well that have helped me get further on this.

[jkane (John Kane)]

I recommend making your recursive result a CTE and then trying to do the running total using the CTE.

[dgreenEA (Dylan Green)]

Hate to pry, but any chance you have some visuals/instructions on how to get this working?

[jkane (John Kane)]

Correct me if I am wrong, but I believe you said you had the recursion working and the aggregation was not?

If that is correct, take the aggregation out of the recursive query. Make your Top Level a CTE and then you can use those results in a new query and just aggregate against the results.

[dgreenEA (Dylan Green)]

You are right. So in this case I have the following listing as the results:

Company: Year/Month: Part: Change: = Production - Demand

If I change this top-level to a CTE that means that this will be my anchor point, right? The way I was trying to do it before was to have the current on-hand qty first and try to iterate by the change shown for each month.

Currently, I have the CTE set the way you said and I have linked the current on-hand qty as the union. Unfortunately, this doesn’t quite seem to work. I have been taking notes from the following post. The new version of BAQ listed:

BAQ Running Total

Yes, something like this should work in a calculated field… You can use the "SUM " function from SQL with the “OVER” clause. Here is an example from PartTran, but it works the same in yours… just change the “partition by” and “OrderBy” fields to your needs. SUM(PartTran.TranQty) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum) [image] [image] For more info, read: Data with Bert

AGDB_BI_SpplyDmnd02Rec2.baq (235.7 KB)

[jkane (John Kane)]

Unfortunately I am not on Kinetic yet, so I can’t use your BAQ. Can you post the “SQL” from the general tab?

[dgreenEA (Dylan Green)]

Sure thing! Don’t make too much fun of my poor BAQ building

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
with [UnionGrab] as 
(select 
	(Case
     when SOByMonth1.OrderRel_Company is not null then SOByMonth1.OrderRel_Company
     when ForeQty.Forecast_Company is not null then ForeQty.Forecast_Company
     when MPSQty.MasProd_Company is not null then MPSQty.MasProd_Company
     when FirmJobQty.JobHead_Company is not null then FirmJobQty.JobHead_Company
     when UnfirmJobQty.JobHead1_Company is not null then UnfirmJobQty.JobHead1_Company
     when OHQty2.PartBin_Company is not null then OHQty2.PartBin_Company
 end) as [Calculated_Company],
	(Case
     when SOByMonth1.Calculated_MonthYear is not null then SOByMonth1.Calculated_MonthYear 
     when ForeQty.Calculated_MonthYear is not null then ForeQty.Calculated_MonthYear
     when MPSQty.Calculated_YearMonth is not null then MPSQty.Calculated_YearMonth
     when FirmJobQty.Calculated_YearMonth is not null then FirmJobQty.Calculated_YearMonth
     when UnfirmJobQty.Calculated_YearMonth is not null then UnfirmJobQty.Calculated_YearMonth
     when OHQty2.Calculated_CurrentYearMonth is not null then OHQty2.Calculated_CurrentYearMonth
 end) as [Calculated_YearMonth03],
	(case
     when SOByMonth1.OrderRel_PartNum is not null then SOByMonth1.OrderRel_PartNum
     when ForeQty.Forecast_PartNum is not null then ForeQty.Forecast_PartNum
     when MPSQty.MasProd_PartNum is not null then MPSQty.MasProd_PartNum
     when FirmJobQty.JobHead_PartNum is not null then FirmJobQty.JobHead_PartNum
     when UnfirmJobQty.JobHead1_PartNum is not null then UnfirmJobQty.JobHead1_PartNum
     when OHQty2.PartBin_PartNum is not null then OHQty2.PartBin_PartNum
 end) as [Calculated_Part],
	(case  
     when SOByMonth1.Calculated_OpenQty is null then 0.00 else SOByMonth1.Calculated_OpenQty
 end) as [Calculated_OpenSOQty],
	(case
     when ForeQty.Calculated_QtyOpen is null then 0.00 else ForeQty.Calculated_QtyOpen
 end) as [Calculated_OpenForeQTY],
	(case
     when MPSQty.Calculated_MPSQty is null then 0.00 else MPSQty.Calculated_MPSQty
 end) as [Calculated_OpenMPSQty],
	(case
     when FirmJobQty.Calculated_JobQty is null then 0.00 else FirmJobQty.Calculated_JobQty
 end) as [Calculated_OpenFirmJobQty],
	(case
     when UnfirmJobQty.Calculated_UnfirmJobQty is null then 0.00 else UnfirmJobQty.Calculated_UnfirmJobQty
 end) as [Calculated_OpenUnfirmJobQty],
	(case 
     when OHQty2.Calculated_OHQty is null then 0.00 else OHQty2.Calculated_OHQty
 end) as [Calculated_OpenOHQty],
	(OpenFirmJobQty+ OpenUnfirmJobQty) as [Calculated_Production],
	(OpenSOQty+ OpenForeQTY) as [Calculated_Demand],
	(Production- Demand) as [Calculated_Change],
	(OpenOHQty) as [Calculated_Total]
from  (select 
	[OrderRel].[Company] as [OrderRel_Company],
	[OrderRel].[PartNum] as [OrderRel_PartNum],
	(sum((OrderRel.OurJobQty- OrderRel.OurJobShippedQty)+ (OrderRel.OurStockQty- OrderRel.OurStockShippedQty))) as [Calculated_OpenQty],
	(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101)) as [Calculated_MonthYear],
	('SOByMonth') as [Calculated_Catagory]
from Erp.OrderRel as OrderRel
inner join Erp.OrderDtl as OrderDtl on 
	OrderRel.Company = OrderDtl.Company
	and OrderRel.OrderNum = OrderDtl.OrderNum
	and OrderRel.OrderLine = OrderDtl.OrderLine
	and ( OrderDtl.OpenLine = 1  )

inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
	and ( OrderHed.OpenOrder = 1  )

where (OrderRel.OpenRelease = 1)
group by [OrderRel].[Company],
	[OrderRel].[PartNum],
	(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101)))  as SOByMonth1
full outer join  (select 
	[Forecast].[Company] as [Forecast_Company],
	[Forecast].[PartNum] as [Forecast_PartNum],
	(sum(Forecast.ForeQty- Forecast.ConsumedQty)) as [Calculated_QtyOpen],
	(convert(varchar, year(Forecast.ForeDate), 101)+ '-'+ convert(varchar, month(Forecast.ForeDate), 101)) as [Calculated_MonthYear],
	('ForeQty') as [Calculated_Catagory]
from Erp.Forecast as Forecast
where (Forecast.Inactive = 0  and Forecast.ForeQty > Forecast.ConsumedQty)
group by [Forecast].[Company],
	[Forecast].[PartNum],
	(convert(varchar, year(Forecast.ForeDate), 101)+ '-'+ convert(varchar, month(Forecast.ForeDate), 101)))  as ForeQty on 
	ForeQty.Forecast_Company = SOByMonth1.OrderRel_Company
	and ForeQty.Forecast_PartNum = SOByMonth1.OrderRel_PartNum
	and ForeQty.Calculated_MonthYear = SOByMonth1.Calculated_MonthYear
full outer join  (select 
	[MasProd].[Company] as [MasProd_Company],
	[MasProd].[PartNum] as [MasProd_PartNum],
	(sum(MasProd.ProdQty)) as [Calculated_MPSQty],
	(convert(varchar, year(MasProd.DueDate), 101)+ '-'+ convert(varchar, month(MasProd.DueDate), 101)) as [Calculated_YearMonth],
	('MPSQty') as [Calculated_Catagory]
from Erp.MasProd as MasProd
group by [MasProd].[Company],
	[MasProd].[PartNum],
	(convert(varchar, year(MasProd.DueDate), 101)+ '-'+ convert(varchar, month(MasProd.DueDate), 101)))  as MPSQty on 
	MPSQty.MasProd_Company = ForeQty.Forecast_Company
	and MPSQty.MasProd_PartNum = ForeQty.Forecast_PartNum
	and MPSQty.Calculated_YearMonth = ForeQty.Calculated_MonthYear
full outer join  (select 
	[JobHead].[Company] as [JobHead_Company],
	[JobHead].[PartNum] as [JobHead_PartNum],
	(sum(JobHead.ProdQty)) as [Calculated_JobQty],
	(convert(varchar, year(JobHead.DueDate), 101)+ '-'+ convert(varchar, month(JobHead.DueDate), 101)) as [Calculated_YearMonth],
	('FirmJobQty') as [Calculated_Catagory]
from Erp.JobHead as JobHead
where (JobHead.JobClosed = 0  and JobHead.JobComplete = 0  and JobHead.JobFirm = 1)
group by [JobHead].[Company],
	[JobHead].[PartNum],
	(convert(varchar, year(JobHead.DueDate), 101)+ '-'+ convert(varchar, month(JobHead.DueDate), 101)))  as FirmJobQty on 
	SOByMonth1.OrderRel_Company = FirmJobQty.JobHead_Company
	and SOByMonth1.OrderRel_PartNum = FirmJobQty.JobHead_PartNum
	and SOByMonth1.Calculated_MonthYear = FirmJobQty.Calculated_YearMonth
full outer join  (select 
	[JobHead1].[Company] as [JobHead1_Company],
	[JobHead1].[PartNum] as [JobHead1_PartNum],
	(sum(JobHead1.ProdQty)) as [Calculated_UnfirmJobQty],
	(convert(varchar, year(JobHead1.DueDate), 101)+ '-'+ convert(varchar, month(JobHead1.DueDate), 101)) as [Calculated_YearMonth],
	('UnfirmJobQty') as [Calculated_Catagory]
from Erp.JobHead as JobHead1
where (JobHead1.JobClosed = 0  and JobHead1.JobComplete = 0  and JobHead1.JobFirm = 0)
group by [JobHead1].[Company],
	[JobHead1].[PartNum],
	(convert(varchar, year(JobHead1.DueDate), 101)+ '-'+ convert(varchar, month(JobHead1.DueDate), 101)))  as UnfirmJobQty on 
	FirmJobQty.JobHead_Company = UnfirmJobQty.JobHead1_Company
	and FirmJobQty.JobHead_PartNum = UnfirmJobQty.JobHead1_PartNum
	and FirmJobQty.Calculated_YearMonth = UnfirmJobQty.Calculated_YearMonth
full outer join  (select 
	[PartBin].[Company] as [PartBin_Company],
	[PartBin].[PartNum] as [PartBin_PartNum],
	(sum(PartBin.OnhandQty)) as [Calculated_OHQty],
	(FORMAT (Constants.Today, 'yyyy-MM')) as [Calculated_CurrentYearMonth],
	('OHQty') as [Calculated_Catagory]
from Erp.PartBin as PartBin
inner join Erp.WhseBin as WhseBin on 
	PartBin.Company = WhseBin.Company
	and PartBin.WarehouseCode = WhseBin.WarehouseCode
	and PartBin.BinNum = WhseBin.BinNum
	and ( WhseBin.NonNettable = 0  )

where (PartBin.OnhandQty > 0)
group by [PartBin].[Company],
	[PartBin].[PartNum])  as OHQty2 on 
	UnfirmJobQty.JobHead1_Company = OHQty2.PartBin_Company
	and UnfirmJobQty.JobHead1_PartNum = OHQty2.PartBin_PartNum
	and UnfirmJobQty.Calculated_YearMonth = OHQty2.Calculated_CurrentYearMonth
union all
select 
	[UnionGrab1].[Calculated_Company] as [Calculated_Company],
	[UnionGrab1].[Calculated_YearMonth03] as [Calculated_YearMonth03],
	[UnionGrab1].[Calculated_Part] as [Calculated_Part],
	[UnionGrab1].[Calculated_OpenSOQty] as [Calculated_OpenSOQty],
	[UnionGrab1].[Calculated_OpenForeQTY] as [Calculated_OpenForeQTY],
	[UnionGrab1].[Calculated_OpenMPSQty] as [Calculated_OpenMPSQty],
	[UnionGrab1].[Calculated_OpenFirmJobQty] as [Calculated_OpenFirmJobQty],
	[UnionGrab1].[Calculated_OpenUnfirmJobQty] as [Calculated_OpenUnfirmJobQty],
	[UnionGrab1].[Calculated_OpenOHQty] as [Calculated_OpenOHQty],
	[UnionGrab1].[Calculated_Production] as [Calculated_Production],
	[UnionGrab1].[Calculated_Demand] as [Calculated_Demand],
	[UnionGrab1].[Calculated_Change] as [Calculated_Change],
	(sum(UnionGrab1.Calculated_Change) OVER (PARTITION BY UnionGrab1.Calculated_Part ORDER BY UnionGrab1.Calculated_YearMonth03)) as [Calculated_Total]
from  UnionGrab  as UnionGrab1)

select 
	[UnionGrab3].[Calculated_Company] as [Calculated_Company],
	[UnionGrab3].[Calculated_YearMonth03] as [Calculated_YearMonth03],
	[UnionGrab3].[Calculated_Part] as [Calculated_Part],
	[UnionGrab3].[Calculated_OpenSOQty] as [Calculated_OpenSOQty],
	[UnionGrab3].[Calculated_OpenForeQTY] as [Calculated_OpenForeQTY],
	[UnionGrab3].[Calculated_OpenMPSQty] as [Calculated_OpenMPSQty],
	[UnionGrab3].[Calculated_OpenFirmJobQty] as [Calculated_OpenFirmJobQty],
	[UnionGrab3].[Calculated_OpenUnfirmJobQty] as [Calculated_OpenUnfirmJobQty],
	[UnionGrab3].[Calculated_OpenOHQty] as [Calculated_OpenOHQty],
	[UnionGrab3].[Calculated_Production] as [Calculated_Production],
	[UnionGrab3].[Calculated_Demand] as [Calculated_Demand],
	[UnionGrab3].[Calculated_Change] as [Calculated_Change],
	[UnionGrab3].[Calculated_Total] as [Calculated_Total]
from  UnionGrab  as UnionGrab3

[jkane (John Kane)]

First off, your first few Case statements do not have an Else clause. That might be causing an issue. You could also think of doing COALESCE instead if all you are doing is checking for Null values.

COALESCE (Transact-SQL) - SQL Server | Microsoft Docs

[jkane (John Kane)]

If I were to do this (and I am definitely not an expert!!) I would do it as 6 CTE queries and then use those CTEs as the tables for a seventh query to bring all the data together.

Start with SubQuery1, rename it SOByMonth, change the type to CTE and the generated SQL should look like

select 
	[OrderRel].[Company] as [OrderRel_Company],
	[OrderRel].[PartNum] as [OrderRel_PartNum],
	(sum((OrderRel.OurJobQty- OrderRel.OurJobShippedQty)+ (OrderRel.OurStockQty- OrderRel.OurStockShippedQty))) as [Calculated_OpenQty],
	(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101)) as [Calculated_MonthYear],
from Erp.OrderRel as OrderRel
where (OrderRel.OpenRelease = 1)
group by [OrderRel].[Company],
	[OrderRel].[PartNum],
	(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101)))

You can take out the OrderHed and OrderDtl as if the line is closed all releases are automatically closed and if the order is closed all lines and releases are automatically closed.

Add a new SubQuery as a CTE and rename to ForeQty. Create that query and keep on doing this until all 6 are in their own CTE.

Finally, add a new SubQuery as Top Level and use the previous CTEs as the tables. Join them all together and do your calculations.

[dgreenEA (Dylan Green)]

Isn’t that pretty much what I did only instead of storing them as CTE’s I stored them as InnerSubQueries?

[jkane (John Kane)]

Yes, but at the end of what I am suggesting, there is no Union.

[dgreenEA (Dylan Green)]

True, but then how do we get a rolling total for each part by month?

Rolling total = Current On hand number + (production for the month - demand for the month)

Currently, I only see that for each month it will look at what I have on hand, right now, for each month instead of looking at what the rolling total was at the end of each month.

[Banderson (Brandon Anderson)]

I took a quick look at your query then noped out because it’s way more complicated that I have time for. But if you are looking for a rolling total you can use windowing functions.

Sum(table.Field) over (partition by Separator1Table.SeparatorField1 , Separator2Table.SeparatorField2 etc. order by Table.FieldtoSortBy)

This will give you a running sum without have to try and use a CTE.

Not sure if that’s what you are looking for, but it seems simpler than whatever you are trying to do.

[jkane (John Kane)]

Sorry, missed the rolling part.

@Banderson thanks, I was just looking up the syntax, now I don’t have to.

I would use the Rows window.

Sum(table.Field) over (partition by Separator1Table.SeparatorField1  order by Table.FieldtoSortBy ROWS BETWEEN CURRENT ROW AND 1 PRECEDING)

[dgreenEA (Dylan Green)]

@Banderson and @jkane,

Thank you both!! Just now getting into the data. I now need to figure out how to get my initial on hand qty to only show up on the current month and I believe that will get it to where this thing is finally counting right. Thank you both for the assist!!

[kylepbsps (Kyle P)]

RunningValue in SSRS if that helps =) My scenario is different but maybe these notes will help spark some ideas.

=RunningValue(Fields!Calc_Quantity.Value, SUM , “Dataset”) + Fields!OnhandQty.Value

[Calc_Quantity]
=IIF(Fields!RequirementFlag.Value = 0, Fields!Quantity.Value, (-1 * Fields!Quantity.Value))

[dgreenEA (Dylan Green)]

@Banderson and @jkane,

When you guys say (partition by Separator1Table.SeparatorField1, 2) what do you think I should use? I’m trying to get company, year/month, and partnumber. If those are good enough can I skip the order by, or should I still use it for the same fields?