iterating values in sql
-
hi, I have two tables reciept and issue. Reciept have the foloowing details partno date rquantity rrate a 10/1/2005 50 15 a 15/4/2005 10 22 a 11/7/2005 40 20 Issue have the foloowing details partno date iquantity irate value a 14/1/2005 10 10 a 15/5/2005 20 22 a 1/8/2005 20 22 what i have to do is take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column iquantity(irate-rrate) example, first we have to subtract 10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50 20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440 20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440 Can anyone tell to solve this?
Chaitra N
-
hi, I have two tables reciept and issue. Reciept have the foloowing details partno date rquantity rrate a 10/1/2005 50 15 a 15/4/2005 10 22 a 11/7/2005 40 20 Issue have the foloowing details partno date iquantity irate value a 14/1/2005 10 10 a 15/5/2005 20 22 a 1/8/2005 20 22 what i have to do is take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column iquantity(irate-rrate) example, first we have to subtract 10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50 20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440 20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440 Can anyone tell to solve this?
Chaitra N
n_gchaitra wrote:
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column
How about trying to itrate this from Front-End and thereby updating the column in the DB accordingly?
-
n_gchaitra wrote:
take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column
How about trying to itrate this from Front-End and thereby updating the column in the DB accordingly?
I thought of using asp.net with vb script. But I think then also we need to write appropriate sql statements only.
Chaitra N
-
hi, I have two tables reciept and issue. Reciept have the foloowing details partno date rquantity rrate a 10/1/2005 50 15 a 15/4/2005 10 22 a 11/7/2005 40 20 Issue have the foloowing details partno date iquantity irate value a 14/1/2005 10 10 a 15/5/2005 20 22 a 1/8/2005 20 22 what i have to do is take the quatity from issue table subtract from the rquantity of reciept table until rquatity becomes 0 then we have to store some calucated result in "Value" column iquantity(irate-rrate) example, first we have to subtract 10 from the 50, the remainder is 40 ,store 10(10)+10(10-15)=50 20 from the 40, the remainder is 20,store 20(22)+20(22-22)=440 20 from the 20, the remainder is 0,,store 20(20)+20(22-20)=440 Can anyone tell to solve this?
Chaitra N
What you are looking for is a cursor (at least if you are using sql server). cursors allow you to iterate through a set of results returned by a select statement and perform any number of sql actions on each iteration. Be careful though as SQL is designed for Set based actions not iterative procedures so the performance overhead against a properly designed query (if one would be possible for what you want to do) is huge. Just google for 'SQL Cursor' and you should find what you want. Jonathan
-
What you are looking for is a cursor (at least if you are using sql server). cursors allow you to iterate through a set of results returned by a select statement and perform any number of sql actions on each iteration. Be careful though as SQL is designed for Set based actions not iterative procedures so the performance overhead against a properly designed query (if one would be possible for what you want to do) is huge. Just google for 'SQL Cursor' and you should find what you want. Jonathan
I dont know whether i have to use cursor or simply a stored prcedure. This is FIFO inventory system. Recieved and Issue items will be in different table say, RECIEVE & ISSUE. Each item may be recieved and issued many times. Each time may be with different rate,quantiy and date. For example let us considere the following tables, RECIEVE TABLE PARTNO RDATE RRATE/UNIT rQUANTITY A 10/10/2006 15 20 A 10/10/2007 20 50 A 1/11/2006 18 10 B 10/12/2006 15 30 B 13/2/2007 25 15 ISSUE TABLE PARTNO IDATE IRATE/UNIT iQUANTITY A 10/5/2007 18 18 A 1/12/2007 20 45 A 13/2/2007 20 15 B 13/2/2007 20 20 B 1/5/2007 20 25 Ultimately i have to get the following table PARTNO IDATE IRATE/UNIT iQUANTITY ShortTerm LongTerm Here I want to find the LongTerm Value and Shorttem value depending on the difference in date between idate and rdate of each item. This is to find the gain or loss of the item in that particular date. The formula is iquatity(irate)+iquatity(irate-rRate),if the date difference between recieve and issue is >365 day then the calculated value should be place in LongTerm value column else in the Short term value column First we take A, the first issue quanitity is 18, but the no of quantity recieved was 20 so the value will be 18(18)+18(18-15). Now the remaining no of items present in first recieved is 2.The difference between issue date 10/5 /2007 and recieve date 10/10/2006 is less than 365 days so has to be placed in ShortTerm column. So when calculating the amount of second issue first we have issue remaining 2, then go for second recieve. Here issue quantity is 45. so, the value must be (2(20)+2(20-15)). The difference between issue date 1/12/2007 and recieve date 10/10/2006 is more than 365 days so has to be placed in LongTerm column. Then remaining 43 items, the value has to be (43(20)+(43(20-20). The difference between issue date 1/12/2007 and recieve date 1/12/2007 is less than 365 days so has to be placed in ShortTerm column. Now the remaining items in Recieve table for A item is 7+10. Now we have to calculate the next A item as we did for second. i.e. fir