I got a BAQ that a previous employee made years ago. It’s a production status report for every week.
It’s quite complicated (honestly overly complicated) and makes a number of subqueries. Do not have the time to rewrite it as of now but I need to fix it.
The issue is and has been from the get go, it’s getting 14 days of production status. I need 7. No where in the query does it show a limit for 14 days. Either I’m missing it or there is more to the story.
Maybe Crystal reports is what is filtering the data but I haven’t found anything in that, that could do that. Maybe someone could suggest something to look at.
Here is the query:
select distinct
[ProdGrup].[Description] as [ProdGrup_Description],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[CreateDate] as [JobHead_CreateDate],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[PartDescription] as [JobHead_PartDescription],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[OrderAmt] as [OrderHed_OrderAmt],
[Customer].[Name] as [Customer_Name],
[SNTran].[SerialNumber] as [SNTran_SerialNumber],
((case when RcvHead.Received = 1 or len(VIN.Calculated_Character02) > 0 or VIN.Calculated_SNStatus = ‘Consumed’ then
‘ON GROUND’
else
‘NOT ON GROUND’ end)) as [Calculated_OrderStatus],
((case when upper(substring(ProdGrup.Description,11,13))=‘VAN’ then
‘VANS-SPECIALTY’
when upper(substring(ProdGrup.Description,1,8))=‘MOBILITY’ then
‘MOBILITY’
when upper(substring(ProdGrup.Description,1,4))=‘LABO’ then
ProdGrup.Description
when upper(substring(ProdGrup.Description,1,4))=‘WARR’ then
ProdGrup.Description
when upper(substring(ProdGrup.Description,1,4))=‘VANS’ then
ProdGrup.Description
when upper(substring(ProdGrup.Description,1,4))=‘CARS’ then
ProdGrup.Description
else
‘TRUCKS’ end)) as [Calculated_JobType],
((case when RcvHead.Received = 1 then
RcvHead.ArrivedDate
else (case when len(VIN.Calculated_Character02) > 0 or VIN.Calculated_SNStatus = ‘Consumed’ then
VIN.Calculated_Date01
else
NULL end) end)) as [Calculated_OnGroundDate],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
[Part_Chassis].[ShortChar04] as [Part_Chassis_ShortChar04],
((case when convert(int, InvcHead.InvoiceNum) = 10135 or convert(int, InvcHead.InvoiceNum) = 10136 then
0
else (case when JobHead.JobClosed = 0 then
1
else (case when ((InvcHead.InvoiceNum) = null) and JobHead.JobClosed = 1 then
0
else (case when InvcHead.OpenInvoice = 1 then
1
else
0 end) end) end) end)) as [Calculated_OpenJob],
[JobHead].[JobCompletionDate] as [JobHead_JobCompletionDate],
[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
[SalesRep].[Name] as [SalesRep_Name],
[JobHead].[QuoteNum] as [JobHead_QuoteNum],
[JobHead].[ShortChar01] as [JobHead_ShortChar01],
[JobHead].[StartDate] as [JobHead_StartDate],
[JobHead].[DueDate] as [JobHead_DueDate],
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobHead].[CheckBox01] as [JobHead_CheckBox01],
[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[JobHead].[WIStartDate] as [JobHead_WIStartDate],
(ISNULL(JobMatlFlags.Calculated_JobHasTintMtl,‘N’)) as [Calculated_Tint],
(ISNULL(JobMatlFlags.Calculated_JobHasLiner,‘N’)) as [Calculated_Allign],
(ISNULL(JobMatlFlags.Calculated_JobHasLeather,‘N’)) as [Calculated_Leather],
(ISNULL(JobMatlFlags.Calculated_JobHasPaintedParts,‘N’)) as [Calculated_Paint],
[LastInvoice].[Calculated_Invoice] as [Calculated_Invoice],
[VIN].[Calculated_VIN] as [Calculated_VIN],
[VIN].[Part2_ShortChar04] as [Part2_ShortChar04],
[JobHead].[Date01] as [JobHead_Date01],
[ProdGrup].[ProdCode] as [ProdGrup_ProdCode]
from Erp.JobHead as JobHead
inner join Erp.Part as Part on
JobHead.Company = Part.Company
and JobHead.PartNum = Part.PartNum
and ( not Part.ProdCode in (‘LABOR’, ‘WARRANTY’) )
left outer join Erp.ProdGrup as ProdGrup on
Part.Company = ProdGrup.Company
and Part.ProdCode = ProdGrup.ProdCode
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
inner join Erp.OrderHed as OrderHed on
JobProd.Company = OrderHed.Company
and JobProd.OrderNum = OrderHed.OrderNum
left outer join Erp.SalesRep as SalesRep on
OrderHed.Company = SalesRep.Company
and OrderHed.SalesRepList = SalesRep.SalesRepCode
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
left outer join Erp.InvcHead as InvcHead on
JobProd.Company = InvcHead.Company
and JobProd.OrderNum = InvcHead.OrderNum
left outer join (select
[InvcHead1].[Company] as [InvcHead1_Company],
[InvcHead1].[OrderNum] as [InvcHead1_OrderNum],
(max(InvcHead1.InvoiceNum)) as [Calculated_Invoice]
from Erp.InvcHead as InvcHead1
group by InvcHead1.Company,InvcHead1.OrderNum) as LastInvoice on
LastInvoice.InvcHead1_Company = InvcHead.Company
and LastInvoice.InvcHead1_OrderNum = InvcHead.OrderNum
and LastInvoice.Calculated_Invoice = InvcHead.InvoiceNum
left outer join Erp.SNTran as SNTran on
JobHead.Company = SNTran.Company
and JobHead.JobNum = SNTran.JobNum
and ( SNTran.TranType = ‘STK-MTL’ )
left outer join Erp.RcvHead as RcvHead on
SNTran.Company = RcvHead.Company
and SNTran.PurPoint = RcvHead.PurPoint
and SNTran.VendorNum = RcvHead.VendorNum
and SNTran.PackSlip = RcvHead.PackSlip
left outer join Erp.SerialNo as SerialNo on
SNTran.Company = SerialNo.Company
and SNTran.PartNum = SerialNo.PartNum
and SNTran.SerialNumber = SerialNo.SerialNumber
left outer join Erp.Part as Part_Chassis on
SerialNo.Company = Part_Chassis.Company
and SerialNo.PartNum = Part_Chassis.PartNum
left outer join Erp.JobMtl as JobMtl on
JobMtl.Company = SNTran.Company
and JobMtl.JobNum = SNTran.JobNum
and JobMtl.PartNum = SNTran.PartNum
left outer join (select
[JobMtl1].[Company] as [JobMtl1_Company],
[JobMtl1].[JobNum] as [JobMtl1_JobNum],
(max(case when JobMtl1.PartNum like ‘VOR.%’ then ‘Y’ else ‘N’ end)) as [Calculated_JobHasLiner],
(max(case when isnull(JobMtl1.Description,’’) like ‘%LEATHER%’ then ‘Y’ else ‘N’ end)) as [Calculated_JobHasLeather],
(max(case when JobMtl1.PartNum like ‘WCT.%’ then ‘Y’ else ‘N’ end)) as [Calculated_JobHasTintMtl],
(max(case when JobMtl1.PartNum like ‘WPP.%’ then ‘Y’ else ‘N’ end)) as [Calculated_JobHasPaintedParts]
from Erp.JobMtl as JobMtl1
left outer join Erp.Part as Part3 on
JobMtl1.Company = Part3.Company
and JobMtl1.PartNum = Part3.PartNum
left outer join Erp.ProdGrup as ProdGrup1 on
Part3.Company = ProdGrup1.Company
and Part3.ProdCode = ProdGrup1.ProdCode
where (JobMtl1.PartNum like ‘VOR%’ or JobMtl1.PartNum like ‘WCT%’ or (JobMtl1.Description like ‘%LEATHER%’ or JobMtl1.PartNum like ‘WPP%’ and not ProdGrup1.Description like ‘CHASSIS%’ ))
group by JobMtl1.Company,JobMtl1.JobNum) as JobMatlFlags on
JobMatlFlags.JobMtl1_Company = JobHead.Company
and JobMatlFlags.JobMtl1_JobNum = JobHead.JobNum
left outer join (select
[JobAsmbl].[Company] as [JobAsmbl_Company],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
(max(case when JobAsmbl.PartNum like ‘%.T023%’ then ‘Y’ else ‘N’ end)) as [Calculated_HasLeather]
from Erp.JobAsmbl as JobAsmbl
group by JobAsmbl.Company,JobAsmbl.JobNum) as JobAsmFlags on
JobAsmFlags.JobAsmbl_Company = JobHead.Company
and JobAsmFlags.JobAsmbl_JobNum = JobHead.JobNum
left outer join (select
[SNTran1].[Company] as [SNTran1_Company],
[SNTran1].[JobNum] as [SNTran1_JobNum],
[Part2].[ShortChar04] as [Part2_ShortChar04],
(MAX(SNTran1.SerialNumber)) as [Calculated_VIN],
(MAX(SerialNo1.SNStatus)) as [Calculated_SNStatus],
(MAX(SerialNo1.Character02)) as [Calculated_Character02],
(MAX(SerialNo1.Date01)) as [Calculated_Date01]
from Erp.SNTran as SNTran1
inner join Erp.JobMtl as JobMtl2 on
JobMtl2.Company = SNTran1.Company
and JobMtl2.JobNum = SNTran1.JobNum
and JobMtl2.PartNum = SNTran1.PartNum
inner join Erp.Part as Part2 on
Part2.Company = JobMtl2.Company
and Part2.PartNum = JobMtl2.PartNum
inner join Erp.SerialNo as SerialNo1 on
SerialNo1.Company = SNTran1.Company
and SerialNo1.PartNum = SNTran1.PartNum
and SerialNo1.SerialNumber = SNTran1.SerialNumber
where (JobMtl2.CheckBox01 = 0 and SNTran1.TranType = ‘STK-MTL’)
group by SNTran1.Company,SNTran1.JobNum,Part2.ShortChar04) as VIN on
VIN.SNTran1_Company = JobHead.Company
and VIN.SNTran1_JobNum = JobHead.JobNum
where (JobHead.JobNum <> ‘14877’ and JobHead.JobNum <> ‘14882’ and JobHead.JobNum <> ‘14870’ and JobHead.JobClosed = FALSE)
and (((case when convert(int, InvcHead.InvoiceNum) = 10135 or convert(int, InvcHead.InvoiceNum) = 10136 then
0
else (case when JobHead.JobClosed = 0 then
1
else (case when ((InvcHead.InvoiceNum) = null) and JobHead.JobClosed = 1 then
0
else (case when InvcHead.OpenInvoice = 1 then
1
else
0 end) end) end) end)) = 1 and JobHead.CheckBox01 = 0 and (InvcHead.InvoiceNum is null or LastInvoice.InvcHead1_OrderNum is not null) and (JobMtl.JobNum is not null or SNTran.SerialNumber is null))
