Post reply

YTD Summary report

  • November 22, 2011 at 8:39 am

    #250992

    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

    #1411622

    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.

    SELECT DataSourceName,

    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.

    SQL SERVER Central Forum Etiquette[/url]

  • November 22, 2011 at 9:22 am

    #1411630

    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

    #1411631

    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

    #1411637

    Ahhh OK,

    Try:

    SUM(CASE WHEN MONTH(TradeDate) = 7 THEN 1 END) AS TradeOrderNumber_Jul

    Should work because you're already grouping by month and customer etc.

    Cheers,

    Jim.

    SQL SERVER Central Forum Etiquette[/url]

  • November 23, 2011 at 8:43 am

    #1412262

    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

    #1412293

    No problems.

    drew.allen is right in that SSRS or Excel would be better at presenting this info for you, however sometimes we're limited pragmatically to doing things the hard way.. haha

    Cheers,

    Jim.

    SQL SERVER Central Forum Etiquette[/url]

  • November 23, 2011 at 9:34 am

    #1412314

    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