Mathematical Equations

# Mathematical Equations

• Monday, May 07, 2012 2:11 PM

Hi,

I have a table that has multiple mathematical equations with the individual components split over rows (Instead of columns).

For example:

Table TEST

Equation_ID         Equation_Component

1                              A

1                              +

1                              (

1                              B

1                              *

1                              C)

2                              A

2                              -

2                              E

2                              +

2                              F

In this example, I have 2 equations. Eq1 = A+(B*C), Eq2 = A-E+F

(A,B,C,E,F map back to data values stored in a different table).

I would like to calculate the results of these equations in a set. I have found a way to flatten the equation so that it is one line (using FOR XML) and I can pass the result of that query and execute it using dynamic SQL. This works well but only on a record by record basis.

Does anyone know of a more efficient way of calculating this?

### All Replies

• Monday, May 07, 2012 3:32 PM
Moderator

Try something along these lines:

use tempdb
create table Equations (PK int identity primary key,   Equation_ID    int,     Equation_Component varchar(10))
insert into Equations
select
1,                              'A'
union all select
1,                              '+'
union all select
1,                              '('
union all select
1,                              'B'
union all select
1,                              '*'
union all select
1,                              'C)'
union all select
2,                              'A'
union all select
2,                              '-'
union all select
2,                              'E'
union all select
2,                              '+'
union all select
2,                              'F'

declare @sql NVARCHAR(MAX)
;with cte as (select (select Equation_Component as [data()] from Equations E1
where E1.Equation_ID = E.Equation_ID order by PK
for xml path(''),type).value('.','varchar(max)') AS [Expression] from Equations E
GROUP BY E.Equation_ID)

SELECT @sql = stuff((select 'UNION ALL
SELECT ' + Expression + ' from Equations '
from cte for xml path(''),type).value('.','varchar(max)'),1,10,'')

print @SQL

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Monday, May 07, 2012 3:54 PM

I like this approach. However, it doesn't quite solve what I need to do. I don't think my explanation was that great.

In the first example I listed above, Eq1 = A+(B*C), A, B, and C are mapped back to values. I need to replace these variables with their values. For example A+(B*C) could be 5 + (2 * 3). I need to store the result (11).

I need to make the equation a string and then execute the string to get the result.

Any ideas?

Again, any help is appreciated. Thanks!

• Monday, May 07, 2012 3:55 PM

I also should add that the equation components are ordered. This is done so that we know the order in the equation. For example:

Equation_ID         Equation_Component   ComponentOrder

1                              A                                1

1                              +                                2

1                              (                                 3

1                              B                                4

1                              *                                5

1                              C                                6

1                               )                                7

• Monday, May 07, 2012 3:59 PM
Moderator

Right, perhaps you noticed that I added PK in the original table. In your case change PK to component order.

Now, in order to map the expressions you will need to join with the table where you store values. I think you need to explain a bit more of the problem, but I believe the idea I gave you should work and you need to adapt it for your scenario or provide a better explanation with all necessary tables and data demonstrating it.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Monday, May 07, 2012 4:02 PM

sth like this:

create table #temp_result(Equation_ID varchar(10),result numeric(18,2))

create table #temp(Equation_ID int,Equation varchar(1000))
insert into #temp
select Equation_ID ,(select ''+Equation_Component as [text()] from @t where t.Equation_ID=Equation_ID for xml path('')) from @t	t
group by Equation_ID

declare @Lower int
set @Lower=1
declare @Upper int
set @Upper=(select COUNT(*) from #temp )
declare @temp varchar(1000)
while(@Lower<=@Upper)
BEGIN
set @temp=(select 'select '+Equation + ','+cast(Equation_ID as varchar(10)) from #temp where Equation_ID=@Lower)
insert into #temp_result(result,Equation_id)
exec (@temp)
set @Lower=@Lower +1
END

select * from #temp_result

Thanks and regards, Rishabh K

• Tuesday, May 08, 2012 2:29 PM

Thank you both very much for your replies. I appreciate it!

It works great. I just have one more question: Do you know of a way that I can execute these equations without looping?

For example:

In this example I have substituted actual values for A,B,C,E,F.

Equation Id                 Equation                 Result

1                                10 + (5*2)             20

2                                5-1+3                     7

I would like to know the values of these equations stored in a separate field (such as result).

Do you know of a way I can execute these equations without looping through the equation Ids?

Thanks again.

• Tuesday, May 08, 2012 3:01 PM
Moderator

I showed it to you as how to perform these calculations using 1 dynamic select statement.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Wednesday, May 09, 2012 1:12 PM

Thanks Naomi. I really appreciate your help.

I have one more question. I would like to have the results displayed per equation id.

SELECT @sql = stuff((select 'UNION ALL SELECT DISTINCT ' + Expression + ' ,Equation_Id  from Equations '
from cte for xml path(''),type).value('.','varchar(max)'),1,10,'')
---------------------------------------------------
/* Results:
result    equation_id
16	1
16	2
13	1
13	2
--I substituted values for the letters. The formulas are:
10 + ( 2 * 3)	1
10 - 1 + 4	2
*/

I changed the query to select equation_id but I get duplicates. It is printing a copy of the result per equation id.

Any ideas on how I can get this to display :

16      1

13      2

Thanks in advance. Have a great day!

• Wednesday, May 09, 2012 1:17 PM
Moderator

I need to see your tables (with some data) in order to provide you a solution. It's hard to discuss a problem based on the limited information you gave so far.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Wednesday, May 09, 2012 1:56 PM
Moderator

I believe you have to loop with dynamic SQL execution (Naomi's code above with minor modifications):

use tempdb;
GO

create table Equations (PK int identity primary key,   Equation_ID    int,
Equation_Component varchar(10));

insert into Equations
select
1,                              'A'
union all select
1,                              '+'
union all select
1,                              '('
union all select
1,                              'B'
union all select
1,                              '*'
union all select
1,                              'C)'
union all select
2,                              'A'
union all select
2,                              '-'
union all select
2,                              'E'
union all select
2,                              '+'
union all select
2,                              'F'
union all select
3,                              'A'
union all select
3,                              '-'
union all select
3,                              'E'
union all select
3,                              '+'
union all select
3,                              'G'

declare @sql NVARCHAR(MAX);
with cte as (select (select Equation_Component as [data()] from Equations E1
where E1.Equation_ID = E.Equation_ID order by PK
for xml path(''),type).value('.','varchar(max)') AS [Expression] from Equations E
GROUP BY E.Equation_ID)

SELECT @sql = stuff((select 'UNION ALL
SELECT ' + Expression + ' from YourTable '
from cte for xml path(''),type).value('.','varchar(max)'),1,10,'');

print @SQL;
GO
/*
SELECT A + ( B * C) from YourTable UNION ALL
SELECT A - E + F from YourTable UNION ALL
SELECT A - E + G from YourTable  */

-- EXEC SP_EXECUTESQL @SQL; -- Dynamic SQL execution

drop table Equations;
Article:
http://www.sqlusa.com/bestpractices/dynamicsql/

Kalman Toth SQL SERVER & BI TRAINING

• Wednesday, May 09, 2012 2:50 PM
Moderator

Kalman,

Isn't it exactly what I suggested in my prior message?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Wednesday, May 09, 2012 3:02 PM

Hi Naomi,

I apologize for the lack of data. Here is a simplistic view of my tables. I hope this helps. I need to update D = A+(B*C), G = A-E+F.

Is this possible (w/o looping)?

Thanks again!

/*Table A:
DataValue DataLetter ProdYear
10	A	 2011
11	A	 2012
2	B	 2011
3	B	 2012
3	C	 2011
3	C	 2012
1	E	 2011
0	E	 2012
4	F	 2011
5	F	 2012
NULL	D	 2011
NULL	D	 2012
NULL	G	 2011
NULL	G	 2012
Table B:
eqId     eqType    eqComponent
1	1	A
1	2	+
1	2	(
1	1	B
1	2	*
1	1	C
1	2	)
1	2	=
1	1	D
2	1	A
2	2	-
2	1	E
2	2	+
2	1	F
2	2	=
2	1	G
eqType1 = DataLetter (use this to map to Table A)
(Based on prodYear)*/

• Wednesday, May 09, 2012 3:04 PM

I should add that eqType 2 means an mathematical operator.

I also have a componentOrder which I did not list here but the order shown is the correct order.

Thanks again.

• Wednesday, May 09, 2012 3:05 PM

Thank you SQLUSA!
• Wednesday, May 09, 2012 3:12 PM

I think the question is, roll all the results back into a join against the original equation (via equation_id), or find a way to keep the equation_id in the results set?  So for example, Rishabh's answer (in theory if it rolled in the join on the variable values, and vetted out a couple other things) created a temp table, thereby it could return the equation_id along with the results... (I'd vote for also including a column containing the formula as a string too).

• Edited by Wednesday, May 09, 2012 3:19 PM clarified comments
•
• Wednesday, May 09, 2012 4:09 PM
Moderator

Kalman,

Isn't it exactly what I suggested in my prior message?

Affirmative  Naomi. I agree with your assessment (again!).

Kalman Toth SQL SERVER & BI TRAINING

• Wednesday, May 09, 2012 4:32 PM
Moderator

Try:

USE tempdb CREATE TABLE T1 ( DataValue FLOAT ,DataLetter CHAR(1) ,ProdYear SMALLINT ) INSERT INTO T1 SELECT 10 ,'A' ,2011 UNION ALL SELECT 11 ,'A' ,2012 UNION ALL SELECT 2 ,'B' ,2011 UNION ALL SELECT 3 ,'B' ,2012 UNION ALL SELECT 3 ,'C' ,2011 UNION ALL SELECT 3 ,'C' ,2012 UNION ALL SELECT 1 ,'E' ,2011 UNION ALL SELECT 0 ,'E' ,2012 UNION ALL SELECT 4 ,'F' ,2011 UNION ALL SELECT 5 ,'F' ,2012 UNION ALL SELECT NULL ,'D' ,2011 UNION ALL SELECT NULL ,'D' ,2012 UNION ALL SELECT NULL ,'G' ,2011 UNION ALL SELECT NULL ,'G' ,2012 CREATE TABLE T2 ( eqId INT ,eqType TINYINT ,eqComponent CHAR(1) ,eqOrder INT identity(1, 1) ) INSERT INTO t2 SELECT 1 ,1 ,'A' UNION ALL SELECT 1 ,2 ,'+' UNION ALL SELECT 1 ,2 ,'(' UNION ALL SELECT 1 ,1 ,'B' UNION ALL SELECT 1 ,2 ,'*' UNION ALL SELECT 1 ,1 ,'C' UNION ALL SELECT 1 ,2 ,')' UNION ALL SELECT 1 ,2 ,'=' UNION ALL SELECT 1 ,1 ,'D' UNION ALL SELECT 2 ,1 ,'A' UNION ALL SELECT 2 ,2 ,'-' UNION ALL SELECT 2 ,1 ,'E' UNION ALL SELECT 2 ,2 ,'+' UNION ALL SELECT 2 ,1 ,'F' UNION ALL SELECT 2 ,2 ,'=' UNION ALL SELECT 2 ,1 ,'G' DECLARE @SQL NVARCHAR(max) IF OBJECT_ID('tempDB..#Temp', N'U') IS NOT NULL DROP TABLE #Temp; WITH cte1 AS ( SELECT EqID ,ProdYear FROM ( SELECT DISTINCT EqID FROM T2 ) X CROSS JOIN ( SELECT DISTINCT ProdYear FROM T1 ) Y ) SELECT E.EqID ,E.ProdYear ,( SELECT CASE WHEN E1.eqType = 1 THEN coalesce(cast(T1.DataValue AS VARCHAR(10)), E1.EqComponent) ELSE EqComponent END AS [data()] FROM T2 E1 LEFT JOIN T1 ON E1.eqComponent = T1.DataLetter AND E1.eqType = 1 WHERE E1.eqID = E.eqID AND ( T1.ProdYear IS NULL OR T1.ProdYear = E.ProdYear ) ORDER BY E1.eqOrder FOR XML path('') ,type ).value('.', 'varchar(max)') AS [Expression] INTO #Temp FROM cte1 E SELECT @SQL = stuff(( SELECT ' UNION ALL SELECT ' + convert(VARCHAR(10), EqID) + ' AS EqID, ' +

convert(VARCHAR(10), ProdYear) + ' AS ProdYear, ' +

replace(Expression, '=', ' AS ') FROM #Temp ORDER BY EqID ,ProdYear FOR XML PATH('') ,type ).value('.', 'nvarchar(max)'), 1, 10, '') PRINT @SQL EXECUTE (@SQL)

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Wednesday, May 09, 2012 4:48 PM
Moderator

And this is the version that also performs UPDATE:

USE tempdb

CREATE TABLE T1 (
DataValue FLOAT
,DataLetter CHAR(1)
,ProdYear SMALLINT
)

INSERT INTO T1
SELECT 10
,'A'
,2011

UNION ALL

SELECT 11
,'A'
,2012

UNION ALL

SELECT 2
,'B'
,2011

UNION ALL

SELECT 3
,'B'
,2012

UNION ALL

SELECT 3
,'C'
,2011

UNION ALL

SELECT 3
,'C'
,2012

UNION ALL

SELECT 1
,'E'
,2011

UNION ALL

SELECT 0
,'E'
,2012

UNION ALL

SELECT 4
,'F'
,2011

UNION ALL

SELECT 5
,'F'
,2012

UNION ALL

SELECT NULL
,'D'
,2011

UNION ALL

SELECT NULL
,'D'
,2012

UNION ALL

SELECT NULL
,'G'
,2011

UNION ALL

SELECT NULL
,'G'
,2012

CREATE TABLE T2 (
eqId INT
,eqType TINYINT
,eqComponent CHAR(1)
,eqOrder INT identity(1, 1)
)

INSERT INTO t2
SELECT 1
,1
,'A'

UNION ALL

SELECT 1
,2
,'+'

UNION ALL

SELECT 1
,2
,'('

UNION ALL

SELECT 1
,1
,'B'

UNION ALL

SELECT 1
,2
,'*'

UNION ALL

SELECT 1
,1
,'C'

UNION ALL

SELECT 1
,2
,')'

UNION ALL

SELECT 1
,2
,'='

UNION ALL

SELECT 1
,1
,'D'

UNION ALL

SELECT 2
,1
,'A'

UNION ALL

SELECT 2
,2
,'-'

UNION ALL

SELECT 2
,1
,'E'

UNION ALL

SELECT 2
,2
,'+'

UNION ALL

SELECT 2
,1
,'F'

UNION ALL

SELECT 2
,2
,'='

UNION ALL

SELECT 2
,1
,'G'

DECLARE @SQL NVARCHAR(max)

IF OBJECT_ID('tempDB..#Temp', N'U') IS NOT NULL
DROP TABLE #Temp;

WITH cte1
AS (
SELECT EqID
,ProdYear
FROM (
SELECT DISTINCT EqID
FROM T2
) X
CROSS JOIN (
SELECT DISTINCT ProdYear
FROM T1
) Y
)
SELECT E.EqID
,E.ProdYear
,(
SELECT CASE
WHEN E1.eqType = 1
THEN coalesce(cast(T1.DataValue AS VARCHAR(10)), E1.EqComponent)
ELSE EqComponent
END AS [data()]
FROM T2 E1
LEFT JOIN T1 ON E1.eqComponent = T1.DataLetter
AND E1.eqType = 1
WHERE E1.eqID = E.eqID
AND (
T1.ProdYear IS NULL
OR T1.ProdYear = E.ProdYear
)
ORDER BY E1.eqOrder
FOR XML path('')
,type
).value('.', 'varchar(max)') AS [Expression]
INTO #Temp
FROM cte1 E

IF OBJECT_ID('tempDb..#Results', N'U') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #Results (
EqID INT
,ProdYear SMALLINT
,ExprValue FLOAT
,DataLetter CHAR(1)
)

SELECT @SQL = stuff((
SELECT ' UNION ALL
SELECT ' + convert(VARCHAR(10), EqID) + ' AS EqID, ' + convert(VARCHAR(10), ProdYear) + ' AS ProdYear, ' + replace(Expression, '=', ' AS ') + ' , ' + quotename(right(Expression, 1), '''') + ' AS DataLetter'
FROM #Temp
ORDER BY EqID
,ProdYear
FOR XML PATH('')
,type
).value('.', 'nvarchar(max)'), 1, 10, '')

PRINT @SQL

INSERT INTO #Results
EXECUTE (@SQL)

UPDATE T1
SET DataValue = R.ExprValue
FROM T1
INNER JOIN #Results R ON T1.ProdYear = R.ProdYear
AND T1.DataLetter = R.DataLetter

SELECT *
FROM T1

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Thursday, May 10, 2012 12:39 PM

Wow! Thank you very much!! This is great. I appreciate your help.

Have a great day. Thanks again.

• Thursday, May 10, 2012 6:34 PM
Owner

Wow! Thank you very much!! This is great. I appreciate your help.

Have a great day. Thanks again.

Thanks to everyone for helping out MissMolly in this thread!