Problem to fetch data using loop in SP

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

Problem to fetch data using loop in SP

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-02 : 01:52:21

Hello Friends,

I have a question please help me.

currently this scenario is working for me.

I have displayed the data based upon item description.

Item Desc Total Qty
===========================
Item A 100 ID Qty Rest Qty
=========================
10 40 ?
20 80 ?

Im facing problem to retriveing data for Rest Qty coloumn.
I have created stored procedure to do this stuff.

What i need suppose Total qty < Last ID(i.e 20) Qty(80) then it should simply display 80 t0 the next column, then it will look for previous ID(i.e. 10) Qty(40),
here in that case Qty is 40 but in total of both ID (i.e. 20 and 10) Quantity(i.e. 80 and 40) is 120 which is more then Total qty(i.e. 100), hence in that case it should subtract Total qty(100) - Qty(80) = 20 so it should dispaly 20 next to ID(10) column of Rest Qty.

Means evertime Total qty should compare with both the IDs Qty(startting with top most ID)
and see whether its greater or not and display it to next col of Rest Qty.

First Scenario
===============
1) 100 > 80 hence 80 comes to Rest Qty
2) 100 > 40(but since 80+40 which is more then 100 hence in that case its should be 100-80 = 20 should comes next to the coloumn)

I need to do this by using loop condition in stored procedure, i have
inserted all data in temp table from main table.

Please guide me, its very urgent.

Regards,
Av

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-02 : 02:26:48

Can you please provide a larger table (5-6 rows at least) of what you have in the table already and what you expect from the Qty column? And if you could put [ code ] tags around your table to preserve the formatting that would be great. Your explanation is slightly confusing (at least to me). That being said: if you want to start looking for yourself search this forum for the keywords "running total"...I believe this is what you're doing.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-02 : 03:46:45

Item Desc TotalQty ID Rate GRPOQty RestQty
K20 DS200 Item1 28 903 48.6 40 18
K20 DS200 Item1 28 903 48.6 40 18
K20 DS200 Item1 28 917 48.6 7 7
K20 DS200 Item1 28 917 48.6 7 7
K20 DS200 Item1 28 930 47.3 3 3
K20 DS200 Item1 28 930 47.3 3 3

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-02 : 03:47:27

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 04:11:24

sorry you're not quite clear. can you explain rules for getting results as 18,7 etc in above case?

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-02 : 04:39:07

Total Qty is getting = 28

GRPO Qty is getting = 40, 7, 3

So first Total quantity 28 should compare with last GRPO qty i.e 3,
so its less then Total qty, so its should display in rest qty.

Now total qty 28 will compare with second last GRPO qty i.e. 7,
so now last GRPO qty 3 + second last GRPO qty 7 = 10, so 10 is also less then Total qty 28, hence it will display 7 next to Rest qty col.

Now total qty 28 will compare total of all three GRPO quantity
7+3+40, but its greater then Total qty 28, hence 28 - 10 = 18 we need to keep it there.

for better understand copy the above output to excel.

Please help me i need it urgently, if u think we can chat then i vl be avialable at abhitkumar@gmail.com.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:01:15

quote:
Originally posted by abhit_kumar

Total Qty is getting = 28

GRPO Qty is getting = 40, 7, 3

So first Total quantity 28 should compare with last GRPO qty i.e 3,
so its less then Total qty, so its should display in rest qty.

Now total qty 28 will compare with second last GRPO qty i.e. 7,
so now last GRPO qty 3 + second last GRPO qty 7 = 10, so 10 is also less then Total qty 28, hence it will display 7 next to Rest qty col.

Now total qty 28 will compare total of all three GRPO quantity
7+3+40, but its greater then Total qty 28, hence 28 - 10 = 18 we need to keep it there.

for better understand copy the above output to excel.

Please help me i need it urgently, if u think we can chat then i vl be avialable at abhitkumar@gmail.com.

Regards

ok.in that case whats your primary key (unique valued column) of the table?

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-02 : 05:18:29

Itemcode or Item Description is the unique key of my table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:22:04

quote:
Originally posted by abhit_kumar

Itemcode or Item Description is the unique key of my table.

its not unique as per your sample data. you've several rows with same value for them (6 rows with value K20 DS200 in example)

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-02 : 05:42:10

acyually i need the data based upon the Item descrition or item code.

Item description is displayiing 6 times because Item Table joins with GRPO Table and GRPO tabel have different IDs i.e 903, 917,930 (These ID is also unique for GRPO qty ) based upon the items.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:44:58

quote:
Originally posted by abhit_kumar

acyually i need the data based upon the Item descrition or item code.

Item description is displayiing 6 times because Item Table joins with GRPO Table and GRPO tabel have different IDs i.e 903, 917,930 (These ID is also unique for GRPO qty ) based upon the items.

nope you still have two records for 903,917 etc. you need to identify a unique valued column to define first and last in a group

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources