I’m working on a BAQ to help manage transfers between our warehouses. In my first iteration of the BAQ, my top level query (Warehouse Demand) finds all parts in a specific warehouse and filters any where the demand is less than the on-hand quantity. The top level query is joined to a inner subquery (Transfer From). The (Transfer From) subquery looks in all parts bins in the other warehouses to find which bins the needed part can be pulled from.
The combined query return has the columns Demand Warehouse, Part, Part Description, Warehouse total demand, Demand Warehouse Qty on Hand, Transfer Quantity, Transfer Warehouse, Transfer Warehouse Bin, Transfer Warehoulse Bin On Hand Quantity. I’m getting the data that I need, but I’m concerned that someone may get confused by the Demand warehouse information being duplicated for each bin when a part is available in more than 1 bin at the transfer warehouse(s).
Is it possible to inhibit the repeated information? It seems like this might be a use for a CTE subquery, but all the examples that I’ve seen for a CTE are recursive on the same table. Suggestions?
Thanks