Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I get
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position by
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to do
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
Maury
I tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury
|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades to
> date. I want to generate a row for every month where there was any activity.
> Some of the trades have null shares, however. So I did this (and before I get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stock,
> don't generate a row. That's because I was generating the opening position by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury