Calculating Correct Stock - 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

Calculating Correct Stock

Author

Topic

croubekas
Starting Member

8 Posts

Posted - 2009-09-14 : 11:46:48

Dear all,

I have a small programming issue and I need your help.

I have a small warehouse and a typical issue of inflows/outflows for that warehouse on a daily basis.
The user inputs daily inflows as Unit_Code, Quantity, Price and records sales as outflows with Unit_Code, Quantity, Price.

The problem is I need the inflow-items to decrease as outflows occur on a per-entry basis.

Example:
User records inflows:
Code Quantity Price
Item_1 10 1.10
Item_1 20 1.08

After selling for example 15 units the remaining in the warehouse should show:
Code Quantity Price
Item_1 15 1.08.

In simple words I would like to use FIFO in selling items from the warehouse.

Is this feasable ?

Thank you tons!!
Chris.

System: MySQL 5.0.51a on Ubuntu.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 11:50:11

its feasible. but on what basis you determine order? there's no concept of first and last in table. also this forum is for sql server so solutions provided will be for sql server and may not work fine in mysql

croubekas
Starting Member

8 Posts

Posted - 2009-09-14 : 11:56:39

quote:
Originally posted by visakh16

its feasible. but on what basis you determine order? there's no concept of first and last in table. also this forum is for sql server so solutions provided will be for sql server and may not work fine in mysql

Good point!
The first/last record is determined on the date. Thus FIFO will use the oldest as "first entered" and will user that when selling to a client.

Thanks for the interest!!
C.

croubekas
Starting Member

8 Posts

Posted - 2009-09-14 : 12:27:42

Topic closed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:10:44

quote:
Originally posted by croubekas

Topic closed.

why? u managed to sort it out?

croubekas
Starting Member

8 Posts

Posted - 2009-09-14 : 13:47:20

No I didn't manage to solve it but I restated it correctly with a new topic name "FIFO Warehouse issue".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:56:42

Are you using sql 2005?

croubekas
Starting Member

8 Posts

Posted - 2009-09-14 : 14:00:51

Yes. I decided to switch to sql2005 as it is familiar to me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 14:14:27

ok. then here's a way



declare @alloc int

insert into sales
select pk,Code, Quantity, Price
from yourtable t
outer apply (select sum(quantity) as totalqty
             from yourtable
             where code=t.code
             and date <=t.date)t1
where isnull(t1.totalqty,0)< @totalqty
and code=@yourcode
order by date

select @alloc=sum(quantity)
from sales
where code=@code

delete t
from yourtable t
inner join sales s
on s.pk=t.pk
and t.code=@code


insert into sales
output inserted.pk into @inserted_pk
select top 1 pk,Code, @totalqty-@allocated, Price
from yourtable 
where Code=@code
order by date

update t
set t.Quantity=t.Quantity-@totalqty+@allocated
from yourtable t
join @inserted_pk i
on i.pk=t.pk
where i.code=@code


select * from yourtable

croubekas
Starting Member

8 Posts

Posted - 2009-09-15 : 03:55:41

Can you explain what pk is?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-15 : 04:36:54

pk - primary key

KH
[spoiler]Time is always against us[/spoiler]

croubekas
Starting Member

8 Posts

Posted - 2009-09-15 : 05:31:45

Thank you visakh16 & khtan for your help.
The code you provided is a little complicated for me but I will try to manage...

If someone could give me some insight or perhaps an "easier" solution, it would be appreciated.

C.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources