December 2, 2010 at 12:29 pm
Hello everyone, sorry I couldn't come up with a better title.
I have ran into a snag and am looking for some SQL help.
My Sql is ok at best. I have been able to get pretty far with it.
But I am sure there are better ways to go about what I have been doing.
I have a solution to what I want to do, but It involves a frontend console app.
I am looking for a pure SQL method, if it exists, this is purely for my educational basis.
Just wish to improve my knowledge.
What I want to do is Generate a Cost depending on the date and time.
Let me elaborate, Case is that there is a First In First Out Pricing model.(I think that i what it is called)
Product A costs 5$ and has 3Pcs Stock
A new inventory comes in 5Pcs of Product A with a new Price of 6$.
So the new Price of Product A is ((5*3) + (6*5)) / (5+3).
Now What I want to do is get the Price of Product A say a year ago.
Now rather that Resolve the the Equation to work backwards (NewCost = (OldCost*OldStock + NewPrice*NewQty) / (OldStock+NewQty)), I think it is easier to work from earlier Date. At the End of The Financial Year a SnapShot of the Inventory is taken.
So What I did was to use that as the Opening Cost and Opening Stock and then work in Incoming stock and calculate the Cost at that time. But I did it with the Front End working with the DB Backend.
to use the formula it would require working out the Stock right before the purchase. (Thankfully this is saved in the Purchase tables).
PsuedoCode :
double OpeningCost = Select Cost From SnapShots Where Year = '2009' and ProductID = 123
int OpeningQuantity = Select Qty From SnapShots Where Year = '2009' and ProductID = 123
Next = Select Price, Qty, RStock From Purchase Where PDate Between '20090101' and '20090601' and ProductID = 123
-- 20090601 being user input
-- Price Being the Incoming Purchase Price
-- Qty Being the number of Pcs Incoming
-- Rstock being the Stock right before the Purchase.
double x = 0;
Next.Read();
x = ((OpeningCost * OpeningQuantity) + (Next.Price * Next.Qty) / (OpeningQuantity + Next.Qty));
While (Next.Read)
x = (x * Next.RStock) + (Next.Price * Next.Qty) / (Next.RStock + Next.Qty));
Print x;
I know I am supposed to Provide Create Table Scripts, but I don't have the data, this is something I helped a friend with, I was curious if it can be done in Sql.
Sorry for the long read, any help is appreciated
Thanks in Advance.
Peace.
December 2, 2010 at 12:32 pm
You can definitely keep point-in-time pricing in an SQL database. You'd just have a table with, for example, and item ID and a price and an effective date. Join that to your inventory table, which would have item ID, quantity, and entry date, and you can easily find how many you had at any given time, and what price they should have.
I'm not clear on your formula. What's it for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 2, 2010 at 12:43 pm
GSquared (12/2/2010)
You can definitely keep point-in-time pricing in an SQL database. You'd just have a table with, for example, and item ID and a price and an effective date. Join that to your inventory table, which would have item ID, quantity, and entry date, and you can easily find how many you had at any given time, and what price they should have.I'm not clear on your formula. What's it for?
I don't really know it was my friends formula, I was just intrigued if it is possible to do in SQL without a frontend.
So what you are saying is do something like.
SELECT SUM((x * Next.RStock) + (Next.Price * Next.Qty) / (Next.RStock + Next.Qty)) FROM SnapShots INNER JOIN Purchase on Purchase.ProductID = SnapShots.ProductID WHERE Year = '2009' and PDate Between '20090101' and '20090601' and ProductID = 123
But what about x? how do I save x before I do this?
I think that won't work but something like that. What Method would I require. As I said my sql is ok at best.
December 2, 2010 at 1:07 pm
Hmm.
Since I don't have the data can anyone clarify if this would work?
@DECLARE @Avg money
Select Top 1 @Avg = ((OpeningCost*OpeningStock) +(Price * Qty) / (RStock + Qty)) FROM SnapShots JOIN Purchase on Purchase.ProductID = SnapShots.ProductID WHERE Year = '2009' and ProductID = 123 and PDate Between '20090101' and '20090601';
Select @Avg =(@Avg * RStock) + (Price * Qty) / (RStock + Qty)) FROM Purchase WHERE PDate Between '20090101' and '20090601' and ProductID = 123;
December 2, 2010 at 1:22 pm
OK It seems that the code will work I tried to set up tables and all to just try to see if such a query is possible. this opens a whole new understanding of sql for me.
It seems just making the post made me work for it. Hehe.
Thanks for the help.
December 2, 2010 at 2:52 pm
This is very similar, if not identical to a sql challenge posted a while back. check out the discussion related to it, I think you'll find some good tips:
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
December 2, 2010 at 3:32 pm
weitzera (12/2/2010)
Thanks will check it out.
Is there a better method then Top 1 for this solution, is this the only possible solution?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply