Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    Wink Unanswered: SQL Query using Lag function

    OK -- this has been driving me MAD

    Here is my query

    The data returns like this

    Time SYmbol Buy/Sell Quantity Price Calculation

    11:05:43 AMX B 37050 65.5 37050
    11:10:23 AMX S 26400 65.64 10650
    14:00:33 AMX S 70200 65.5308 -43800

    I want to accumulate the count of share quantity (quantity field) for buys and sells with an on-going run total. problem is that it works for the first sell, calculating (37050-26400=10650) but wont work when I have 2 sells in a row as value -43,800 is incorrect. Should be -59,550 --so what I really need for 2 consecutive sells is to calculate 10650-70,200 NOT 26,400-70,200

    Hope this makes sense hay anyone dealt witrh something like this before

    This is my 1st post ever on this site -- Obviously , I am desparate and just plain MAD
    The key calculation for sell in below sql is

    lag (sum(em.shares_quantity),1) over (order by em.symbol,em.buy_sell_code) - em.shares_quantity


    Thanks in advance
    Mad Brendan

    select
    /*+ INDEX (em EVENT_MAIN_COMP5_BI) */
    substr(tl.time_string,1,2)||':'||substr(tl.time_st ring,3,2)||':'||substr(tl.time_string,5,2) "EX_TIME",
    em.symbol "SYMBOL",
    em.buy_sell_code "SIDE",
    em.shares_quantity "EX_QTY",
    eem.exec_price "EX_PR",
    em.terminal_id "TRADER",
    case
    when em.buy_sell_code='B' then
    sum(em.shares_quantity) over (partition by em.symbol,em.buy_sell_code order by tl.time_string,em.buy_sell_code)
    when em.buy_sell_code='SL' then
    lag (sum(em.shares_quantity),1) over (order by em.symbol,em.buy_sell_code) - em.shares_quantity
    end
    from execution_event_main eem, event_main em, src_application sa, eds_instrument edsi,
    trd_account ta, time_lookup tl, employee emp
    where to_char(em.terminal_id) || to_char(ta.TRD_ACCT_ID)||em.symbol in
    (
    select /*+ INDEX (em EVENT_MAIN_COMP5_BI) */
    distinct (to_char(em.terminal_id) || to_char(ta.TRD_ACCT_ID) ||em.symbol)
    from execution_event_main eem, event_main em, src_application sa, eds_instrument edsi,
    trd_account ta, time_lookup tl,employee emp
    where em.app_id = sa.app_id
    and sa.app_full_name in ('LODC','LODS','LEGO')
    and em.event_date_id=eem.event_date_id
    and em.event_date_id=1033
    and em.event_id=eem.event_id
    and em.event_type = 'EX'
    and edsi.oats_eds_key=em.instrument_id
    and edsi.exchange='XNYS'
    and em.buy_sell_code='B'
    and em.capacity='P'
    and (em.shares_quantity * eem.exec_price)>=500000
    and em.terminal_id=ta.TRD_ACCT_ID
    and em.terminal_id=emp.emp_id (+)
    and tl.time_id=em.event_time_id
    )
    and em.app_id = sa.app_id
    and sa.app_full_name in ('LODC','LODS','LEGO')
    and em.event_date_id=eem.event_date_id
    and em.event_date_id=1033
    and em.event_id=eem.event_id
    and em.event_type = 'EX'
    and edsi.oats_eds_key=em.instrument_id
    and edsi.exchange='XNYS'
    and em.buy_sell_code in ('B','SL')
    AND ((em.buy_sell_code = 'B' and (em.shares_quantity * eem.exec_price)>=500000) or em.buy_sell_code = 'SL')
    and em.capacity in ('P','R')
    and em.terminal_id=ta.TRD_ACCT_ID
    and em.terminal_id=emp.emp_id (+)
    and tl.time_id=em.event_time_id
    --group by EX_TIME,SYMBOL,SIDE,EX_QTY,EX_PR,TRADER
    group by
    tl.time_string,
    em.symbol,
    em.buy_sell_code,
    em.shares_quantity,
    eem.exec_price,
    em.terminal_id
    order by SYMBOL,SIDE,EX_TIME;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.dbforums.com/showthread.php?t=1031644
    At a minimum you should read & FOLLOW posting guidelines & use <code tags> to make the code more readable.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Well, it seems to me, you are trying to count SUM. So, forget about LAG and have a look at WINDOWING_CLAUSE in analytic functions. Something like
    Code:
    SUM(DECODE( buy_sell, 'S', -1, 1 ) * shares_quantity)
      OVER (PARTITION BY symbol
      ORDER BY time
      ROWS UNBOUNDED PRECEEDING)
    Also have a look how to format your post in the future (see the difference between yours and mine).

  4. #4
    Join Date
    Nov 2007
    Location
    Connecticut
    Posts
    40

    Smile

    Hey Flyboy

    That helped tremendously -- Appreciate the Help -- I got it to work

    I'll remember to format text properly next time -- That was my 1st post -- just a newbie on this forum

    Thanks

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Thank you for the feedback.

    I forgot to mention, that I only briefly went through your code, as it was too big for analysis.
    In your future posts, you could try to simplify it and show only relevant parts (eg. cover the joins and conditions into view).
    On the other hand, your description was precise, so I could understand your problem well (although it was just a draft and you probably had to adjust it a little).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •