• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

Adding a given start number and end number in T-SQL without using loop

Hi, I want to create a stored procedure where you pass a start number and an end number. For example 1 as your start number and 1000 as your end number, then the output should be the sum of 1-1000.

We can do it easily using loops. But my requirement is to do it without using loops.

Can any one please give me that logic ?

Many Thanks
0
gvamsimba
Asked:
gvamsimba
  • 11
  • 5
  • 3
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>But my requirement is to do it without using loops.
Why's that?  Homework?
0
 
PortletPaulCommented:
sum of 1-1000 = 999

I think you mean 1 + 2 + 3 + 4 ... + 1000
(the sum of all numbers from 1 to 1000)

is that correct?
0
 
gvamsimbaAuthor Commented:
Hi Portlet , yes that is correct.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
gvamsimbaAuthor Commented:
I think you mean 1 + 2 + 3 + 4 ... + 1000
(the sum of all numbers from 1 to 1000)
0
 
PortletPaulCommented:
and Jim's questions?
0
 
gvamsimbaAuthor Commented:
yes kind of Homework :-)
0
 
PortletPaulCommented:
thanks for the honesty.

we can only guide you or help if your current approach has hit a snag

what have you tried so far?
0
 
PortletPaulCommented:
tip:
does the name Karl Friedrich Gauss
mean anything to you?
0
 
gvamsimbaAuthor Commented:
yes he is a German mathematician, why ?
0
 
gvamsimbaAuthor Commented:
Hi Portlet, I am not a sql guru yet and hence I posted the question on this website...Yes I have done this but using loops...But now the requirement is without  using loops.

can you suggest me of any idea ?
0
 
PortletPaulCommented:
Gauss worked out a method of quickly summing all numbers from 1 to 100

several maths sites explain the technique

it requires working out a repeating pattern, and I believe it would apply here (with some effort)
0
 
gvamsimbaAuthor Commented:
so can I not got any help from this site then ? I do not have enough time for research and hence I have posted it here.
0
 
Philip PortnoyCommented:
Hi there,

here's the code for you for calculating 1 to N:

SET @sum = (1 + @lastnumber) * (@lastnubmer / 2)

But we need to calculate M to N, which is basically (1 to N) - (1 to M). So it will be:

SET @sum = ((1 + @lastnumber) * (@lastnumber / 2)) - ((1 + @firstnumber) * (@firstnumber / 2))
0
 
gvamsimbaAuthor Commented:
Hi Jim,Ok, I was not aware of this policy, but thanks for letting me know and I will make sure I will follow these guidelines from now on.
0
 
gvamsimbaAuthor Commented:
Thanks for the help , but your solution will not work where I have 5 as the start number and 10 as the end number. However, I have worked my self the right solution which is below and works like a charm in all cases.

declare @x as int;

declare @y as int;

set @x =5;

set @y =10;

 

With raf

as

(

 

select  @x as num

 

union all

 

select num + 1

from raf

where num < @y

 

)

 

select sum(num)

from raf

option (maxrecursion 1000)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks.  In that case, give this a whirl..

Declare @firstnumber int = 0, @lastnumber int = 6
Declare @runningsum int = 0, @counter int = @firstnumber

WHILE @counter <= @lastnumber
      begin
      SET @runningsum = @runningsum + @counter
      SET @counter = @counter + 1
      end

SELECT @runningsum

Open in new window

0
 
gvamsimbaAuthor Commented:
Hi Jim,

Thanks for this, but As you can see from my above posts yesterday, I have already done this using loops but the requirement was to achieve this without using loops...

Cheers
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Again, spell out the requirement.  Ten bucks says you didn't get it from a business user, so either this is coming from a homework assigment (which means it's against policy for us to answer it) or a DBA got a wild hair and is torturing you for fun.

Spill.
0
 
gvamsimbaAuthor Commented:
Hi Jim,

Yes you are right, this is not exactly homework...our DBA has challenged me to do this without using loops .
However he was happy with my solution which I have described above and it works in all scenarios. I did not use a loop , however I have just used recursive CTE :-)
0
 
gvamsimbaAuthor Commented:
good
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 11
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now