Complicated Cumulative Total/Count reporting requirement

Question

here is the shape of the table
I'm basing this table on the simplified and anonymous model of the confidential data i wish to show

CREATE TABLE BlocksAndTrades
(
 BlockKey Varchar(1),
 Item        Varchar(1),
 Book        Varchar(10),
 Notional    float,
 ReportingMonth int,
 CancellationMonth int 
)

Each block key is a category and it has trades (Item) that roll up to it...
so A consists of A1 to A6
B consists of B1 to B3

tblBlocksAndTrades

BlockKey	Item	Book	Notional	ReportingMonth	CancellationMonth
A	A1	BookA	5000	5
A	A2	BookA	1000	5	6
A	A3	BookA	1000	6	9
A	A4	BookA	1000	6	6
A	A5	BookA	1000	7
A	A6	BookA	1000	7
A	A7	BookA	1000	8	10
B	B1	BookB	1000	6
B	B2	BookB	1000	7	8
B	B3	BookB	1000	8
C	C1	BookC	1000	6
C	C2	BookC	1000	7	7
C	C3	BookC	5000	8	9
D	D1	BookD	1000	5	6
D	D2	BookD	1000	6	7
D	D3	BookD	1000	6
D	D4	BookD	1000	7	9
E	E1	BookE	1000	8	9
E	E2	BookE	1000	8	9
E	E3	BookE	1000	9	10
E	E4	BookE	1000	9	10
E	E5	BookE	1000	10

Here's the Query with the Correlated subquery that I wrote to get the cumulative total per month

qryCorrelate:
SELECT tbat.ReportingMonth, (SELECT Sum(iif(qs.CancellationMonth<=tbat.Reportingmonth,0,qs.SumOfNotional)) FROM qs where qs.ReportingMonth <= tbat.ReportingMonth) AS CumulativeSum, (SELECT Sum(iif(qs.CancellationMonth<=tbat.Reportingmonth,0,qs.CountOfItem)) FROM qs where qs.ReportingMonth <= tbat.ReportingMonth) AS CumulativeCount
FROM tblBlocksAndTrades AS tbat
GROUP BY tbat.ReportingMonth
ORDER BY tbat.ReportingMonth


the qs alias represents a convenient way to give a shortcut for the following SQL

SELECT tblBlocksAndTrades.ReportingMonth, tblBlocksAndTrades.Book, tblBlocksAndTrades.CancellationMonth, tblBlocksAndTrades.BlockKey, Count(tblBlocksAndTrades.Item) AS CountOfItem, Sum(tblBlocksAndTrades.Notional) AS SumOfNotional
FROM tblBlocksAndTrades
GROUP BY tblBlocksAndTrades.ReportingMonth, tblBlocksAndTrades.Book, tblBlocksAndTrades.CancellationMonth, tblBlocksAndTrades.BlockKey
ORDER BY tblBlocksAndTrades.ReportingMonth;




returns this

qryCorrelate

ReportingMonth	CumulativeSum	CumulativeCount
5	7000	3
6	10000	6
7	13000	9
8	21000	13
9	14000	10
10	12000	8

A bit of explanation on what I'm trying to do
I want a cumulative sum of notionals per month (and count of trades per month) where the trades are not cancelled yet
this is why in month 9 the cumulative total goes down
a trade goes out of scope within any month if the entry in the CancellationMonth is the same or less than the figure in the reportingmonth
if cancellation month is null or greater than the current ReportingMonth the trade is still active and we want to count it and its notional

if a trade is cancelled in the same month it is not counted

So far so good the cumulative sum is correct

here's my question....
how can I get that cumulative sum to be correct across more than one dimension, i.e if I put in Reporting month and say Book,
so per reportingmonth and per book what is the cumulative total... I couldn't make this work correctly...

lastly and this is a real toughie (for me)

how can I show repeating groups of trades (i.e blockKeys) in every month that they haven't yet expired, even though there may not be an entry for A5 in month 10, there should be (in the ouptut) because trade A5 that rolls up to A is still valid in month 10?

is this possible in SQL or does it need some procedural code

 you will have noticed that this is in Access SQL, this is not ideal but it's the only tool available to me right now....
if you send me real Transact Set based SQL (but without CTEs pls) I'm sure I'll figure out how to translate it

many thanks for your time in reading this and any potential answers
Regards
CharlesA

Answer 1

I worked out that what I needed was a cross-join

the Months are the outer loop, the inner loop is the keys (so two subqueries in effect) cross-joined
and then I had criteria that refered the left subquery to the month in the right subquery and that meant that only the entries that need to appear, appear...