We have a 3-tier application with a C# client that connects to a C# web service via WCF and requests data from a SQL Server database.
One feature in our application is a user-created form app in which our customers setup forms that have fields. The fields refer to values that can be imported, entered by the user, or calculated. The calculations can be thought of as Operation / Value / Timeframe. Each value referenced in a calculation might also be a calculated value. (Care is taken to ensure circular calculations do not exist.)
For example, a user might setup a field that shows Cash Over / Short that subtracts the value Total to Account For and Total Accounted For. These fields might in turn add up sales, credit card payments, cash totals, etc. Changing Grocery Sales in the form UI would trigger a change that recalculates the Total to Account For, and that field's change triggers the Cash Over / Short field to recalculate itself.
Complicating the calculations are historical values. Some calculations might be phrased in English like "Subtract Today's Totalizer Value from Yesterday's Totalizer Value". Or, someone might simply set a Last Year Sales field to the value of Total Sales exactly 365 days ago.
All this calculation is done in the C# user interface. The C# code builds dependency trees and sets up event handlers when values change. Changing a value triggers an event, and calculations that depend on the changed value are recalculated.
We also have a recalculation engine that verifies the totals are correct before making permanent changes (i.e. before drafting someone's account, so the amounts better be correct). This recalculation engine uses the same C# calculation engine across multiple threads. This incurs penalties of reading from the database, creating the C# objects, performing calculations, and writing to the database.
The timeframe dependencies mean we really need to calculate in some kind of day order. I could use some kind of cursor to move day-by-day, gather up all the data on each day, figure out the dependencies, join to historical values, but...
My question: how would you approach performing complex user-defined calculations in a set-based manner in SQL? I don't see how to do this without RBAR operations on each calculated value.