Outstanding Stock Weighted Average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to set up some tables and queries to give me the following result:

Beg_Bal Date Change End_Bal Days Share_Days
1000 1/1/07 0 1000 6 6000
1000 1/7/07 (85) 915 7 6405
915 1/14/07 (75) 840 1 840
840 1/15/07 0 840 17 14280

Totals 31 27525
Weighted Average = 27525/31 = 887.9

Questions: How do I design the tables or a query so that I can make the
Begin_Bal change with each entry? How do I compute the number of days?

Is there any easier way for me to arrive at the same result?

Any help you can offer will be appreciated.

Cathy
 
Questions: How do I design the tables or a query so that I can make
the Begin_Bal change with each entry? How do I compute the number of
days?
Click to expand...

Note that DATE is a reserved word in SQL, so I'll call it BegDate
instead.

You can do what you want using standard subqueries. Break it down into
visible chunks first. You can get the opening balance of the table by
looking at the oldest record:

select First(BegBal) from MyTable order by BegDate ASC)

and you can get the overall change since then just by adding up all the
Change values

select sum(Change) from MyTable as i where i.BegDate <= o.BegDate)

.... where o.BegDate is the date you want to go as far as. It is a <=
operator because you need to include the current record each time.

So you can run the whole query like this:

SELECT BegDate,

( SELECT FIRST(BegBal)
FROM MyTable ORDER BY BegDate ASC
) + ( SELECT SUM(Change)
FROM MyTable AS i WHERE i.BegDate <= o.BegDate
) AS EndBal,

BegDate -
( SELECT MAX(BegDate)
FROM MyTable AS d
WHERE d.BegDate < o.BegDate
) AS NumberOfDays

FROM MyTable AS o
ORDER BY o.BegDate ASC;



Hope that helps


Tim F
 
Back
Top