loop through a sql db - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

loop through a sql db

Author

Topic

Qnatz
Starting Member

4 Posts

Posted - 2011-06-10 : 17:16:24

Hey guys
got a Sql db n tblReport which looks like this
ID Name StockIn StockOut Bal
2 OP 100 0 100
3 na 400 0
4 ma 0 600
5 xz 0 40
6 vc 20 0

i want to use sql to get this

ID Name StockIn StockOut Bal
2 OP 100 0 100
3 na 400 0 500
4 ma 0 600 -100
5 xz 0 40 -140
6 vc 20 0 -120

i want to go by row in a loop
like
for i = 0 to maxRow - 1
Bal(i+1) = Bal(i) + StockIn(i)-StockOut(i)
next i

can anyone help please
Qnatz

Qnatz

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-10 : 18:06:31

If you are on SQL 2005 or higher, a recursive CTE can do this for you. I am assuming that the ID's are numbered consecutively.


;with a as
(
   select
      Id, name, StockIn, Stockout,
      StockIn - StockOut as Bal
   from
      tblReport
   where
      Id = 2 -- or whatever is the starting Id
   
   union all
   
   select
      t.Id, t.Name, t.StockIn, t.StockOut,
      a.Bal - t.Stockout as Bal
   from
      tblReport t
      inner join a on t.Id = a.Id+1
)
select * from a;

Qnatz
Starting Member

4 Posts

Posted - 2011-06-11 : 08:36:08

Thanks for the quick reply Sunitabeck. I do appreciate..LOTS.
The code works perfect...
but if forgot to say that a number of Openning balances(OP) will appear for each catgegory of crops.

Name Crop StockIn StockOut Bal
OpeningBal Maize 15400 0 15400
Tony Maize 5000 0 20400
ceaser Maize 5000 10 25390
Ek Maize 5400 100 30690
sam Maize 4500 150 35040
Tony Maize 4500 150 39390
sam Maize 6000 0 45390
OpeningBal Beans 5000 0 5000
ceaser Beans 0 1000 4000
Ek Beans 0 1000 3000
sam Beans 50000 0 50000
Tony Beans 0 4000 46000
sam Beans 0 5000 41000
Tony Beans 1000 2000 40000
OpeningBal Soybean 60400 0 60400
Ek Soybean 0 1000 59400
sam Soybean 0 1000 58400
Tony Soybean 0 2000 56400
sam Soybean 5440 0 61840
Tony Soybean 0 4000 57840
ceaser Soybean 0 4000 53840

how can i always go back to change
Id = 2 -- or whatever is the starting Id
when ever OP is found

thanks

Qnatz

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 08:59:52

In your new sample data, there is one more piece of information that is required. And that is the order in which the Stock is consumed. This is required because, by definition, rows in a table are unordered. So even if you inserted the data in a specific order, SQL has no knowledge of that order, unless you specify it.

In your previous posting, you had an ID column which indicated the order. So we were able to make use of that. Do you have something similar - a sequential id number, or a date/time column or anything else that would help us determine the ordering?

Qnatz
Starting Member

4 Posts

Posted - 2011-06-11 : 15:43:33

VERY SORRY...
the table has an ID column that starts always at 1
ID Name Crop StockIn StockOut Bal
1 OpeningBal Maize 15400 0 15400
2 Tony Maize 5000 0 20400
3 ceaser Maize 5000 10 25390
4 Ek Maize 5400 100 30690
5 sam Maize 4500 150 35040
6 Tony Maize 4500 150 39390
7 sam Maize 6000 0 45390
8 OpeningBal Beans 5000 0 5000
9 ceaser Beans 0 1000 4000
10 Ek Beans 0 1000 3000
11 sam Beans 50000 0 50000
12 Tony Beans 0 4000 46000
13 sam Beans 0 5000 41000
14 Tony Beans 1000 2000 40000
15 OpeningBal Soybean 60400 0 60400
16 Ek Soybean 0 1000 59400
17 sam Soybean 0 1000 58400
18 Tony Soybean 0 2000 56400
19 sam Soybean 5440 0 61840
20 Tony Soybean 0 4000 57840
21 ceaser Soybean 0 4000 53840

thanks again for your help.
Much appreciated
Qnatz

Qnatz

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 16:14:28

Thanks, that makes it easier! See if the query below gives you the results you are looking for.


;with cte as
(
   select
      *,row_number() over (partition by Crop order by ID) N
   from
      tblReport
),
a as
(
   select
      Id, name, Crop, StockIn, Stockout,
      StockIn - StockOut as CalculatedBal
   from
      cte
   where
      N = 1
   
   union all
   
   select
      t.Id, t.Name, t.Crop, t.StockIn, t.StockOut,
      a.CalculatedBal + t.StockIn - t.Stockout as CalculatedBalBal
   from
      cte t
      inner join a on t.Id = a.Id+1 and a.Crop = t.Crop
)
select * from a order by Id;

Qnatz
Starting Member

4 Posts

Posted - 2011-06-11 : 17:14:06

if GOD, Jesus n Muhammed were not in the picture.....YO GOD.
Just worked perfect...after 4 months of hassling.......
Just off topic alito....
could you help me n advise me on how to be perfect...
i do alot of stuff,,, i graduated with a BSc Agric am a farmer
i teaching farming as well as market for my company.... i also head
the IT dept for which am doin this app.
Am a grafix consult who does movies, directing n production works
but am not so perfect in every thing i do....
when i do some alot of people are mouth dropped but i feel there's
some always missin....how can i be contented with my self coz few
guys here can do stuff i do..... actually i also do part time
lecturing to IT students especially on their course works...
Just some advise...like a parent...i missed that.
Thanx a bunch BRO

Qnatz

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 19:40:53

There is an error in the code that I had posted earlier - see changes below. That it worked for the sample data was just a coincidence.


;with cte as
(
   select
      *,row_number() over (partition by Crop order by ID) N
   from
      tblReport
),
a as
(
   select
      Id, name, Crop, StockIn, Stockout,
      StockIn - StockOut as CalculatedBal,N
   from
      cte
   where
      N = 1
   
   union all
   
   select
      t.Id, t.Name, t.Crop, t.StockIn, t.StockOut,
      a.CalculatedBal + t.StockIn - t.Stockout as CalculatedBal, t.N
   from
      cte t
      inner join a on t.N = a.N+1 and a.Crop = t.Crop
)
select * from a order by Id;

As for advice on becoming perfect, I am very unqualified to offer any such advice. If you like to improve your Database/SQL skills, start another thread asking for advice. I am sure many people on this forum who have far more experience and skills than I, would offer very useful suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources