Linked Queries Issue
-
Please someone help or give me ideas how to do this since I have ever seen this problem before and have no clue how to handle it. I was told that I was not allowed to change the structure of the current system, but I was allowed to add new tables to hold data if they will help me to solve the problem. I'm in a big puzzle now. I have 2 queries that linkes tables in over the places to return data like this:
Query1:
Location Product Unshipped_Qty
SJ P1 500
SC P2 1000
Query2:
Location Product TotalQTYAvailable WorkShop
SJ P1 650 Work1
SJ P1 1000 Work2
SC P2 500 Work1
SC P2 850 Work2
The task is to find new TotalQtyAvailable by substracting the Unshipped_QTY from TotalQTYAvailable and making sure that the TotalQTYAvailabe has to be always deducted from WorkShop #1 before WorkShop#2 is used if the product has the same location. The result that I'd like to have will be like this:
Final Query:
Location Product TotalQTYAvailable Workshop
SJ P1 150 Work1
SJ P1 1000 Work2
SC P2 0 Work1
SC P2 350 Work2
The result of this final query can be stored in a table so that system will have to use data in this table to do new calculation and also update the data in this table so that it can continue being used the next time if these queries are run again. I really don't know how to link my 2 queries to find the Final Query and really appreciate if someone can help me achieve this. Thanks in advance.
PS. My query1 is always changing, but my query2 will not change constantly. Everytime, it runs, it will always show total available qty. This cause a problem for order entry persons because they don't know what exactly quantity is available so that they can't promise to customers. Therefore, I need to work out this problem to show correct available quantity for them.
Minh
-
paste the query and schema, we are not understanding anything what u r saying,
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
You probably need to use a derived table as a nested query, eg :
SELECT t1.KeyID, SUM(t1.TotalQuantity) - SUM(dt.Unshipped)
FROM Table1 t1
JOIN (SELECT KeyID, TotalQuantity AS Unshipped
FROM Table1
WHERE Unshipped = 1) dt
ON dt.KeyID = t1.KeyID
GROUP BY t1.KeyID
The inner query gives the quantities for unshipped records by KeyID. The outer query totals all quantities and substracts the unshipped amounts from the inner query.
The outer query
-
If there are only two workshops then
SELECT q2.Location, q2.Product, q2.Workshop,
(CASE WHEN q2.WorkShop = 'Work1'
THEN (CASE WHEN q1.Unshipped_Qty > q2.TotalQTYAvailable
THEN 0 ELSE q2.TotalQTYAvailable - q1.Unshipped_Qty END)
ELSE (CASE WHEN q1.Unshipped_Qty > w1.TotalQTYAvailable
THEN q2.TotalQTYAvailable - (q1.Unshipped_Qty - w1.TotalQTYAvailable)
ELSE q2.TotalQTYAvailable
END)
END) AS [TotalQTYAvailable]
FROM #Query2 q2
INNER JOIN #Query1 q1
ON q1.Location = q2.Location
AND q1.Product = q2.Product
INNER JOIN #Query2 w1
ON w1.Location = q2.Location
AND w1.Product = q2.Product
AND w1.WorkShop = 'Work1'
However I bet thats not the case
Far away is close at hand in the images of elsewhere.
Anon. -
Hi All,
Believe it or not David Burrows's way gave me exactly what I'm looking for (Thank you so much!). I tested couple time and it worked very well for me. However, this way only works if I have only 2 workshops. What if they will add more workshops in the future? This query will no longer work. If anyone has a way to handle multiple workshops at the same time, please share it with me. In the meantime, I try to apply the same concept to see if I can work it out or not. Again, thank you so much, David.
Minh
-
David's answer was right on the money for two workshops. Here's a query that works for any number of workshops:
SELECT base.location, base.product
base.TotalQTYAvailable - ship.ship_qty AS TotalQTYAvailable,
base.workshop
FROM #Query2 AS base,
(
-- Subquery "ship": calculates allocated ship_qty by workshop:
SELECT ebase.location, ebase.product, ebase.workshop,
(CASE
WHEN ISNULL(unsh.Unshipped_Qty,0) THEN 0
WHEN ebase.other_available > unsh.Unshipped_Qty
THEN 0
WHEN (ebase.this_available + ebase.other_available) > unship.Unshipped_Qty
THEN unsh.Unshipped_Qty - ebase.other_available
ELSE ebase.this_available
) AS ship_qty
FROM
(
-- Sub-subquery "ebase": calculates cumulative availability by workshop:
SELECT base.location, base.product, base.workshop,
base.TotalQTYAvailable AS this_available,
SUM(CASE WHEN base.workshop > other.workshop
THEN other.TotalQTYAvailable
ELSE 0
) AS other_available
FROM #Query2 AS base,
#Query2 AS other
WHERE base.location = other.location
AND base.product = other.product
GROUP BY base.location, base.product, base.workshop, base.TotalQTYAvailable
) AS ebase,
#Query1 AS unsh
-- Note: Left join to handle products with no Unshipped_Qty entries
WHERE ebase.location *= unsh.location
AND ebase.product *= ebase.product
) AS ship
WHERE base.location = ship.location
AND base.product = ship.product
AND base.workshop = ship.workshop
Regards,Bob Monahon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply