Aggregate Issue

• eseosaoregie

  SSCertifiable

  Points: 6889

  More actions

  December 2, 2011 at 10:56 am

  #247193

  I have some base data which I will use to creat a cross tab report. The data consists ot trade transactions. My issue is that a trade consists of transaction number and trade order number. for every tradenumber can consist of one or more tradetransaction numbers. In the final cross tab the data must summarize the number of transactions in a given period and the number of orders in a given period.

  The allocation numbers are unique and thus represent a row in the data. I have provided some sample data

  CREATE TABLE #TradeDetail

  (

  DataSourceName varchar(12)

  , CorporateRegion char(3)

  , TradeDate datetime

  , TradeOrderNumber varchar(50)

  , TradeTransactionNumber varchar(50)

  , sBroker varchar(120)

  , AssetClassCode char(1)

  , AssetClassName varchar(120)

  , InstrumentType varchar(120)

  , GrossAmount_EUR numeric(18,6)

  , Commission_EUR numeric(18,6)

  )

  INSERT INTO #TradeDetail

  --drop table #TradeDetail

  SELECT 'CRTS','EUR','20110701','1423562556','556533342','BARCLAYS', 'E','Equity','Common Stock','120','130' UNION ALL

  SELECT 'CRTS','EUR','20110701','1423562556','556633342','BARCLAYS', 'E','Equity','Common Stock','100','130' UNION ALL

  SELECT 'CRTS','EUR','20110701','1423562556','556733322','BARCLAYS', 'E','Equity','Common Stock','120','100' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562556','555533342','BARCLAYS', 'E','Equity','Common Stock','100','130' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562556','555533346','BARCLAYS', 'E','Equity','Common Stock','100','150' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562557','655533346','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562557','655533347','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562557','655533348','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562557','655533349','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562557','655533350','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110901','1323562558','665533341','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110901','1323562558','665533342','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110901','1323562558','665533343','BARCLAYS', 'E','Equity','Common Stock','150','100' UNION ALL

  SELECT 'CRTS','EUR','20110901','1323562558','665533344','BARCLAYS', 'E','Equity','Common Stock','150','143' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562559','667733544','BARCLAYS', 'E','Equity','Common Stock','105','200' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562559','667733345','BARCLAYS', 'E','Equity','Common Stock','100','200' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562559','667733346','BARCLAYS', 'E','Equity','Common Stock','100','220' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562559','667733347','BARCLAYS', 'E','Equity','Common Stock','100','200' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562560','697733348','JP MORGAN', 'E','Equity','Common Stock','50','100' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562560','697733349','JP MORGAN', 'E','Equity','Common Stock','50','100' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562560','697733350','JP MORGAN', 'E','Equity','Common Stock','50','100' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562560','697733351','JP MORGAN', 'E','Equity','Common Stock','50','120' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562560','697733352','JP MORGAN', 'E','Equity','Common Stock','80','100' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562560','697733353','JP MORGAN', 'E','Equity','Common Stock','50','170' UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562560','697733354','JP MORGAN', 'E','Equity','Common Stock','50','100' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562561','700033360','JP MORGAN', 'E','Equity','Common Stock','50','200' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562561','700033361','JP MORGAN', 'E','Equity','Common Stock','90','200' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562561','700033362','JP MORGAN', 'E','Equity','Common Stock','120','200' UNION ALL

  SELECT 'CRTS','EUR','20110801','1323562561','700033363','JP MORGAN', 'E','Equity','Common Stock','50','220' UNION ALL

  SELECT 'CRTS','EUR','20110901','1323562562','790033364','JP MORGAN', 'E','Equity','Common Stock','100','200'UNION ALL

  SELECT 'CRTS','EUR','20110901','1323562562','790033365','JP MORGAN', 'E','Equity','Common Stock','100','200'UNION ALL

  SELECT 'CRTS','EUR','20110701','1323562563','790033368','KEMPEN', 'E','Equity','Common Stock','25','40' UNION ALL

  SELECT 'CRTS','EUR','20091201','1323562564','790033369','KEMPEN', 'E','Equity','Common Stock','25','50' UNION ALL

  SELECT 'CRTS','EUR','20101201','1323562562','790033370','JP MORGAN', 'E','Equity','Common Stock','500','250'

  I would like to have an output that shows the number of transactions, Number of trade orders plus all the other detail fields.

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  December 2, 2011 at 1:07 pm

  #1416169

  It also helps to include expected results based on your sample data and what you have already tried. This looks fairly straightforward to me, so I must be misunderstanding what you are asking which is where the expected results and your attempts will help.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA