# Thread: SQL Query using Lag function

1. Registered User
Join Date
Nov 2007
Location
Connecticut
Posts
40

## 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

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.shares_quantity "EX_QTY",
eem.exec_price "EX_PR",
case
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.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 = '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
tl.time_string,
em.symbol,
em.shares_quantity,
eem.exec_price,
em.terminal_id
order by SYMBOL,SIDE,EX_TIME;

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
At a minimum you should read & FOLLOW posting guidelines & use <code tags> to make the code more readable.

3. Registered User
Join Date
Mar 2007
Posts
629
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. Registered User
Join Date
Nov 2007
Location
Connecticut
Posts
40
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. Registered User
Join Date
Mar 2007
Posts
629
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
•