So I am working on a BAQ that will Start with our sales orders, and work its way down the components of jobs to calculate the remaining amount of time is left to complete that release. Because sales orders and jobs are not linked, i am joining my tables by part number. Its a huge BAQ, but its going well until i tried to filter down the job numbers. So right now its giving us every open job number for every part. We only need the first open job for each part. I use MIN on the jobs date to get the first job, but it doesn’t seem to be filtering anything out. I still get every job and every date. If i remove the job number from the query then it works fine. i get only the one date. I still need the job number to show somehow and using MIN on the job number is not possible. only about 70% of our jobs are created in order. Ill post the code for this BAQ, but it is massive. I hope you guys understand what im trying to say, and i appreciate any help i can get.
Thank you
With [CTE1] AS
(select
[JobHead].[JobNum] as [JobHead_JobNum],
(min( JobHead.ReqDueDate )) as [Calculated_Firstopenjob]
from Erp.JobHead as JobHead
where (JobHead.JobClosed = False and JobHead.JobComplete = False)
group by [JobHead].[JobNum])
select
[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderRel].[OpenRelease] as [OrderRel_OpenRelease],
[OrderRel].[ReqDate] as [OrderRel_ReqDate],
[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
[OrderRel].[OurStockShippedQty] as [OrderRel_OurStockShippedQty],
[OrderRel].[PartNum] as [OrderRel_PartNum],
[JobHead1].[JobNum] as [JobHead1_JobNum],
[SubQuery3].[Calculated_OnHandQty] as [Calculated_OnHandQty],
[SubQuery2].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[SubQuery2].[JobHead_JobNum] as [JobHead_JobNum],
[SubQuery2].[PartMtl_QtyPer] as [PartMtl_QtyPer],
(( SubQuery2.PartMtl_QtyPer * OrderRel.SellingReqQty )) as [Calculated_ReqQty],
[SubQuery2].[Calculated_OnHandQty] as [Calculated_OnHandQty01],
[SubQuery2].[PartMtl1_MtlPartNum] as [PartMtl1_MtlPartNum],
[SubQuery2].[JobHead_JobNum01] as [JobHead_JobNum01],
[SubQuery2].[PartMtl1_QtyPer] as [PartMtl1_QtyPer],
(( SubQuery2.PartMtl1_QtyPer * OrderRel.SellingReqQty )) as [Calculated_ReqQty1],
[SubQuery2].[Calculated_OnHandQty04] as [Calculated_OnHandQty04],
[SubQuery2].[PartMtl2_MtlPartNum] as [PartMtl2_MtlPartNum],
[SubQuery2].[JobHead_JobNum02] as [JobHead_JobNum02],
[SubQuery2].[PartMtl2_QtyPer] as [PartMtl2_QtyPer],
(( SubQuery2.PartMtl2_QtyPer * OrderRel.SellingReqQty )) as [Calculated_RegQty2],
[SubQuery2].[Calculated_OnHandQty01] as [Calculated_OnHandQty0101],
[SubQuery2].[PartMtl3_MtlPartNum] as [PartMtl3_MtlPartNum],
[SubQuery2].[JobHead_JobNum03] as [JobHead_JobNum03],
[SubQuery2].[PartMtl3_QtyPer] as [PartMtl3_QtyPer],
(( SubQuery2.PartMtl3_QtyPer * OrderRel.SellingReqQty )) as [Calculated_ReqQty3],
[SubQuery2].[Calculated_OnHandQty02] as [Calculated_OnHandQty02],
[SubQuery2].[PartMtl4_MtlPartNum] as [PartMtl4_MtlPartNum],
[SubQuery2].[JobHead_JobNum04] as [JobHead_JobNum04],
[SubQuery2].[PartMtl4_QtyPer] as [PartMtl4_QtyPer],
(( SubQuery2.PartMtl4_QtyPer * OrderRel.SellingReqQty )) as [Calculated_ReqQty4],
[SubQuery2].[Calculated_OnHandQty03] as [Calculated_OnHandQty03],
[SubQuery2].[PartMtl5_MtlPartNum] as [PartMtl5_MtlPartNum],
[SubQuery2].[JobHead_JobNum05] as [JobHead_JobNum05],
[SubQuery2].[PartMtl5_QtyPer] as [PartMtl5_QtyPer],
(( SubQuery2.PartMtl5_QtyPer * OrderRel.SellingReqQty )) as [Calculated_ReqQty5],
[SubQuery2].[Calculated_OnHandQty05] as [Calculated_OnHandQty05],
[SubQuery2].[PartMtl6_MtlPartNum] as [PartMtl6_MtlPartNum],
[SubQuery2].[JobHead_JobNum06] as [JobHead_JobNum06],
[SubQuery2].[PartMtl6_QtyPer] as [PartMtl6_QtyPer],
(( SubQuery2.PartMtl6_QtyPer * OrderRel.SellingReqQty )) as [Calculated_ReqQty6],
[SubQuery2].[Calculated_OnHandQty06] as [Calculated_OnHandQty06]
from Erp.JobHead as JobHead1
left outer join Erp.OrderRel as OrderRel on
OrderRel.PartNum = JobHead1.PartNum
and ( OrderRel.ReqDate < GETDATE() and OrderRel.OpenRelease = True and not OrderRel.SellingReqQty = OrderRel.OurStockShippedQty )
inner join Erp.OrderHed as OrderHed on
OrderHed.Company = OrderRel.Company
And
OrderHed.OrderNum = OrderRel.OrderNum
and ( OrderHed.OpenOrder = True and OrderHed.OrderNum = 407153 )
inner join (select
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[CTE1].[JobHead_JobNum] as [JobHead_JobNum],
[SubQuery30].[Calculated_OnHandQty] as [Calculated_OnHandQty],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
[CTE11].[JobHead_JobNum] as [JobHead_JobNum01],
[SubQuery31].[Calculated_OnHandQty] as [Calculated_OnHandQty04],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl2].[MtlPartNum] as [PartMtl2_MtlPartNum],
[CTE12].[JobHead_JobNum] as [JobHead_JobNum02],
[SubQuery32].[Calculated_OnHandQty] as [Calculated_OnHandQty01],
[PartMtl2].[QtyPer] as [PartMtl2_QtyPer],
[PartMtl3].[MtlPartNum] as [PartMtl3_MtlPartNum],
[CTE13].[JobHead_JobNum] as [JobHead_JobNum03],
[SubQuery33].[Calculated_OnHandQty] as [Calculated_OnHandQty02],
[PartMtl3].[QtyPer] as [PartMtl3_QtyPer],
[PartMtl4].[MtlPartNum] as [PartMtl4_MtlPartNum],
[CTE14].[JobHead_JobNum] as [JobHead_JobNum04],
[SubQuery34].[Calculated_OnHandQty] as [Calculated_OnHandQty03],
[PartMtl4].[QtyPer] as [PartMtl4_QtyPer],
[PartMtl5].[MtlPartNum] as [PartMtl5_MtlPartNum],
[CTE15].[JobHead_JobNum] as [JobHead_JobNum05],
[SubQuery35].[Calculated_OnHandQty] as [Calculated_OnHandQty05],
[PartMtl5].[QtyPer] as [PartMtl5_QtyPer],
[PartMtl6].[MtlPartNum] as [PartMtl6_MtlPartNum],
[CTE16].[JobHead_JobNum] as [JobHead_JobNum06],
[SubQuery36].[Calculated_OnHandQty] as [Calculated_OnHandQty06],
[PartMtl6].[QtyPer] as [PartMtl6_QtyPer]
from Erp.PartMtl as PartMtl
left outer join Erp.PartMtl as PartMtl1 on
PartMtl.MtlPartNum = PartMtl1.PartNum
left outer join Erp.PartMtl as PartMtl2 on
PartMtl1.MtlPartNum = PartMtl2.PartNum
left outer join Erp.PartMtl as PartMtl3 on
PartMtl2.MtlPartNum = PartMtl3.PartNum
left outer join Erp.PartMtl as PartMtl4 on
PartMtl3.MtlPartNum = PartMtl4.PartNum
left outer join Erp.PartMtl as PartMtl5 on
PartMtl4.MtlPartNum = PartMtl5.PartNum
left outer join Erp.PartMtl as PartMtl6 on
PartMtl5.MtlPartNum = PartMtl6.PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery30 on
PartMtl.MtlPartNum = SubQuery30.PartWhse_PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery31 on
PartMtl1.MtlPartNum = SubQuery31.PartWhse_PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery32 on
PartMtl2.MtlPartNum = SubQuery32.PartWhse_PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery33 on
PartMtl3.MtlPartNum = SubQuery33.PartWhse_PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery34 on
PartMtl4.MtlPartNum = SubQuery34.PartWhse_PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery35 on
PartMtl5.MtlPartNum = SubQuery35.PartWhse_PartNum
left outer join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery36 on
PartMtl6.MtlPartNum = SubQuery36.PartWhse_PartNum
left outer join CTE1 as CTE1 on
PartMtl.MtlPartNum = JobHead_PartNum
left outer join CTE1 as CTE11 on
PartMtl1.MtlPartNum = JobHead_PartNum
left outer join CTE1 as CTE12 on
PartMtl2.MtlPartNum = JobHead_PartNum
left outer join CTE1 as CTE13 on
PartMtl3.MtlPartNum = JobHead_PartNum
left outer join CTE1 as CTE14 on
PartMtl4.MtlPartNum = JobHead_PartNum
left outer join CTE1 as CTE15 on
PartMtl5.MtlPartNum = JobHead_PartNum
left outer join CTE1 as CTE16 on
PartMtl6.MtlPartNum = JobHead_PartNum) as SubQuery2 on
OrderRel.PartNum = SubQuery2.PartMtl_PartNum
inner join (select
[PartWhse].[PartNum] as [PartWhse_PartNum],
(sum( PartWhse.OnHandQty )) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by [PartWhse].[PartNum]) as SubQuery3 on
SubQuery2.PartMtl_PartNum = SubQuery3.PartWhse_PartNum
where JobHead1.ReqDueDate = ANY (select Calculated_Firstopenjob from CTE1) and JobHead1.JobClosed = ANY (select JobHead_JobClosed from CTE1) and JobHead1.JobComplete = ANY (select JobHead_JobComplete from CTE1) and JobHead1.JobNum = ANY (select JobHead_JobNum from CTE1)
group by [OrderHed].[OpenOrder],
[OrderHed].[OrderNum],
[OrderHed].[OrderDate],
[OrderRel].[OrderLine],
[OrderRel].[OrderRelNum],
[OrderRel].[OpenRelease],
[OrderRel].[ReqDate],
[OrderRel].[SellingReqQty],
[OrderRel].[OurStockShippedQty],
[OrderRel].[PartNum],
[JobHead1].[JobNum],
[SubQuery3].[Calculated_OnHandQty],
[SubQuery2].[PartMtl_MtlPartNum],
[SubQuery2].[JobHead_JobNum],
[SubQuery2].[PartMtl_QtyPer],
(( SubQuery2.PartMtl_QtyPer * OrderRel.SellingReqQty )),
[SubQuery2].[Calculated_OnHandQty],
[SubQuery2].[PartMtl1_MtlPartNum],
[SubQuery2].[JobHead_JobNum01],
[SubQuery2].[PartMtl1_QtyPer],
(( SubQuery2.PartMtl1_QtyPer * OrderRel.SellingReqQty )),
[SubQuery2].[Calculated_OnHandQty04],
[SubQuery2].[PartMtl2_MtlPartNum],
[SubQuery2].[JobHead_JobNum02],
[SubQuery2].[PartMtl2_QtyPer],
(( SubQuery2.PartMtl2_QtyPer * OrderRel.SellingReqQty )),
[SubQuery2].[Calculated_OnHandQty01],
[SubQuery2].[PartMtl3_MtlPartNum],
[SubQuery2].[JobHead_JobNum03],
[SubQuery2].[PartMtl3_QtyPer],
(( SubQuery2.PartMtl3_QtyPer * OrderRel.SellingReqQty )),
[SubQuery2].[Calculated_OnHandQty02],
[SubQuery2].[PartMtl4_MtlPartNum],
[SubQuery2].[JobHead_JobNum04],
[SubQuery2].[PartMtl4_QtyPer],
(( SubQuery2.PartMtl4_QtyPer * OrderRel.SellingReqQty )),
[SubQuery2].[Calculated_OnHandQty03],
[SubQuery2].[PartMtl5_MtlPartNum],
[SubQuery2].[JobHead_JobNum05],
[SubQuery2].[PartMtl5_QtyPer],
(( SubQuery2.PartMtl5_QtyPer * OrderRel.SellingReqQty )),
[SubQuery2].[Calculated_OnHandQty05],
[SubQuery2].[PartMtl6_MtlPartNum],
[SubQuery2].[JobHead_JobNum06],
[SubQuery2].[PartMtl6_QtyPer],
(( SubQuery2.PartMtl6_QtyPer * OrderRel.SellingReqQty )),
[SubQuery2].[Calculated_OnHandQty06]
