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.
Best Answer
LINQ to SQL is an option, and here's a walkthrough that loosely does what you described. However, it's essentially a row by row solution.
Entity Framework may also provide additional ways to solve your challenge. This SO Question and this P.SE Question have some relevant information in comparing L2S and EF.
Another option to consider is a noSQL database like MongoDB or Cassandra or pick-your-flavor. Queries against static data like your example of "Last Year's Sales" can be pretty quickly performed within an noSQL / non-relational database.
The challenge will be deciding where you handle each calculation. Generally, complex calculations are best done in code since they aren't set operations. Likewise, relational DBs can make short work of something that is essentially a set operation. noSQL DBs provide a degree of a hybrid approach, but can require restructuring your data. Unfortunately, you need to evaluate each calculation and identify what the optimal environment will be for processing.