August 7, 2015 at 7:50 am
Hi,
I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + + [c] + [d]
[a] +
The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
Does anyone have any thoughts on this problem, and how best to tackle it?
August 7, 2015 at 7:55 am
kyagi.jo (8/7/2015)
Hi,I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + + [c] + [d]
[a] +
The developer before me wrote something SQL-nased where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
Does anyone have any thoughts on this problem, and how best to tackle it?
You will have to do this with dynamic sql. There is no other option if you have store equations and want them to execute.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 8:32 am
I did think that would have been likely, does anyone know if there there are any resources on the internet that might give me a headstart on getting this done, so I don't redesign the wheel from scratch if I don't have to?
August 7, 2015 at 8:42 am
kyagi.jo (8/7/2015)
I did think that would have been likely, does anyone know if there there are any resources on the internet that might give me a headstart on getting this done, so I don't redesign the wheel from scratch if I don't have to?
It sounds like you have a pretty specialized situation. It is going to require you to "reinvent" the wheel. I would be willing to help get you started but it would require some ddl and sample data from you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 8:45 am
kyagi.jo (8/7/2015)
I did think that would have been likely, does anyone know if there there are any resources on the internet that might give me a headstart on getting this done, so I don't redesign the wheel from scratch if I don't have to?
Understand that you're being asked to reproduce a subset of Excel... with a totally inappropriate tool for the job.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 7, 2015 at 11:01 am
sgmunson (8/7/2015)
kyagi.jo (8/7/2015)
I did think that would have been likely, does anyone know if there there are any resources on the internet that might give me a headstart on getting this done, so I don't redesign the wheel from scratch if I don't have to?Understand that you're being asked to reproduce a subset of Excel... with a totally inappropriate tool for the job.
To add to that, once done, the dynamic SQL in this case could become a major source of SQL Injection possibilities unless the appropriate safe-guards are taken.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply