Recurring values
-
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
b 10/6/2005 30 20
b 25/6/2005 15 22
Issue have the foloowing details
partno date iquantity irate sval lval
a 14/1/2005 10 10
a 15/5/2005 20 22
a 1/8/2005 20 22
b 11/7/2005 10 20
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 calcucated result in “lVal” and ’sval’ column
iquantity(irate)+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.
and also if the date difference between dates of two tables are greater than 365 days then they have to stored in the column lval otherwise in the column sval. And this should be repeated to all items.
-
Have you tried anything on your own?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I find difficult where to start from. I wrote the query to retrieve the first item of each type of partno. Then dont know how to proceed.
select partno
from receive a
where partno = ( select top 1 partno
from issue b
where b.partno = a.partno
)
-
Chaitra - please understand that, from your perspective, what you've presented is the core of the problem that you are currently trying to solve. But that problem resides in a context that is 'intuitively obvious' to you. However, since you are reaching out to a world of experience, we don't know the set of assumptions you are working under. Yes, we need to know what you've already told us, but the ultimate solution that works is dependent on the context that you know and we don't.
It 'sounds' like an inventory system. Inventory systems generally work in two ways - LIFO (last in, first out) and FIFO (first in, first out). You seem to have implied FIFO, but it's not clear from the values used. Also, it seems that you also have an order fulfillment component. Is that true? Or is this purely an accounting exercise? Or purely a subaccounting (i.e., inventory) only?
What volumes? onesy twosy? tens? hundreds? thousands? How many records in the tables you reference? What works well for hundreds of records may die on the vine when there are millions of records.
To quote the flyleaf from a volume of humorous poems written by Ogden Nash, 'the road to hell is paved with good intentions'. We'd like to have a better destination than that.
-
Ya 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. first issuing 7 items from the remaining RECIEVE and then another 8. So we are left with 2 recieved items which will be there until next issue.
This has to be repeated for the Item B and so on.
-
Very nicely stated and explained, Steve.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you for the detailed explanation... I have just a couple more questions , if you don't mind...
Can you post the table of results for the "A" items?
In the formula 18(18)+18(18-15), do the parentheses indicate multiplication? Is the answer to this formula = 378 ??? If not, then what?
Could you explain why 45 means the value must be (2(20)+2(20-15))... sorry to be thick on this but I don't know enough about what that must be.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ya, () represents multiplication.
Could you explain why 45 means the value must be (2(20)+2(20-15))... sorry to be thick on this but I don't know enough about what that must be.
For the item A previously 2 items are remaining in the stock. So when we issue 45 items first we have to issue the remaining items. But the rate of remaining items are different from the later arrived one. In the example you could see that the rate of remaining items is 15 but later one is 20. So only seperate calculation. Actually main problem is here only I am facing. Could not figure out how to get this type of result. And also the difference between currently issuing date and remaining items is more than 365 days so put the resulting value in LongTem column.
RECIEVE TABLE
PARTNO RDATE RRATE/UNIT rQUANTITY Remaining
A 10/10/2006 15 20 2 --(20-18)
A 10/10/2007 20 50 7--(50-43) 2 items have taken from previous remaining items
A 1/11/2006 18 10 2--(10-8) 7 items have taken from previous remaining items
B 10/12/2006 15 30
B 13/2/2007 25 15
ISSUE TABLE
PARTNO IDATE IRATE/UNIT iQUANTITY ShortTerm LongTerm
A 10/5 /2007 18 18 378
A 1/12/2007 20 45 860 50
A 13/2/2007 20 15 312
B 13/2/2007 20 20
B 1/5/2007 20 25
(312) is because the rate of remaining 7 items and next 8 items are different,
7(20)+(7(20-20)=140 and 8(20)+(8(20-18)=172, Since the difference of date for both is less than 365 days i have put their sum in ShortTerm column
-
Let's see if I have this right:
Goals:
1. Correct calculation of: a. Inventory on hand, b. short term value fulfilled, and c. long term value fulfilled
2. Correct updating of database to reflect calculation (Remaining column)
Inputs:
1. Inventory is received on given date, with given unit value. Each day's receipt must be recorded separately if the value OR the date received is different.
2. Order fulfillments against inventory is received on a given date, and the counts in inventory are relieved according to FIFO (explained in earlier post) rules.
Outputs:
1. Calculated monetary value of inventory shipped out as order fulfillment. Depending on the age of the inventory, the monetary value is recorded as either short term or long term income (hence the discussion of 365 days consideration).
2. Reduce the inventory used in the calculation so it's not used twice.
Calculations:
t = total number of units involved in calculation. subscripts represent partial orders (eg, t = t1 + t2 + ... + tn)
m = unit monetary value. m1 is associated with t1, m2 with t2, etc.
Taking the example given above, where t = 45, we have t1 = 2 and t2 = 43. m1 = 15 and m2 = 20. So the total value can be described as:
V = t1(m1) + t2(m2). But it can also be described as t(m1) + t2(m2-m1), which is how Chandra is describing it.
Let V = the total value of the issued inventory (if all inventory had same unit value v, then V = Tv.
But, note also that V = Vst + Vlt, where Vst = short term issue and Vlt = long term issue.
Depending on the dates, compared to 'today', if all dates are less than a year old, V = Vst and Vlt=0, which is simple. Otherwise we have to store two nonzero values.
The complication which makes this question interesting is that we MAY have to consider multiple database records on input, and may have to update a variety of fields as a result of the calculation. It is not clear, in advance, how many records will be considered on input, nor how many fields in how many records will be updated on output.
SIMPLE QUESTION: when inventory is received, is the quantity remaining column null, zero or equal to the quantity received?
NOT SO SIMPLE QUESTION: what's the data structure? DDL is now critical! Knowing what fields are in the table makes a huge difference in how to propose a solution.
QUESTION (may be easy, maybe not): Is there a separate record created for each row of received inventory for each issuing of inventory? You show 3 inventory records for A, with three different dates (and note that the illustration does NOT show inventory in date order! is that a typo?) Or is it sufficient to show one row of issued inventory, reflecting the total short term and long term value of issued inventory?
-
And, sorry, I'm still not getting it... (2(20)+2(20-15)) = 50... not 45... True?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oh shoot... although the answer to the formula above may have originally been posted incorrectly, I think I finally get this process that you're looking for... Not sure what the answer to such formula's is actually supposed to represent, but I think I get it... this'll take more than a minute, though...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heh... I just noticed that the dates in the example data are in the "dmy" format which means the examples posted are not in the correct date order and the example outputs are wrong... but I still get it...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply