Any BAQ gurus want to help me?
I have a BAQ that displays order & shipment info; SO #, PO #, Ship Date, etc I need to then have it remove results when the Ship Date is over one month old.
I thought I had it by adding the ShipDate in the criteria. But on orders with partial shipments, it removes ALL results for that SO. I also tried an exclude subquery but it ran for over 5min before I stopped it.
This is for a BAQ Export, not a dashboard or I’d just filter it there.
Hey Randy,
Have you tried a calculated field that is “y” or “n” depending on the month criteria and then do a Subquery criteria to select only the “y” calculated records?
Mark W.
Mark,
Hmm, interesting idea! Are you thinking an except subquery on the Y/N field?
Would you mind pasting the query phrase? Then we can see which data field you are trying to filter and the impact to the other table results.
Mark is saying, to use a calculated field in the Innersubquery, then your toplevel query would select all item in your innersubquery that are Y (Y being the records you want to show).
I was think something like this:
[cid:image005.jpg@01D29F11.F17B0F40]
[cid:image006.jpg@01D29F11.F17B0F40]
I may be misunderstanding, but it seems like you’re looking for a left join on the shipment table with a table criteria set to the date, and then if you only want orders that have had at least one shipment, you can link orders to an inner query that has shipments with no criteria.
I was trying the suggestions above and need to clean it up --Should of copied the BAQ first.
I think I’m close. Here is what I’m working on, I need orders that haven’t shipped yet plus Shipments within that last 2 months:
select
[OrderHed].[ChangeDate] as [OrderHed_ChangeDate],
[OrderHed].[ChangeTime] as [OrderHed_ChangeTime],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[PONum] as [OrderHed_PONum],
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
[ShipHead].[TrackingNumber] as [ShipHead_TrackingNumber],
(case when shiphead.ReadyToInvoice=1 then concat(745171600,shiphead.PackNum) else null end) as [Calculated_PalletNumber],
((case when (ShipHead.ShipDate ) is null
then ‘Y’
else
(case when ShipHead.ShipDate >= dateadd(mm,-2, cast(getdate() as date)) then ‘Y’ else ‘N’ end)
end)) as [Calculated_ShipFilter]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
And
OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
And
OrderDtl.OrderNum = OrderRel.OrderNum
And
OrderDtl.OrderLine = OrderRel.OrderLine
left outer join Erp.ShipTo as ShipTo on
OrderRel.Company = ShipTo.Company
And
OrderRel.ShipToCustNum = ShipTo.CustNum
And
OrderRel.ShipToNum = ShipTo.ShipToNum
left outer join Erp.ShipDtl as ShipDtl on
OrderRel.Company = ShipDtl.Company
And
OrderRel.OrderNum = ShipDtl.OrderNum
And
OrderRel.OrderLine = ShipDtl.OrderLine
And
OrderRel.OrderRelNum = ShipDtl.OrderRelNum
cross join Erp.ShipVia as ShipVia
left outer join Erp.ShipHead as ShipHead on
ShipDtl.Company = ShipHead.Company
And
ShipDtl.PackNum = ShipHead.PackNum
left outer join Erp.ShipHead as ShipHead and
ShipHead.Company = ShipVia.Company
And
ShipHead.FreightedShipViaCode = ShipVia.ShipViaCode
where (OrderHed.ReservePriorityCode <> ‘SAMPLE’ and (OrderHed.ChangeDate = @Yesterday and OrderHed.ChangeTime >= 68400 ) or (OrderHed.ChangeDate = @Today and OrderHed.ChangeTime < 46800 ))
EXCEPT
select
(GETDATE()) as [Calculated_FDate],
(0) as [Calculated_FInt],
(0) as [Calculated_FInt2],
(’’) as [Calculated_Filler3],
(GETDATE()
/* Constants.Today */) as [Calculated_FDate2],
(’’) as [Calculated_Filler4],
(’’) as [Calculated_Filler5],
(’’
/*(case when (ShipHead.ShipDate ) is null
then ‘Y’
else
(case when ShipHead.ShipDate >= dateadd(mm,-2, cast(getdate() as date)) then ‘Y’ else ‘N’ end)
end)
*/) as [Calculated_ShipFilter1]
from Erp.ShipHead as ShipHead1
order by ShipHead.ShipDate
You may want to use a union of two subqueries. One of orders that haven’t shipped, and one with the shipments for the last two months. Just have to make sure they both have the same fields, calculated fields as null if required to make sure they match.
What I’m doing is trying to convert a old SQL query that someone else wrote. It used a Union but it was duplicating rows Once showing the order-line-rel data and blacks for shipment fields and the same order-line-rel again including the shipment data.
This was not good as the export is for a customer demanded report. I made the BAQ awhile back but now they want to exclude older shipments.
select
[OrderHed].[ChangeDate] as [OrderHed_ChangeDate],
[OrderHed].[ChangeTime] as [OrderHed_ChangeTime],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[PONum] as [OrderHed_PONum],
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
[ShipHead].[TrackingNumber] as [ShipHead_TrackingNumber],
(case when shiphead.ReadyToInvoice=1 then concat(745171600,shiphead.PackNum) else null end) as [Calculated_PalletNumber],
((case when (ShipHead.ShipDate ) is null
then ‘Y’
else
(case when ShipHead.ShipDate >= dateadd(mm,-2, cast(getdate() as date)) then ‘Y’ else ‘N’ end)
end)) as [Calculated_ShipFilter]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
And
OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
And
OrderDtl.OrderNum = OrderRel.OrderNum
And
OrderDtl.OrderLine = OrderRel.OrderLine
left outer join Erp.ShipTo as ShipTo on
OrderRel.Company = ShipTo.Company
And
OrderRel.ShipToCustNum = ShipTo.CustNum
And
OrderRel.ShipToNum = ShipTo.ShipToNum
left outer join Erp.ShipDtl as ShipDtl on
OrderRel.Company = ShipDtl.Company
And
OrderRel.OrderNum = ShipDtl.OrderNum
And
OrderRel.OrderLine = ShipDtl.OrderLine
And
OrderRel.OrderRelNum = ShipDtl.OrderRelNum
left outer join Erp.ShipHead as ShipHead on
ShipDtl.Company = ShipHead.Company
And
ShipDtl.PackNum = ShipHead.PackNum
Where (ShipHead.ShipDate >= dateadd(DAY,-60, cast(getdate() as date)) or ShipHead.ShipDate is null)
It might be a good idea to rebuild the BAQ. Get the shiphead criteria working then add the other tables. The old BAQ has a funky join that might be causing some problems.
I didn’t have the null check in the Shiphead criteria then started down a rabbit hole. I was making it more complex than I needed too.
Though, I’d still like to learn sub-queries better in BAQs someday.
Thanks everyone!
Hi Randy,
I think there is provision for date criteria i.e before or after from Today’s date constant.
You can create criteria for shipHead,shipdate > 30 days, to display those shipments, which are shipped in past 30 days only.
I have added this criteria by selecting following option in phrase builder and it works very well.
After that, enter your days value, if it is before today’s date, enter -ve value.
Ultimately, ur dependent table will show the results.
