missing orders alert

A basic orders table with this definition:

OrderID   unique IDENTITY
OrderTime
Exchange
Account
Symbol
Quantity
Price
OrderNumber

We have 'basket' orders, where a large order with total quantity is written once into the table.  The basket order is traded by multiple users, and each of their order quantities together, will equate to the quantity of the first basket order.  Here are two examples:


orderid     ordertime                           exchange   account    symbol    quantity       price        ordernumber
3573310      2011-12-02 14:02:54.000      1      337      1289      10      2340.00      99889987
3573311      2011-12-02 14:02:54.000      1      337      1289      5      2340.00      99889987
3573312      2011-12-02 14:02:54.000      1      337      1289      5      2340.00      99889987
3573305      2011-12-02 12:28:03.000      13      544      5324      20      2340.00      99889988
3573306      2011-12-02 12:28:03.000      13      544      5324      5      2340.00      99889988
3573307      2011-12-02 12:28:03.000      13      544      5324      5      2340.00      99889988
3573308      2011-12-02 12:28:03.000      13      544      5324      5      2340.00      99889988
3573308      2011-12-02 12:28:03.000      13      544      5324      5      2340.00      99889988



In the above you can see two basket orders, 99889987 and 99889988.  The larger quantity order is the 'basket', the smaller quantity are the children.
OrderNumber 99889987 has a quantity of 10, it has two children, each with a quantity of 5, to total the 10 quantity in the basket order.
OrderNumber 99889988 has a quantity of 20, it has four children, each with a quantity of 5, to total the 20 quantity in the basket order.

That is a very basic example of basket trading, and I need to identify any orders that aren't lined up.  Basically, if there is a basket order that does not have it's full quantity accounted for in X number of children, I need to report it.  Then so and so can go figure out where the missing children orders are.

I know that the total order quantity is referenced in the basket order.
I know that there will be 2 or more children -- smaller quantity orders -- that will total to the amount of the basket trade.  
I do not know how many children there will be, but the OrderNumber for the basket and it's children will always be the same.


I'll return an order count, and volume for any exchange/symbol that is not lined up, and let them take it from there.  

My query is about that collection -- say my dataset is this:

orderid     ordertime                           exchange   account    symbol    quantity       price        ordernumber
3573310      2011-12-02 14:02:54.000      1      337      1289      10      2340.00      99889987
3573311      2011-12-02 14:02:54.000      1      337      1289      5      2340.00      99889987
3573312      2011-12-02 14:02:54.000      1      337      1289      5      2340.00      99889987
3573305      2011-12-02 12:28:03.000      13      544      5324      20      2340.00      99889988
3573306      2011-12-02 12:28:03.000      13      544      5324      5      2340.00      99889988
3573308      2011-12-02 12:28:03.000      13      544      5324      5      2340.00      99889988


You can see that second basket order (99889988) no longer has all of its children, and the quantity is ten short of the total, correct quantity.  

What would be the best way to construct this check?
LVL 18
dbaSQLAsked:
Who is Participating?
 
tim_csCommented:
Left out my group by on the SUM()
;WITH CTE AS (
SELECT
	OrderNumber
	,Quantity
	,ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY Quantity DESC) RN
FROM
	YourTable ),
CTE2 AS (
SELECT	
	OrderNumber
	SUM(Quantity) SumQuantity
FROM
	CTE
WHERE
	RN <> 1
GROUP BY
	OrderNumber
)

SELECT
	*
FROM
	CTE a
	INNER JOIN CTE2 b
		ON a.OrderNumber = b.OrderNumber
WHERE
	a.RN = 1
	AND a.Quantity <> b.SumQuantity

Open in new window

0
 
dbaSQLAuthor Commented:
Any thoughts?
0
 
tim_csCommented:
I would prefer to have a different table for the parent and child records or a columns that lets you know which record is the parent.  If that's not possible then something like this could work.
;WITH CTE AS (
SELECT
	OrderNumber
	,Quantity
	,ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY Quantity DESC) RN
FROM
	YourTable ),
CTE2 AS (
SELECT	
	OrderNumber
	SUM(Quantity) SumQuantity
FROM
	CTE
WHERE
	RN <> 1
)

SELECT
	*
FROM
	CTE a
	INNER JOIN CTE2 b
		ON a.OrderNumber = b.OrderNumber
WHERE
	a.RN = 1
	AND a.Quantity <> b.SumQuantity

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tim_csCommented:
Change the last part to this if there is a chance there won't be any child records.
SELECT
	*
FROM
	CTE a
	LEFT JOIN CTE2 b
		ON a.OrderNumber = b.OrderNumber
WHERE
	a.RN = 1
	AND (a.Quantity <> b.SumQuantity OR b.SumQuantity IS NULL)

Open in new window

0
 
dbaSQLAuthor Commented:
I should note, we will only always be looking at current day (under normal conditions), and the orders bucket order type is 7.  both of these things can help us refine the targeted dataset.
0
 
dbaSQLAuthor Commented:
oh yes.  having put in those two clauses, the dataset returns instantaneously.

ordernumber            quantity         rn          ordernumber           sumquantity
99889988      20.00000000      1      99889988      15.00000000
998899123      100.00000000      1      998899123      50.00000000


sure, it's only a very small dataset i am testing right now.  but i think this may be good, tim_cs.  now i just need to make an output to the front end, based on this occurrence.

yes.  i think this will work.

thank you
0
 
dbaSQLAuthor Commented:
very nice.  thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.