Mathematical Equations

Ask a question

Monday, May 07, 2012 2:11 PM

0

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?

Any ideas are appreciated. Thanks in advance for your time.
- Reply
- Quote

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

Proposed As Answer by Leonardo Martínez P Thursday, May 10, 2012 7:45 PM

Monday, May 07, 2012 3:54 PM

0

Thank you for your reply.

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!
- Reply
- Quote
Monday, May 07, 2012 3:55 PM

0

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
- Reply
- Quote
Monday, May 07, 2012 3:59 PM

Moderator

1
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
- Proposed As Answer by Iric WenModerator Wednesday, May 09, 2012 3:14 AM
- Reply
- Quote

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

Proposed As Answer by Iric WenModerator Wednesday, May 09, 2012 3:14 AM

Tuesday, May 08, 2012 2:29 PM

0

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.
- Reply
- Quote
Tuesday, May 08, 2012 3:01 PM

Moderator

1

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
- Reply
- Quote
Wednesday, May 09, 2012 1:12 PM

1
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!
- Reply
- Quote
Wednesday, May 09, 2012 1:17 PM

Moderator

1

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
- Reply
- Quote

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

Edited by Kalman TothMicrosoft Community Contributor, Moderator Wednesday, May 09, 2012 4:07 PM

Wednesday, May 09, 2012 2:50 PM

Moderator

0

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
- Reply
- Quote

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

0

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.
- Reply
- Quote
Wednesday, May 09, 2012 3:05 PM

1

Thank you SQLUSA!
- Reply
- Quote
Wednesday, May 09, 2012 3:12 PM

0
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 johnqflorida Wednesday, May 09, 2012 3:19 PM clarified comments
- Reply
- Quote
Wednesday, May 09, 2012 4:09 PM

Moderator

2

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
- Reply
- Quote

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

Edited by Naomi NMicrosoft Community Contributor, Moderator Wednesday, May 09, 2012 4:33 PM
Proposed As Answer by Ed Price - MSFTMicrosoft Employee, Owner Thursday, May 10, 2012 6:33 PM

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

Proposed As Answer by Ed Price - MSFTMicrosoft Employee, Owner Thursday, May 10, 2012 6:33 PM
Marked As Answer by Iric WenModerator Tuesday, May 15, 2012 1:46 AM

Thursday, May 10, 2012 12:39 PM

1

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

Have a great day. Thanks again.
- Reply
- Quote
Thursday, May 10, 2012 6:34 PM

Owner

0

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!

MissMolly, please mark any of these replies that answered your questions. I'm glad they were able to help you!

Thanks!
Ed Price (a.k.a User Ed), SQL Server Experience Program Manager (Blog, Twitter, Wiki)
- Reply
- Quote