Calculate a quarterly rollover amount that can only rollover to the next quarter.

[RSfromC0]

I have a query that sums purchases & sales units for each product by quarter.

Some of these products are bought and sold at a "special price" - and they are identified as their own product, even though they are the same actual item as the same thing bought and sold at a regular price.

It's a little weird, but we are allowed to purchase at the special price up to the number of units we have sold at the special price during the previous quarter. If we don't purchase that many units during the current quarter, they do not roll-over to the following quarter.

So, I have a formula to calculate the "Rollover" units for each quarter for each product. As follows...
IF [Purchases] >= [Previous Quarter Rollover] THEN
[Rollover] = [Sales] - ([Purchases]-[Previous Quarter Rollover]) {CASE A}
ELSE
[Rollover] = [Sales] - [Purchases] {CASE B}

I'm wondering if (and HOW) I can implement this in a view/query (perhaps using a "OVER PARTITION BY" clause) or will I need to have a table that is ordered by product/year/quarter where I go row by row and calculate the Rollover and save it in each row.

Given an existing view that has Product, Year, Qtr, Sales, and Purchases fields, how can I calculate the Rollover field...

[pre]
Prod Year Qtr Sales Purch Roll Description…
---- ---- --- ----- ----- ----- --------------------------------------------------------------------
A1 2005 Q1 100 50 50 First Quarter, [Rollover] = [Sales]-[Purchases]
A1 2005 Q2 100 50 100 {CASE A} [Rollover] = [Sales] - ([Purchases]-[Previous Quarter Rollover])
A1 2005 Q3 25 100 25 {CASE A} [Rollover] = [Sales] - ([Purchases]-[Previous Quarter Rollover])
A1 2005 Q4 100 25 100 {CASE A} [Rollover] = [Sales] - ([Purchases]-[Previous Quarter Rollover])
A1 2006 Q1 100 100 100 {CASE A} [Rollover] = [Sales] - ([Purchases]-[Previous Quarter Rollover])
A1 2006 Q2 50 50 0 {CASE B} [Rollover] = [Sales] - [Purchases]
A1 2006 Q3 50 0 50 {CASE A} [Rollover] = [Sales] - ([Purchases]-[Previous Quarter Rollover])
A1 2006 Q4 100 25 75 {CASE B} [Rollover] = [Sales] - [Purchases]
[/pre]

 

[johnherman]

Since the Rollover appears to be a calculated field that is critical to the business, I would create a separate table with the Special Sales (rollover). Disk is relatively cheap.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright