Solve Linear Equations with SQL Server T-SQL Code


By:   |   Updated: 2020-10-29   |   Comments (4)   |   Related: More > T-SQL


Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.


Problem

This tip addresses the problem of solving any set of three linear equations with three unknown values (x, y, z) by using the N determinants mathematics method. In this tip I will describe a way to implement this with SQL Server's T-SQL code.

The format of the linear equations set is assumed like this:

  • A1x + B1y + C1z = Y1
  • A2x + B2y + C2z = Y2
  • A3x + B3y + C3z = Y3

Where A1, B1, C1, Y1, A2, B2, C2, Y2, A3, B3, C3, Y3 are real numbers.

The T-SQL code will find the solution for the set of equations by finding the values of x, y, z.

If there is no solution these output arguments will contain NULL as the output.

Solution

My solution involves creating a T-SQL stored procedure in the SQL Server master database, (or any other user database) called dbo.usp_solve_linear_equation_set_for_3_unkowns that accepts the input of 12 real arguments:  A1, B1, C1, Y1, A2, B2, C2, Y2, A3, B3, C3, Y3 and also 3 output arguments X, Y, Z and computes the solution.

Note: The mathematical theory that the T-SQL is based on is taken from the following article: Linear Equations: Solutions Using Determinants with Three Variables.

The solution involves calculating 4 results: DET, DETX, DETY, DETZ

The determinant of coefficients defined as:

DET = (a1 * b2 * c3 + a2 * b3 * c1 + a3 * b1 * c2) – (c1 * b2 * a3 + c2 * b3 * a1 + c3 * b1 * a2);

The determinant of X (all occurrences of Ai replace by Yi)

DETX = (y1 * b2 * c3 + y2 * b3 * c1 + y3 * b1 * c2) – (c1 * b2 * y3 + c2 * b3 * y1 + c3 * b1 * y2);

The determinant of Y (all occurrences of Bi replace by Yi)

DETY = (a1 * y2 * c3 + a2 * y3 * c1 + a3 * y1 * c2) – (c1 * y2 * a3 + c2 * y3 * a1 + c3 * y1 * a2);

The determinant of Z (all occurrences of Ci replace by Yi)

DETZ = (a1 * b2 * y3 + a2 * b3 * y1 + a3 * b1 * y2) – (y1 * b2 * a3 + y2 * b3 * a1 + y3 * b1 * a2);

If DET equals zero, then no solution exists, the T-SQL code encounters a division by zero exception and the output results is NULL for all output arguments.

If DET does not equal zero then a solution exists and is as follows:

X = DETX / DET 
Y = DETY / DET 
Z = DETZ / DET 

SQL Server Stored Procedure

Here is the implementation in a T-SQL stored procedure:

-- ====================================================================================
-- Author: Eli Leiba
-- Create date: 04-10-2020
-- Description: Solves 3 linear equations set 
-- The result is the real values for x, y and z unknowns (if solution exists)
-- if no solution exists the values are set to NULL
-- ====================================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_solve_linear_equation_set_for_3_unkowns] 
  (@a1 real, @b1 real, @c1 real, @y1 real, 
   @a2 real, @b2 real, @c2 real, @y2 real, 
   @a3 real, @b3 real, @c3 real, @y3 real, 
   @x real out, @y real out, @z real out) 
AS 
BEGIN 
   DECLARE @det REAL = 0; 
   DECLARE @detx REAL = 0; 
   DECLARE @dety REAL = 0; 
   DECLARE @detz REAL = 0; 

   BEGIN TRY 
      SET @det =  (@a1 * @b2 * @c3 + @a2 * @b3 * @c1 + @a3 * @b1 * @c2)  
                  (@c1 * @b2 * @a3 + @c2 * @b3 * @a1 + @c3 * @b1 * @a2); 

      SET @detx = (@y1 * @b2 * @c3 + @y2 * @b3 * @c1 + @y3 * @b1 * @c2)  
                  (@c1 * @b2 * @y3 + @c2 * @b3 * @y1 + @c3 * @b1 * @y2); 

      SET @dety = (@a1 * @y2 * @c3 + @a2 * @y3 * @c1 + @a3 * @y1 * @c2)  
                  (@c1 * @y2 * @a3 + @c2 * @y3 * @a1 + @c3 * @y1 * @a2); 

      SET @detz = (@a1 * @b2 * @y3 + @a2 * @b3 * @y1 + @a3 * @b1 * @y2)  
                  (@y1 * @b2 * @a3 + @y2 * @b3 * @a1 + @y3 * @b1 * @a2); 

      SET @x = @detx / @det; 
      SET @y = @dety / @det; 
      SET @z = @detz / @det; 
   END TRY 
   BEGIN CATCH 
      SET @x = NULL 
      SET @y = NULL 
      SET @z = NULL 
   END CATCH 
END 
GO

Sample Stored Procedure Execution

Here is an example that we will use with the stored procedure to solve for x, y and z:

equation

This is how the stored procedure is called. For any value that is just the variable, we will use a value of 1 as shown below.

use master
go

declare @x real, @y real, @z real 
exec dbo.usp_solve_linear_equation_set_for_3_unkowns 
    1, -2,  3,   7,
    2 , 1,  1,   4,
   -3,  2, -2, -10,
   @x out, @y out, @z out

print @x
print @y
print @z

And the output for the solution is as follows:

results
x =  2; 
y = -1; 
z =  1 

We can then plug in the values for x, y and z to check the equation:

equation
2 - 2 * (-1) + 3 * 1 = 7 
2 * 2 - 1 + 1 = 4 
-3 * 2 + 2 * (-1) - 2 * 1 = - 10 
Next Steps
  • You can create and compile this stored procedure in any database you choose in order to solve any three linear equations set.
  • The procedure was tested for SQL Server 2019, but should work with all versions of SQL Server.


Last Updated: 2020-10-29


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips



Comments For This Article




Friday, October 30, 2020 - 2:43:34 PM - Rick Dobson Back To Top (86731)
Interesting.

Thanks for posting the tip.

Rick Dobson

Friday, October 30, 2020 - 2:41:45 PM - Rick Dobson Back To Top (86730)
Hey Jeff,

Try the tip at https://www.mssqltips.com/sqlservertip/5506/tsql-starter-statistics-package-for-sql-server/. It has a section titled: A stored procedure to compute the slope and the intercept. The section includes the stored procedure code as well as simple example. I hope this helps.

Rick Dobson

Thursday, October 29, 2020 - 11:50:25 PM - Jeff Moden Back To Top (86718)
To be more accurate, what do a, b, c, and y represent in the formula?

Thursday, October 29, 2020 - 11:49:07 PM - Jeff Moden Back To Top (86717)
Ok... so let me ask, please...

I have a domain of pairs of positive numbers (and sometimes zero for Y but never negative) in a table. When plotted on a graph, all the resulting points would all be limited to the first quadrant of the graph (values of x and y are all positive).

How would I use this formula to determine a linear trend formula in the form of y = mx +b so that I could compare the values of each point on the graph to that linear trend in terms of y related to x?

And be gentle, please. I'm definitely not a math wiz or I wouldn't be asking this question. :D


download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms