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