SQL 2008 R2 or SQL 2014 avoiding cursor in T-SQL

I have a record set such as

Number 1      Number 2      New Number
0                      1.2498                        1.2498
                      0.492      
                      0.7676      
                      0.6987      
                      0.5363      
                                  0.8611      

The first record of number 1 is 0.  Number 1 + Number 2 = New Number.  The New Number then becomes, Number 1 and the addition is done again to create the new number.  and so forth. I know how to do this with a cursor, however, I would like to do it without a cursor (simply bc it's 1000's of records and the cursor takes too long)

Any ideas?  This is a sample of what the result should look like:  

Number 1      Number 2      New Number
0                       1.2498                       1.2498
1.2498                       0.492                       1.7418
1.7418                       0.7676                       2.5094
2.5094                       0.6987                       3.2081
3.2081                       0.5363                       3.7444
3.7444                       0.8611                       4.6055
yanci1179Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Can you provide some schema information.  This appears to be solvable with a fairly simple query but you need to provide a little more information.  Are the numbers in separate tables?  How do you relate them or is it a cross product?
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Brian,

I am also working on the same question. I don't have the solution yet. I am sharing the following with you to help out in the process.
use ee

create table #t(Number2 decimal(6,4));
insert #t(Number2) values
(1.2498), (0.492), (0.7676), 
(0.6987), (0.5363), (0.8611);

Select * from #t;

;with cte as
(Select 0 as Number1, Number2 
From #t)
Select Number1, Number2, Number1 + Number2 as Number3
From cte;

Open in new window

yanci1179Author Commented:
Only the first record has number1 and newnumber.  Number1 and NewNumber are derived.  

I did notice that the sum of Number2 of current and previous records will equal the current New Number

Number 1      Number 2             New Number
 0                1.2498               1.2498
 1.2498       0.492                1.7418
 1.7418       0.7676               2.5094
 2.5094       0.6987               3.2081
 3.2081       0.5363               3.7444
 3.7444       0.8611               4.6055


so 1.2498     + 0.492  = 1.7418
1.2498+0.492 +0.7676 = 2.5094

Create table #sampleTable
(id int identity (1,1)
,Number1 float
,Number2 float
,NewNumber float)

insert into #sampleTable
select 0, 1.24984399951693,  1.24984399951693 + 0
insert into #sampleTable
select null, 0.492064566738954, null
insert into #sampleTable
select null,0.767620724112759, null  
insert into #sampleTable
select null,0.698731684769303, null  
insert into #sampleTable
select null,0.536350377745453, null
insert into #sampleTable
select null,0.861112991793172 , null
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Shaun KlineLead Software EngineerCommented:
Something like this?
DECLARE @Test TABLE
(
	Number1 DECIMAL(9, 4),
	Number2 DECIMAL(9, 4),
	NewNumber DECIMAL (9, 4)
)

INSERT INTO @Test (Number1, Number2) VALUES (0, 1.2498)
INSERT INTO	@Test (Number2) VALUES (0.492), (0.7676), (0.6987), (0.5363), (0.8611)

;WITH cte (Number1, Number2, RowNum)
	AS (SELECT Number1, Number2, ROW_NUMBER() OVER (ORDER BY ISNULL(Number1, 0))
		FROM @Test
	),
	cte_b (Number1, Number2, NewNumber, RowNum)
	AS (SELECT cte.Number1, cte.Number2, 
			CAST(cte.Number1 + cte.Number2 AS DECIMAL(9,4)), cte.RowNum
		FROM cte
		WHERE cte.RowNum = 1
		UNION ALL
		SELECT cte_b.NewNumber, cte.Number2, 
			CAST(cte_b.NewNumber + cte.Number2 AS DECIMAL(9,4)), cte.RowNum
		FROM cte_b
			INNER JOIN cte ON cte.RowNum = cte_b.RowNum + 1)
SELECT *
FROM cte_b

Open in new window


Increase the decimal precision to use the numbers in your sample.
yanci1179Author Commented:
Hi Shaun,

Thank you for the reply.  Your response did yield the correct results, however it takes really long.  To give you an idea, each batch has approximately 40K records.  I ran your example and it took over 10 minutes.  Is there another approach that may speed up the query.  I tried with the cursor and at this point the cursor is performing a little faster.  

Thank you again for your help!
Mike EghtebasDatabase and Application DeveloperCommented:
Try:
Select * from fnMakeTabe(1);

Open in new window

The parameter of this function is not doing much. I thought you might want to pass a parameter (ID =1) telling where to start; of course after making some necessary modifications to it. This function is producing the same result you want but depends on ID column to be able to use Order By clause. You can use any other column you want. Please change t to table name you have.
Create function fnMakeTabe(@IDstart int)
returns @table1 Table
(
  ID int
, N1 decimal(6,4)
, N2 decimal(6,4)
, N3 decimal(6,4)
)
begin
Declare @rn int = 0;
Declare @n1 decimal(6,4) ;
Declare @n2 decimal(6,4);
Declare @n3 decimal(6,4)=0.0;
Declare @ID int;
Declare @rows int = 0;

select @rows = count(*) from t;
While @rn <@rows
begin

Select  @ID = ID
      , @n2 = Number2 
	  , @n1 =(Case When @n3 > 0.0 Then @n3
	  Else
	     coalesce(Number1,0.0) 
	  End )
	  from t ORDER BY ID 
	  OFFSET @rn ROWS FETCH NEXT 1 ROWS ONLY;

select  @n3 = @n1 + @n2;

Insert @table1 Select @ID, @n1, @n2, @n3;
set @rn += 1;
End
Return 
End

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
How do we know the order of the records? I mean, why do we add 0.492 for the 2nd record and not 0.7676?
PortletPaulEE Topic AdvisorCommented:
select
    sum(Number2) over(order by rn) - Number2 as Number1
  , Number2
  , sum(Number2) over(order by rn) as NewNumber
from (
      SELECT
          Number2
        , ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN
      FROM YourTable
     ) as d
;

Open in new window

| Number1 | Number2 | NewNumber |
|---------|---------|-----------|
|       0 |  1.2498 |    1.2498 |
|  1.2498 |   0.492 |    1.7418 |
|  1.7418 |  0.7676 |    2.5094 |
|  2.5094 |  0.6987 |    3.2081 |
|  3.2081 |  0.5363 |    3.7444 |
|  3.7444 |  0.8611 |    4.6055 |

Open in new window

CREATE TABLE YourTable
    ([Number1] decimal(12,4), [Number2] decimal(12,4))
;

INSERT INTO YourTable
    ([Number1],[Number2])
VALUES
    (0.0,1.2498)
;

INSERT INTO YourTable
    ([Number2])
VALUES
    (0.492),
    (0.7676),
    (0.6987),
    (0.5363),
    (0.8611)
;

Open in new window

also see http://sqlfiddle.com/#!6/06c35/5

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yanci1179Author Commented:
Victor, it is in consecutive order.


Paul, thank you so much!!  Your query worked for 40K records in nothing seconds.

Thanks again!!
yanci1179Author Commented:
Excellent solution!!
Vitor MontalvãoMSSQL Senior EngineerCommented:
Victor, it is in consecutive order.
What's consecutive order? If you mean by the order that they were inserted then be very careful since that's not a rule for SQL Server databases. Nothing can assure you that the rows will be always in that order unless a clustered index.
yanci1179Author Commented:
thanks Victor.  The order of the records are not an issue.  The table does have a cluster key.  The code has been working with a cursor for a couple years now, just looking into optimizing it as a few things are changing it.  thanks.
Mike EghtebasDatabase and Application DeveloperCommented:
Paul's solutions are always the best and good learning pieces for the rest.

@yanci1179,

To echo what Victor is saying, in the absence of cluster index (not mandatory) data table is known as hip. And hip, has no order and most like will shift the rows up and down. For example when user enters 10th row of data, it may appear as fifth. This will mess up your output result.

A hip doesn't behave like an excel spreadsheet.

Mike
yanci1179Author Commented:
Thanks guys for the info.  In this case, the table is clustered and like i said it has been working for a few years as far as the records being in the correct order.  

Thank you everyone for all your feedback!
PortletPaulEE Topic AdvisorCommented:
My solutions are not alwaya the best, i simply try to help as best I can.

My solution here relies on a 'trick' which is to order by select null in the over clause as order by is mandatory in that we query.   In effect however this trick avoids imposing an order and simply adopts the default row sequence for that table.

Also my approach only requires a single scan of the data which is likely to be the major cintributor to its speed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.