I mentioned that I would try to get back in here with some more examples using SQL Server's Aggregate functions. I think this is a good one. One of the requests I used to get again and again, was the current net position for all symbols traded on the Equity exchanges. The Ops desk (the Team supporting the traders) needed to know if positions were open for any symbol across these exchanges: ISLD, BRUT, ARCA, CHX, NSDQ, NYSE.
To handle that, I just created this procedure which was run on the back-end, and would alert them if an open position was found.
AS
SET NOCOUNT ON
/*
Returns the net position by symbol, across the equity exchanges, if exists.
EXEC dbo.usp_NetPosBySymbol
Auth: Me
Date: 10/13/2014
*/
BEGIN
-- use this method to avoid an IN list in the WHERE clause
DECLARE @Exchanges TABLE (ExchangeName VARCHAR(15))
INSERT @Exchanges (ExchangeName)
VALUES
('ARCA'),
('BRUT'),
('CHX'),
('DNET'),
('ISLD'),
('NSDQ'),
('NYSE');
IF EXISTS(
SELECT buys-sells AS Difference
FROM (
SELECT Symbol,SUM(Quantity) [buys]
FROM dbo.Orders o
WHERE Side = 'B'
AND EXISTS(
SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) buys JOIN
(
SELECT Symbol,SUM(Quantity) [sells]
FROM dbo.Orders o
WHERE Side = 'S'
AND EXISTS(
SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) sells
ON buys.Symbol = sells.Symbol
WHERE
buys <> sells)
BEGIN
SELECT buys.Symbol,Buys,Sells,buys-sells AS Difference
FROM(
SELECT Symbol,SUM(Quantity) [buys]
FROM dbo.Orders o
WHERE Side = 'B'
AND EXISTS(
SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) buys JOIN
(
SELECT Symbol,SUM(Quantity) [sells]
FROM dbo.Orders o
WHERE Side = 'S'
AND EXISTS(
SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) sells
ON buys.Symbol = sells.Symbol
WHERE
buys <> sells
ORDER BY
buys.Symbol
END
END
SET NOCOUNT OFF
GO
We're still using the Orders data that we wrote in this post, SQL Aggregate functions, and these are our results:
Symbol Buys Sells Difference
BEAS 55 100 -45
YHOO 50 105 -55
That is just a simple example using SUM. We first do an existence check, and then we return the net position back to the front end, if exists.
See this for more information, and for a couple more examples using the SUM function:
http://msdn.microsoft.com/en-us/library/ms187810.aspx