SQL Puzzle

The above figure show a 5X5 matrix. The matrix is formed by using natural numbers which starts at the center and circles out to the outer layers as shown it the diagram.

The principal diagonal of the matrix is formed by the elements 21, 7, 1, 3, 13 and the secondary diagonal by the elements 17, 5,  1, 9, 25. Since 1 is repeating in both the diagonals it needs to be added only once. The sum of diagonals of the above matrix is 101.

Write a SQL query to find the sum of diagonals of a 501X501 matrix.

Solution:

At least one diagonal element contains square of the matrix size as 5X5 contains 25 in its box as one of the diagonal. Then add another number to this as the another diagonal element which will always be one less than size of matrix in this case it is 4. So the new number to be added is 25-4=21. Similar way when we go for iteration, we get the desired result.

 --===============================================================--

CREATE FUNCTION [dbo].[diag_sum_new](@Num BIGINT)

RETURNS BIGINT

AS

BEGIN

DECLARE @sum BIGINT;

DECLARE @sqr BIGINT;

SET @sum=0;

SET @sqr=0;

WHILE(@Num>1)

Begin

                   

SET @sqr=(@Num * @Num);

SET @sum=@sum+@sqr+(@sqr-@num+1);

SET @sum=@sum+(@sqr-2*(@num-1));

SET @sum=@sum+(@sqr-3*(@num-1));

SET @Num=@Num-2;

END

RETURN @sum+1

END

-- select dbo.[diag_sum_new](501)

  --===============================================================--