Saturday, September 27, 2014

Calculate a SUM across two tables

One of my customers called me in a panic last night.  They are a prop trader based in Chicago, and the bulk of their data is the trades that they execute throughout the day. Well, Friday was a pretty crazy day in the market -- stocks rose very high after the government's 2nd quarter growth estimate. Unfortunately, their systems queued up crazy, and they had to do a lot of manual adjustments post trade.  Not uncommon, and also not very fun.  

Anyway, because they struggled to handle the data-flow, they had to split their traffic into two target tables.  This was good intraday, because they got to trade all the way to the close.  At the end of the day, however, they needed one volume count -- not two.

That's all this is.  I've used their problem as an example.  This is just quick means to calculate a SUM across two tables. 

   -- Trader table, so we can translate the trdID
   CREATE TABLE dbo.Trader (
     trdID INT IDENTITY (1,1),
     trdName VARCHAR(25),
     trdDesk VARCHAR(25)
   INSERT dbo.Trader (trdID,trdName,trdDesk)
   SELECT 1, 'John Doe','Equities'
   SELECT 12, 'Edgar Smith','Forex'
   SELECT 207, 'Jane Peters','Fixed Income'
   SELECT 208, 'Jack Black','Futures'

   -- Their 1st trade table
   CREATE TABLE #table1 (trdDate DATE,trdID INT,Volume BIGINT)
   INSERT #table1 (trdDate,trdID,Volume)
   SELECT '9/26/2014',12,18005
   SELECT '9/26/2014',1,100
   SELECT '9/26/2014',207,12500
   SELECT '9/26/2014',208,750

   -- Their 2nd trade table
   CREATE TABLE #table2 (trdDate DATE,trdID INT,Volume BIGINT)
   INSERT #table2 (trdDate,trdID,Volume)
   SELECT '9/26/2014',12,100
   SELECT '9/26/2014',1,5500
   SELECT '9/26/2014',207,1000
   SELECT '9/26/2014',208,0

-- See data from both tables
SELECT * FROM #table1
SELECT * FROM #table2

      -- results
   trdDate        trdID    Volume
   2014-09-26 1 100
   2014-09-26 12 18005
   2014-09-26 207     12500
   2014-09-26 208 750

   trdDate        trdID    Volume
   2014-09-26 1 5500
   2014-09-26 12         100
   2014-09-26 207 1000
   2014-09-26 208 0

   -- Now we'll pull it all back out in one result-set, with the volumes summed
   SELECT trdDate,Trader,SUM(Volume) [Vol Traded]
       SELECT t1.trdDate,t2.trdName [Trader],t1.Volume
       FROM #table1 t1 INNER JOIN dbo.Trader t2
         ON t1.trdID = t2.trdID
       UNION ALL
       SELECT t1.trdDate,t2.trdName [Trader],t1.Volume
       FROM #table2 t1 INNER JOIN dbo.Trader t2
     ON t1.trdID = t2.trdID
   ) t
   GROUP BY trdDate,Trader
   ORDER BY trdDate,Trader

Our final result-set, volumes from both tables were summed into a single 'Vol Traded' --

   trdDate         Trader          Vol Traded
   2014-09-26    Edgar Smith        18105
   2014-09-26    Jack Black           750
   2014-09-26    Jane Peters        13500
   2014-09-26    John Doe            5600

No comments:

Post a Comment