A customer asked if it was possible to compute a table of cumulative transaction values by day. There were business reasons why this could not be left to a BI tool, so we worked out the following method.
- Create a persistent stage table T1 to hold the values to be accumulated. Completely anonymised, these values were TX_DATE, DIM1, DIM2, QUANTITY.
- From that persistent table, create a GROUP BY stage table T2 with the columns DIM1, DIM2 and SUM(QUANTITY). Discard the TX_DATE column.
- Add a derived column, a date named AT_DATE, with the transform CURRENT DATE.
The last step – a derived column – was the key to the solution.
We made one change to validation rules to enable this to work, and we are now able to meet the customer’s requirement.