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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.