In many SQL client GUIs you can create a query parameter, which can turn an element of a WHERE statement into a variable, and the user is prompted to enter a value so that the query can run based on that value.
Here is my idea I am trying to solve for:
I have a relatively complex formula that uses some product sales metrics and some user-defined values to come up with an expected cost. For simplicity sake, let’s say it just looks like this:
Cost = metric1 x U, where U is some number
Is there a way to turn a dimension within a derived table into a variable based on the value of a filter?
I’m thinking I could create a derived table that generates a series, but I don’t have any ideas on how to join such a table to any other table without creating a cartesian product, which in this case could be too many rows.
Any ideas? I’m an Accountant that knows enough SQL to be dangerous, so please forgive me if the answer is simple.
