December 5th, 2003, 09:55 AM
I've got a bit of an issue creating a query that has any elegance at all (i.e. done right by someone who actually knows this stuff) due simply to my knowledge limitations.
In a single query I need to accomplish the following:
Table_A contains Item_NO and UPC_NO
Table_B contains Item_NO and Warehouse_ID and Quantity_Available and Quantity_Allocated
Result set needs to be:
UPC_NO, InStock_Flag, Quantity
The problem is that the 'InStock_Flag' is a new requirement (I have a query already that simply joins the tables on Item_NO and groups by UPC_NO).
The Flag is simply 'T' or 'F' depending upon whether the quantity value for a particular UPC_NO is positive or negative. I've accomplished that as well utilizing a CASE statement. However, what I neglected to take into account is that the UPC_NO shows up multiple times in Table_B (once for each warehouse) and as my CASE statement is looping through the records the flag is getting set to 'T' for some records and 'F' for others, thus my grouping will no longer work as those records become unique.
The logic needs to be as follows for the query, but I'm not sure how to structure it:
First join tables, select all records and group Quantity_Available and Quantity_Allocated by UPC_NO where Warehouse_ID <> 'x'
Then take that result set and generate final result as UPC_NO, InStock_Flag, Quantity (Quantity_Available - Quantity_Allocated)
Where Quantity_Available - Quantity_Allocated > 0 then InStock_Flag = 'T'
Where Quantity_Available - Quantity_Allocated < 1 then InStock_Flag ='F' and Quantity = 0
Again, I have a query that does all of this except for the first part...and not sure if this should be done as a subquery (which frankly I have no experience with) or other.
Any help much appreciated.
December 11th, 2003, 04:18 PM
case when sum(Quantity_Available - Quantity_Allocated) > 0 then 'T' else 'F' end,
case when sum(Quantity_Available - Quantity_Allocated) > 0 then quantity else 0 end
from table_a a
join table_b b
on a.item_no = b.item_no
where where Warehouse_ID <> 'x'
group by upc_no