CTE with multiple unions not merging all columns

fredmeissner · July 2, 2019, 3:47pm

I am trying to get a monthly count of quantity of parts on order, suggested quantity to order, and the demand quantity. These all use different tables, with different date columns, so I set up a CTE with two unions to combine them. It mostly works, but I have a feeling I didn’t set up my dummy columns correctly; I create calculated fields set to null to make sure the CTE/unions have matching columns.

Results (e.g. rows 5 and 6 should be combined so that it has both open qty. and required qty.):

CTE:

Unions:

SubQuery list:

(select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	(case               when  datepart(month,JobHead.StartDate)= '1' then concat(datepart(year,JobHead.StartDate),'-01-January')         when  datepart(month,JobHead.StartDate)= '2' then concat(datepart(year,JobHead.StartDate),'-02-February')          when  datepart(month,JobHead.StartDate)= '3' then concat(datepart(year,JobHead.StartDate),'-03-March')          when  datepart(month,JobHead.StartDate)= '4' then concat(datepart(year,JobHead.StartDate),'-04-April')          when  datepart(month,JobHead.StartDate)= '5' then concat(datepart(year,JobHead.StartDate),'-05-May')          when  datepart(month,JobHead.StartDate)= '6' then concat(datepart(year,JobHead.StartDate),'-06-June')          when  datepart(month,JobHead.StartDate)= '7' then concat(datepart(year,JobHead.StartDate),'-07-July')          when  datepart(month,JobHead.StartDate)= '8' then concat(datepart(year,JobHead.StartDate),'-08-August')          when  datepart(month,JobHead.StartDate)= '9' then concat(datepart(year,JobHead.StartDate),'-09-September')          when  datepart(month,JobHead.StartDate)= '10' then concat(datepart(year,JobHead.StartDate),'-10-October')          when  datepart(month,JobHead.StartDate)= '11' then concat(datepart(year,JobHead.StartDate),'-11-November')          when  datepart(month,JobHead.StartDate)= '12' then concat(datepart(year,JobHead.StartDate),'-12-December')          else null  end) as [Calculated_MonthYear],
	(null) as [Calculated_POSugQty],
	(null) as [Calculated_OpenQty],
	(sum(JobMtl.RequiredQty)) as [Calculated_RequiredQty]
from Erp.JobHead as JobHead
inner join Erp.JobMtl as JobMtl on 
	JobHead.Company = JobMtl.Company
	and JobHead.JobNum = JobMtl.JobNum
	and ( JobMtl.RequiredQty > 0  )

where (JobHead.JobType = 'MFG'  and JobHead.JobComplete = 0)
group by [JobMtl].[Company],
	[JobMtl].[PartNum],
	(case               when  datepart(month,JobHead.StartDate)= '1' then concat(datepart(year,JobHead.StartDate),'-01-January')         when  datepart(month,JobHead.StartDate)= '2' then concat(datepart(year,JobHead.StartDate),'-02-February')          when  datepart(month,JobHead.StartDate)= '3' then concat(datepart(year,JobHead.StartDate),'-03-March')          when  datepart(month,JobHead.StartDate)= '4' then concat(datepart(year,JobHead.StartDate),'-04-April')          when  datepart(month,JobHead.StartDate)= '5' then concat(datepart(year,JobHead.StartDate),'-05-May')          when  datepart(month,JobHead.StartDate)= '6' then concat(datepart(year,JobHead.StartDate),'-06-June')          when  datepart(month,JobHead.StartDate)= '7' then concat(datepart(year,JobHead.StartDate),'-07-July')          when  datepart(month,JobHead.StartDate)= '8' then concat(datepart(year,JobHead.StartDate),'-08-August')          when  datepart(month,JobHead.StartDate)= '9' then concat(datepart(year,JobHead.StartDate),'-09-September')          when  datepart(month,JobHead.StartDate)= '10' then concat(datepart(year,JobHead.StartDate),'-10-October')          when  datepart(month,JobHead.StartDate)= '11' then concat(datepart(year,JobHead.StartDate),'-11-November')          when  datepart(month,JobHead.StartDate)= '12' then concat(datepart(year,JobHead.StartDate),'-12-December')          else null  end)
union
select 
	[PODetail].[Company] as [PODetail_Company],
	[PODetail].[PartNum] as [PODetail_PartNum],
	(case               when  datepart(month,PORel.DueDate)= '1' then concat(datepart(year,PORel.DueDate),'-01-January')         when  datepart(month,PORel.DueDate)= '2' then concat(datepart(year,PORel.DueDate),'-02-February')          when  datepart(month,PORel.DueDate)= '3' then concat(datepart(year,PORel.DueDate),'-03-March')          when  datepart(month,PORel.DueDate)= '4' then concat(datepart(year,PORel.DueDate),'-04-April')          when  datepart(month,PORel.DueDate)= '5' then concat(datepart(year,PORel.DueDate),'-05-May')          when  datepart(month,PORel.DueDate)= '6' then concat(datepart(year,PORel.DueDate),'-06-June')          when  datepart(month,PORel.DueDate)= '7' then concat(datepart(year,PORel.DueDate),'-07-July')          when  datepart(month,PORel.DueDate)= '8' then concat(datepart(year,PORel.DueDate),'-08-August')          when  datepart(month,PORel.DueDate)= '9' then concat(datepart(year,PORel.DueDate),'-09-September')          when  datepart(month,PORel.DueDate)= '10' then concat(datepart(year,PORel.DueDate),'-10-October')          when  datepart(month,PORel.DueDate)= '11' then concat(datepart(year,PORel.DueDate),'-11-November')          when  datepart(month,PORel.DueDate)= '12' then concat(datepart(year,PORel.DueDate),'-12-December')          else null  end) as [Calculated_MonthYear],
	(null) as [Calculated_POSugQty],
	(sum(PORel.RelQty)) as [Calculated_OpenQty],
	(null) as [Calculated_RequiredQty]
from Erp.PODetail as PODetail
inner join Erp.PORel as PORel on 
	PODetail.Company = PORel.Company
	and PODetail.PONUM = PORel.PONum
	and PODetail.POLine = PORel.POLine
	and ( PORel.OpenRelease = 1  and PORel.VoidRelease = 0  )

group by [PODetail].[Company],
	[PODetail].[PartNum],
	(case               when  datepart(month,PORel.DueDate)= '1' then concat(datepart(year,PORel.DueDate),'-01-January')         when  datepart(month,PORel.DueDate)= '2' then concat(datepart(year,PORel.DueDate),'-02-February')          when  datepart(month,PORel.DueDate)= '3' then concat(datepart(year,PORel.DueDate),'-03-March')          when  datepart(month,PORel.DueDate)= '4' then concat(datepart(year,PORel.DueDate),'-04-April')          when  datepart(month,PORel.DueDate)= '5' then concat(datepart(year,PORel.DueDate),'-05-May')          when  datepart(month,PORel.DueDate)= '6' then concat(datepart(year,PORel.DueDate),'-06-June')          when  datepart(month,PORel.DueDate)= '7' then concat(datepart(year,PORel.DueDate),'-07-July')          when  datepart(month,PORel.DueDate)= '8' then concat(datepart(year,PORel.DueDate),'-08-August')          when  datepart(month,PORel.DueDate)= '9' then concat(datepart(year,PORel.DueDate),'-09-September')          when  datepart(month,PORel.DueDate)= '10' then concat(datepart(year,PORel.DueDate),'-10-October')          when  datepart(month,PORel.DueDate)= '11' then concat(datepart(year,PORel.DueDate),'-11-November')          when  datepart(month,PORel.DueDate)= '12' then concat(datepart(year,PORel.DueDate),'-12-December')          else null  end)
union
select 
	[SugPoDtl].[Company] as [SugPoDtl_Company],
	[SugPoDtl].[PartNum] as [SugPoDtl_PartNum],
	(case               when  datepart(month,SugPODtl.DueDate)= '1' then concat(datepart(year,SugPODtl.DueDate),'-01-January')         when  datepart(month,SugPODtl.DueDate)= '2' then concat(datepart(year,SugPODtl.DueDate),'-02-February')          when  datepart(month,SugPODtl.DueDate)= '3' then concat(datepart(year,SugPODtl.DueDate),'-03-March')          when  datepart(month,SugPODtl.DueDate)= '4' then concat(datepart(year,SugPODtl.DueDate),'-04-April')          when  datepart(month,SugPODtl.DueDate)= '5' then concat(datepart(year,SugPODtl.DueDate),'-05-May')          when  datepart(month,SugPODtl.DueDate)= '6' then concat(datepart(year,SugPODtl.DueDate),'-06-June')          when  datepart(month,SugPODtl.DueDate)= '7' then concat(datepart(year,SugPODtl.DueDate),'-07-July')          when  datepart(month,SugPODtl.DueDate)= '8' then concat(datepart(year,SugPODtl.DueDate),'-08-August')          when  datepart(month,SugPODtl.DueDate)= '9' then concat(datepart(year,SugPODtl.DueDate),'-09-September')          when  datepart(month,SugPODtl.DueDate)= '10' then concat(datepart(year,SugPODtl.DueDate),'-10-October')          when  datepart(month,SugPODtl.DueDate)= '11' then concat(datepart(year,SugPODtl.DueDate),'-11-November')          when  datepart(month,SugPODtl.DueDate)= '12' then concat(datepart(year,SugPODtl.DueDate),'-12-December')          else null  end) as [Calculated_MonthYear],
	(sum(SugPoDtl.RelQty)) as [Calculated_POSugQty],
	(null) as [Calculated_OpenQty],
	(null) as [Calculated_RequiredQty]
from Erp.SugPoDtl as SugPoDtl
group by [SugPoDtl].[Company],
	[SugPoDtl].[PartNum],
	(case               when  datepart(month,SugPODtl.DueDate)= '1' then concat(datepart(year,SugPODtl.DueDate),'-01-January')         when  datepart(month,SugPODtl.DueDate)= '2' then concat(datepart(year,SugPODtl.DueDate),'-02-February')          when  datepart(month,SugPODtl.DueDate)= '3' then concat(datepart(year,SugPODtl.DueDate),'-03-March')          when  datepart(month,SugPODtl.DueDate)= '4' then concat(datepart(year,SugPODtl.DueDate),'-04-April')          when  datepart(month,SugPODtl.DueDate)= '5' then concat(datepart(year,SugPODtl.DueDate),'-05-May')          when  datepart(month,SugPODtl.DueDate)= '6' then concat(datepart(year,SugPODtl.DueDate),'-06-June')          when  datepart(month,SugPODtl.DueDate)= '7' then concat(datepart(year,SugPODtl.DueDate),'-07-July')          when  datepart(month,SugPODtl.DueDate)= '8' then concat(datepart(year,SugPODtl.DueDate),'-08-August')          when  datepart(month,SugPODtl.DueDate)= '9' then concat(datepart(year,SugPODtl.DueDate),'-09-September')          when  datepart(month,SugPODtl.DueDate)= '10' then concat(datepart(year,SugPODtl.DueDate),'-10-October')          when  datepart(month,SugPODtl.DueDate)= '11' then concat(datepart(year,SugPODtl.DueDate),'-11-November')          when  datepart(month,SugPODtl.DueDate)= '12' then concat(datepart(year,SugPODtl.DueDate),'-12-December')          else null  end))

select 
	[JobQty].[JobMtl_Company] as [JobMtl_Company],
	[JobQty].[JobMtl_PartNum] as [JobMtl_PartNum],
	[JobQty].[Calculated_MonthYear] as [Calculated_MonthYear],
	[JobQty].[Calculated_POSugQty] as [Calculated_POSugQty],
	[JobQty].[Calculated_OpenQty] as [Calculated_OpenQty],
	[JobQty].[Calculated_RequiredQty] as [Calculated_RequiredQty],
	(case                
     when OnHandQty.PartBin_WarehouseCode <> 'BRZINSP' then OnHandQty.Calculated_TotalOnHand      
     else 0    
 end) as [Calculated_OnHandQty],
	(case           
     when OnHandQty.PartBin_WarehouseCode = 'BRZINSP' then OnHandQty.Calculated_TotalOnHand 
     else 0   
 end) as [Calculated_InspQty],
	(sum(DMRHead.TotDiscrepantQty)) as [Calculated_DMRQty]
from  JobQty  as JobQty
left outer join Erp.DMRHead as DMRHead on 
	JobQty.JobMtl_Company = DMRHead.Company
	and JobQty.JobMtl_PartNum = DMRHead.PartNum
	and ( DMRHead.OpenDMR = 1  )

left outer join  (select 
	[PartBin].[Company] as [PartBin_Company],
	[PartBin].[PartNum] as [PartBin_PartNum],
	[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
	(sum(PartBin.OnhandQty)) as [Calculated_TotalOnHand]
from Erp.PartBin as PartBin
group by [PartBin].[Company],
	[PartBin].[PartNum],
	[PartBin].[WarehouseCode])  as OnHandQty on 
	JobQty.JobMtl_Company = OnHandQty.PartBin_Company
	and JobQty.JobMtl_PartNum = OnHandQty.PartBin_PartNum
where (JobQty.JobMtl_PartNum = '7002077')
group by [JobQty].[JobMtl_Company],
	[JobQty].[JobMtl_PartNum],
	[JobQty].[Calculated_MonthYear],
	[JobQty].[Calculated_POSugQty],
	[JobQty].[Calculated_OpenQty],
	[JobQty].[Calculated_RequiredQty],
	(case                
     when OnHandQty.PartBin_WarehouseCode <> 'BRZINSP' then OnHandQty.Calculated_TotalOnHand      
     else 0    
 end),
	(case           
     when OnHandQty.PartBin_WarehouseCode = 'BRZINSP' then OnHandQty.Calculated_TotalOnHand 
     else 0   
 end)

Banderson · July 2, 2019, 6:03pm

I’m not quite understanding why you need a CTE. This should just be 3 subqueries joined together with the columns displayed. Joined on part number and your calculated date field. Unless I am reading it wrong. Unions stack rows together, joins will place columns next to each other.

CTE’s are generally used for hierarchical data, which I don’t see in your use case.

fredmeissner · July 2, 2019, 6:07pm

Yeah…I changed it to a regular subquery about 30 minutes ago. But won’t I still need the union so that the date column will be a combination of the 3 different date columns?

Banderson · July 2, 2019, 6:07pm

No, just display only one of them.

union works like this.

And this is how a join works.

fredmeissner · July 2, 2019, 6:10pm

But if I have 3 units on order for March, and the job starts in May, and the job date is the only one I display, I’m going to lose that March date being shown. Hopefully that makes sense.

Banderson · July 2, 2019, 6:14pm

Not quite.

What are you planning on joining them with? The part number? The Job number? or the Date? What combination of those? Something else? If you have different months that you need on the same row, what’s the logical grouping to key off of? Once you figure that out, we can figure out how to display it.

If you just want that grid that you have, but instead of up to 3 rows for each month, a single row, then just join the subqueries. Use outer joins to be able to leave them blank if there is nothing to show.

fredmeissner · July 2, 2019, 6:18pm

This is what I am trying to accomplish, joined by part number.

Capture1

Banderson · July 2, 2019, 6:19pm

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.

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. Make sense? I can’t remember if it’s going to return empty string or null, so you’ll have to play with that.

Something along the lines of this.

case
   when POSugQty.Calc_Date is not '' then POSugQty.Calc_Date 
   when JobQty.Calc_Date is not '' then JobQty.Calc_Date
   else OpenQty.Calc_Date
end

fredmeissner · July 2, 2019, 7:01pm

I can’t really do a calculated field based on null or empty date values, because the joins just duplicate rows as shown in the example below. Hence wanting to have the 3 date columns be shared by one column in a union. Otherwise I’m not sure how you deal with this data.

Banderson · July 2, 2019, 7:02pm

join on your partnumber and your calculated date field. Not just part number.

fredmeissner · July 2, 2019, 7:05pm

Ooooo okay, except I don’t see the calculate date field I create in JobMtl in the list.

Banderson · July 2, 2019, 7:06pm

you should have 3 subqueries. Bring all of those 3 into 1 top query. They join there.

Banderson · July 2, 2019, 7:08pm

Can you post your BAQ here?

fredmeissner · July 2, 2019, 7:15pm

After much clicking…I think I’ve got it. Thank you so much for the help!

Banderson · July 2, 2019, 7:19pm

There you go.

And if you want to get those dates consolidated to one column you can do the calculated field trick from above. Then just show the new field.