LEAD Function

[toddyl]

Hi,

I have the following scenario in relation to the holding of financial stocks:

On 01-Sept-2011 I bought two seperate lots in a stock -- 50 units and 100 units, as depicted below, for day T:

Day,Lot_Nbr,Units_Hld,Acq_date
==============================
T,1,50,01-Sept-2011
T,2,100,01-Sept-2011

On T+1 I decided to sell the first lot of 50 and 25 of the second lot resulting in:

Day,Lot_Nbr,Units_Hld,Acq_date
==============================
T+1,1,75,01-Sept-2011

Note: as we sold all of Lot 1, the 50 units, Lot 2 now becomes Lot 1.

Now, I am trying to write a SQL query to pull back these changes in LOTS. I am trying to use the LEAD Analytic function but I cant seem to partition it in such a way as to reflect what is happening.

I currently have:

Units_Hld - LEAD (
Units_Hld,
1,
0
)
OVER (
PARTITION BY lot_Nbr
ORDER BY d_date
)
Day

But this is subtracting the 75 from the 50 as it thinks they are the same lot_nbr. If I change it to use acq_date they its taking the 50 from the 100 which is also incorrect.

Any ideas what I can do to recreate the actual scenario in SQL?

Thanks.

 

[carp]

Where did you change the lot numbers? The database cannot tell that you are changing lot numbers unless you tell it you did. Consequently, when you says you sold 75 shares from lot number 1, all SQL has to go on is the fact that you have 50 shares in lot 1 and you just sold 75 from lot 1. I think you need to be more explicit in your sales transaction and make two entries

Day,Lot_Nbr,Units_Hld,Acq_date
==============================
T+1,1,50,01-Sept-2011
T+1,2,25,01-Sept-2011

 

[toddyl]

Another group provide data in a flat file and a batch job runs which updates the lots on a daily basis. I dont have access to this system and can only see the resultant output.

In the example above on day T they sold off all of lot 1, 50 units, and 25 units of lot2. On T+1 as lot 1 was no longer in existence the 75 remaining lot 2 units now are renumbered to be lot 1. Therefore showing that on day T+1 we only have 1 lot consisting of 75 units.

I need to devise a way of extracting this information by just looking at the data in my table.

Thanks.

 

[taupirho]

Given the information as presented I do not believe you can code a solution without some extra transactional data being captured. I can think of a few ways forward - from easiest to most difficult to implement.

1) Code some DML triggers on your table(s) that capture the extra information you need.

or

2) Investigate the use of flashback version queries

or

3) Look at table versioning using workspace management

or

4) Look into the use of Change data Capture (CDC) which is built on STREAMS technology



In order to understand recursion, you must first understand recursion.