November 22, 2011 at 8:39 am
I am currently working with trade data to try and produce an output which is a summary report of data contained in the table.
Essentially i would like to produce an output that shows the YTD gross amount, YTD commision amount and the ytd sum of orders.
I have included some sample data
CREATE TABLE #TradeDetail
(
DataSourceName varchar(12)
,CorporateRegion char(3)
,TradeDate datetime
,TradeOrderNumber 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
SELECT 'CRTS','EUR','20110701','1323562556','BARCLAYS', 'E','Equity','Common Stock','100','100'UNION ALL
SELECT 'CRTS','EUR','20110801','1323562557','BARCLAYS', 'E','Equity','Common Stock','150','100'UNION ALL
SELECT 'CRTS','EUR','20110901','1323562558','BARCLAYS', 'E','Equity','Common Stock','150','100'UNION ALL
SELECT 'CRTS','EUR','20110701','1323562559','BARCLAYS', 'E','Equity','Common Stock','100','200'UNION ALL
SELECT 'CRTS','EUR','20110701','1323562560','JP MORGAN', 'E','Equity','Common Stock','50','100' UNION ALL
SELECT 'CRTS','EUR','20110801','1323562561','JP MORGAN', 'E','Equity','Common Stock','50','200'UNION ALL
SELECT 'CRTS','EUR','20110901','1323562562','JP MORGAN', 'E','Equity','Common Stock','100','200'UNION ALL
SELECT 'CRTS','EUR','20110701','1323562563','KEMPEN', 'E','Equity','Common Stock','25','50'UNION ALL
SELECT 'CRTS','EUR','20110801','1323562564','KEMPEN', 'E','Equity','Common Stock','25','50'UNION ALL
SELECT 'CRTS','EUR','20110901','1323562565','KEMPEN', 'E','Equity','Common Stock','25','50'
The output header colums should be should be as follows:
DataSourceName
CorporateRegion
TradeDate
TradeOrderNumber
sBroker
AssetClassCode
AssetClassName
InstrumentType
GrossAmount_EUR_Jan
GrossAmount_EUR_Feb
GrossAmount_EUR_Mar
GrossAmount_EUR_Apr
GrossAmount_EUR_May
GrossAmount_EUR_Jun
GrossAmount_EUR_Jul
GrossAmount_EUR_Aug
GrossAmount_EUR_Sep
GrossAmount_EUR_Oct
GrossAmount_EUR_Nov
GrossAmount_EUR_Dec
GrossAmount_EUR_YTD YTD sum of gross amount for whole year
Commission_EUR_Jan
Commission_EUR_Feb
Commission_EUR_Mar
Commission_EUR_Apr
Commission_EUR_May
Commission_EUR_Jun
Commission_EUR_Jul
Commission_EUR_Aug
Commission_EUR_Sep
Commission_EUR_Oct
Commission_EUR_Nov
Commission_EUR_Dec
Commission_EUR_YTD YTD sum of commision for whole year
OrdersJan sum of TradeOrderNumbers in jan
OrdersFeb sum of TradeOrderNumbers in feb
OrdersMar sum of TradeOrderNumbers in mar
OrdersApr sum of TradeOrderNumbers in apr
OrdersMay sum of TradeOrderNumbers in may
OrdersJun sum of TradeOrderNumbers in jun
OrdersJul sum of TradeOrderNumbers in jul
OrdersAug sum of TradeOrderNumbers in aug
OrdersSep sum of TradeOrderNumbers in sep
OrdersAug sum of TradeOrderNumbers in aug
OrdersOct sum of TradeOrderNumbers in oct
OrdersNov sum of TradeOrderNumbers in nov
OrdersDec sum of TradeOrderNumbers in Dec
Orders_EUR_YTD
The sample data i have provided only contains a few months of data but in reality there will be 2 years of data. Any ideas on where I should start would be helpful
November 22, 2011 at 9:09 am
Hi there..
I'd like a better solution for doing this as well, because I am often called upon to query data and return it in this format..
The below is a start for your data, however I'm not exactly sure what you want doing with the TradeOrderNumber? I'm not sure how useful a sum of these numbers would actually be. You'd have to convert them to an integer CONVERT(INTEGER,TradeOrderNumber) before summing if you just wanted a sum of the numbers.
SELECTDataSourceName,
CorporateRegion,
sBroker,
AssetClassCode,
AssetClassName,
InstrumentType,
Yr,
MAX(GrossAmount_EUR_Jul) AS GrossAmount_EUR_Jul,
MAX(GrossAmount_EUR_Aug) AS GrossAmount_EUR_Aug,
MAX(GrossAmount_EUR_Sep) AS GrossAmount_EUR_Sep,
SUM(GrossAmount_EUR_YTD) AS GrossAmount_EUR_YTD,
MAX(Commission_EUR_Jul) AS Commission_EUR_Jul,
MAX(Commission_EUR_Aug) AS Commission_EUR_Aug,
MAX(Commission_EUR_Sep) AS Commission_EUR_Sep,
SUM(Commission_EUR_YTD) AS Commission_EUR_YTD
FROM
(SELECT DataSourceName,
CorporateRegion,
sBroker,
AssetClassCode,
AssetClassName,
InstrumentType,
YEAR(TradeDate) AS Yr,
SUM(CASE WHEN MONTH(TradeDate) = 7 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDate) = 8 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDate) = 9 THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(GrossAmount_EUR) AS GrossAmount_EUR_YTD,
SUM(CASE WHEN MONTH(TradeDate) = 7 THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDate) = 8 THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDate) = 9 THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(Commission_EUR) AS Commission_EUR_YTD
FROM #TradeDetail
GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode,AssetClassName,InstrumentType, YEAR(TradeDate)
) rawData
GROUP BY DataSourceName,CorporateRegion,sBroker,AssetClassCode,AssetClassName,InstrumentType, Yr
[/Code]
Hope this helps, and I also hope someone can improve it!
Cheers,
Jim.
November 22, 2011 at 9:22 am
The first step is to choose the correct tool for the job. In this case, the correct tool is reporting software, such as SSRS, not T-SQL. This kind of stuff is easy in a reporting tool, because it was designed to do it; this kind of stuff is hard in T-SQL, because it wasn't.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 22, 2011 at 9:22 am
hey,
much appreciated. i will workd this and see how it goes. It actaully meant to be a row count for trade orders.
November 22, 2011 at 9:29 am
November 23, 2011 at 8:43 am
Thanks for the solution. That worked. I just wanted know what i need to do to get the total orders for a year from todays today date. i.e the sum of orders with a year based on todas date.
November 23, 2011 at 9:10 am
November 23, 2011 at 9:34 am
Managed to solve it with
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@MyToMonth))
THEN TotalGross END) AS PreviousYear1Turnover,
Thanks once again for the initial help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply