Backorders Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Backorders Query

Author

Topic

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 11:36:03

Hi all - first post so here it goes! Hope someone can help!! :)

Am struggling with a query - I guess that it will be a nested query of some sort?

It's an stock ordering and despatch db. Sometimes some items are despatched on an order but some are outstanding. I need a query to work out which orders are still outstanding - this is a basic breakdown of tables involved:

Order
PK OrderID

Orderline
PK OrderLineID
FK ProductID
Qty

Products
PK ProductID

Despatch
PK DespatchID
FK OrderID

DespatchLine
PK DespatchLineID
FK DespatchID
FK ProductID
Qty

I guess it's a process of comparing the qty ordered of each productid to the qty despatched (if any) but am struggling to do this in a single query..

I can do it via c# and create a dataset and loop through each order to see if all product qtys match despatched qtys but obviously the amount of load on the sql server would be too high so really need a query which would do this for me

Any ideas?

Thanks in advance :)

Steve

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 12:19:14

How do you connection ORDERLINE table with ORDER table?
Is there a FK_OrderID in ORDERLINE table?

SELECT OrderID, ProductID, SUM(Ordered) AS Ordered, SUM(Despatched) AS Despatched FROM (
SELECT FK_OrderID AS OrderID, FK_ProductID AS ProductID, SUM(Qty) AS Ordered, 0 AS Despatched FROM OrderLine GROUP BY FK_OrderID, FK_ProductID
UNION ALL
SELECT d.FK_OrderID, dl.FK_ProductID, 0, SUM(dl.Qty) FROM Despatch AS d LEFT JOIN DespatchLine AS dl ON dl.FK_DespatchID = d.PK_DespatchID GROUP BY d.FK_OrderID, dl.FK_ProductID
) AS d
GROUP BY OrderID, ProductID

E 12°55'05.25"
N 56°04'39.16"

EDIT: Clarification of GROUPING

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 12:25:20

Sorry I meant to add the field orderid into the table orderline!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 12:57:41

Great. Did you try my suggestion?

E 12°55'05.25"
N 56°04'39.16"

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 13:31:45

Hey :) I tried your suggestion but sadly it didn't work. This is the SQL:

SELECT OrderID, ProductID, Ordered, Despatched FROM (
SELECT OrderID AS OrderID, ProductID AS ProductID, SUM(Qty) AS Ordered, 0 AS Despatched FROM OrderLine GROUP BY OrderID, ProductID
UNION ALL
SELECT d.OrderID, dl.ProductID, 0, SUM(dl.Qty) FROM Despatch AS d LEFT JOIN DespatchLine AS dl ON dl.DespatchID = d.DespatchID GROUP BY d.OrderID, dl.ProductID
) AS d

GROUP BY OrderID, ProductID, Ordered, d.Despatched

It produces a strange result:
This is an example of a part despatched order (57)

Order ID Product ID Ordered Despatched
57 44 0 1
57 44 1 0
57 50 0 70
57 50 100 0

Is there any way I can just produce a list of orders which still have items outstanding?

Steve.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 13:39:53

That result is not possible form the query posted above.
Remove the Ordered and d.Despatched from the GROUP BY! You introduced a bug that was not in my original query.

SELECT OrderID, ProductID, SUM(Ordered) AS Ordered, SUM(Despatched) AS Despatched
FROM (
SELECT OrderID, ProductID, Qty AS Ordered, 0 AS Despatched FROM OrderLine
UNION ALL
SELECT d.OrderID, dl.ProductID, 0, dl.Qty FROM Despatch AS d LEFT JOIN DespatchLine AS dl ON dl.DespatchID = d.DespatchID
) AS d
GROUP BY OrderID, ProductID
HAVING SUM(Ordered) <> SUM(Despatched) -- This is the new filtering part

E 12°55'05.25"
N 56°04'39.16"

EDIT: Some optimization

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 13:53:42

Thanks Peso that's fantastic! Am ok with basic queries but still learning...

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 14:02:57

Peso just one more thing (hope I'm not being a pest!)

I have a list now like this:

OrderID / Product ID / Ordered / Despatched
56 50 100 0
57 50 100 70
59 43 10 0
59 50 10 0
60 45 100 0

Is there an easy way in SQL I could have a distinct list and also identify normal orders and backorders? So producing the following:

orderid backorder
56 0
57 0
59 1
60 0

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 14:16:23

Your expected output does not seem to match the sample data.
Is this a typo?
Please try to explain what makes an order a backorder.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 14:18:35

To make the sample data match the expected output above, use

SELECT OrderID, CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END AS Backorder
FROM (
SELECT OrderID, Qty AS Ordered, 0 AS Despatched FROM OrderLine
UNION ALL
SELECT d.OrderID, 0, dl.Qty FROM Despatch AS d LEFT JOIN DespatchLine AS dl ON dl.DespatchID = d.DespatchID
) AS d
GROUP BY OrderID
ORDER BY OrderID

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 14:19:36

Maybe you need something like this?

SELECT OrderID, CASE WHEN SUM(Ordered) <> SUM(Despatched) THEN 1 ELSE 0 END AS Backorder
FROM (
SELECT OrderID, Qty AS Ordered, 0 AS Despatched FROM OrderLine
UNION ALL
SELECT d.OrderID, 0, dl.Qty FROM Despatch AS d LEFT JOIN DespatchLine AS dl ON dl.DespatchID = d.DespatchID
) AS d
GROUP BY OrderID
ORDER BY OrderID

E 12°55'05.25"
N 56°04'39.16"

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 14:30:26

Hi Peso - a backorder is an order which has had something despatched from it but is not completely despatched.

I don't want to list any orders which have been completely despatched - the last 2 queries seem to include these.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 14:33:16

In that case, shouldn't Order 57 be backordered in the sample data above?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 14:34:37

SELECT OrderID, CASE WHEN SUM(Despatched) > 0 THEN 1 ELSE 0 END AS Backorder
FROM (
SELECT OrderID, Qty AS Ordered, 0 AS Despatched FROM OrderLine
UNION ALL
SELECT d.OrderID, 0, dl.Qty FROM Despatch AS d LEFT JOIN DespatchLine AS dl ON dl.DespatchID = d.DespatchID
) AS d
GROUP BY OrderID
HAVING SUM(Ordered) <> SUM(Despatched)
ORDER BY OrderID

E 12°55'05.25"
N 56°04'39.16"

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 14:38:13

Hi peso - this is working - it's marking the orders which have been partly despatched correctly as a backorder :)

Only thing is is that it's also listing orders which have been completely despatched (where ordered qty = despatched qty)

Once again thanks for the help :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 14:41:04

See my edited response.
I have added a HAVING part.

E 12°55'05.25"
N 56°04'39.16"

stevecurrey
Starting Member

9 Posts

Posted - 2007-08-20 : 14:42:26

Absoloutely perfect!!
Thanks very much - you've been very patient ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources