(ask) query SQL about my stock list of items
-
Hi all,
I am a Newbi DBA and I have a case in my job.
the case look like this;
i have a table of my stock list items
CREATE TABLE [tbStockList] (
[item] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_transaction] [datetime] NULL ,
[amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
insert into tbStockList values ('a','2010-01-01',10)
insert into tbStockList values ('a','2010-02-01',-5)
insert into tbStockList values ('a','2010-03-01',10)
insert into tbStockList values ('b','2010-01-01',1)
insert into tbStockList values ('b','2010-02-01',-1)
insert into tbStockList values ('b','2010-03-01',1)
insert into tbStockList values ('c','2010-01-01',100)
insert into tbStockList values ('d','2010-01-01',1000)
insert into tbStockList values ('d','2010-02-01',1000)
insert into tbStockList values ('d','2010-03-01',-500)
insert into tbStockList values ('d','2010-04-01',1000)
note;
i. i am used method FIFO (First In First Out)
ii. if data in field amount sign minus (-) the meaning is the item was sold, else the item was bought
i want to know, the result is look like;
the details of my case is;
item a
at 01-01-2010 (mm-dd-yyyy), get in 10 unit ,
at 02-01-2010 (mm-dd-yyyy), get out 5 unit ,
at 03-01-2010 (mm-dd-yyyy), get in 10 unit,
the meaning is
by FIFO method at my store, we have final available stock 15 unit of item a.
the result is : 15 unit are come from at 01-01-2010 (5 unit) and 03-01-2010 (10 unit)
item b
at 01-01-2010 (mm-dd-yyyy), get in 1 unit ,
at 02-01-2010 (mm-dd-yyyy), get out 1 unit ,
at 03-01-2010 (mm-dd-yyyy), get in 1 unit,
the meaning is
by FIFO method at my store, we have final available stock 1 unit of item b.
the result is : 1 unit are come from at 03-01-2010 (1 unit)
item c
at 01-01-2010 (mm-dd-yyyy), get in 100 unit
the meaning is
by FIFO method at my store, we have final available stock 100 unit of item c.
the result is : 100 unit are come from at 01-01-2010 (100 unit)
item d
at 01-01-2010 (mm-dd-yyyy), get in 1000 unit ,
at 02-01-2010 (mm-dd-yyyy), get in 1000 unit ,
at 03-01-2010 (mm-dd-yyyy), get out 500 unit,
at 04-01-2010 (mm-dd-yyyy), get it 1000 unit
the meaning is
by FIFO method at my store, we have final available stock 15 unit of item a.
the result is : 2500 unit are come from at 01-01-2010 (500 unit) and 02-01-2010 (1000 unit) and 04-01-2010 (1000 unit)
Please let me know the query, thank's
-
I'll be the first to ask... is this a homework problem? My apologies if it isn't, it just has that feel.
That question aside, I'm not sure what you're looking for as your results. It looks like you're needing a monthly total of the available stock, but you have months missing from your results that I don't know why they are missing. Please expand on what the results are showing.
If what you're needing is a running total, I recommend checking out this excellent article by Jeff Moden
Solving the Running Total and Ordinal Rank Problems (Rewritten)[/url]
-- Kit
-
Please explain the business rules on how to get your expected result.
Issues I'm struggling with:
- Item a starts with an amount of 10, but it shows an amount of 5 in your result for 2010-01-01
- Item a has two result set, but item b only has one. But both have three entries in your sample data following the same pattern
- item d doesn't match either
You might want to google for "Phil Factor Challenge Stock Exchange". You'll find a challenge covering pretty much the very same subject with some high performance solutions. And if you need a FiFo related solution: there is a challenge for that, too.
I'd recommend you study the solutions and apply it to your specific scenario. If you get stuck, post back waht you've tried so far and I'm sure we'll be able to help you.
-
Kit G (12/29/2010)
I'll be the first to ask... is this a homework problem? My apologies if it isn't, it just has that feel.That question aside, I'm not sure what you're looking for as your results. It looks like you're needing a monthly total of the available stock, but you have months missing from your results that I don't know why they are missing. Please expand on what the results are showing.
If what you're needing is a running total, I recommend checking out this excellent article by Jeff Moden
Solving the Running Total and Ordinal Rank Problems (Rewritten)[/url]
Hi Kid G, thank's to your attentions
It is my job to show the report to management at my office. I need a monthly total of the available stock, base on date transaction. let me show details example to you;
item a
at 01-01-2010 (mm-dd-yyyy), get in 10 unit ,
at 02-01-2010 (mm-dd-yyyy), get out 5 unit ,
at 03-01-2010 (mm-dd-yyyy), get in 10 unit,
the meaning is
by FIFO method at my store, we have final available stock 15 unit of item a.
the result is : 15 unit are come from at 01-01-2010 (5 unit) and 03-01-2010 (10 unit)
item b
at 01-01-2010 (mm-dd-yyyy), get in 1 unit ,
at 02-01-2010 (mm-dd-yyyy), get out 1 unit ,
at 03-01-2010 (mm-dd-yyyy), get in 1 unit,
the meaning is
by FIFO method at my store, we have final available stock 1 unit of item b.
the result is : 1 unit are come from at 03-01-2010 (1 unit)
item c
at 01-01-2010 (mm-dd-yyyy), get in 100 unit
the meaning is
by FIFO method at my store, we have final available stock 100 unit of item c.
the result is : 100 unit are come from at 01-01-2010 (100 unit)
item d
at 01-01-2010 (mm-dd-yyyy), get in 1000 unit ,
at 02-01-2010 (mm-dd-yyyy), get in 1000 unit ,
at 03-01-2010 (mm-dd-yyyy), get out 500 unit,
at 04-01-2010 (mm-dd-yyyy), get it 1000 unit
the meaning is
by FIFO method at my store, we have final available stock 15 unit of item a.
the result is : 2500 unit are come from at 01-01-2010 (500 unit) and 02-01-2010 (1000 unit) and 04-01-2010 (1000 unit)
please, help me....Thank's
-
LutzM (12/29/2010)
Please explain the business rules on how to get your expected result.Issues I'm struggling with:
- Item a starts with an amount of 10, but it shows an amount of 5 in your result for 2010-01-01
- Item a has two result set, but item b only has one. But both have three entries in your sample data following the same pattern
- item d doesn't match either
You might want to google for "Phil Factor Challenge Stock Exchange". You'll find a challenge covering pretty much the very same subject with some high performance solutions. And if you need a FiFo related solution: there is a challenge for that, too.
I'd recommend you study the solutions and apply it to your specific scenario. If you get stuck, post back waht you've tried so far and I'm sure we'll be able to help you.
hi LutzM, thank's of your attentions.
I need a monthly total of the available stock, base on date transaction. let me show details example to you;
item a
at 01-01-2010 (mm-dd-yyyy), get in 10 unit ,
at 02-01-2010 (mm-dd-yyyy), get out 5 unit ,
at 03-01-2010 (mm-dd-yyyy), get in 10 unit,
the meaning is
by FIFO method at my store, we have final available stock 15 unit of item a.
the result is : 15 unit are come from at 01-01-2010 (5 unit) and 03-01-2010 (10 unit)
item b
at 01-01-2010 (mm-dd-yyyy), get in 1 unit ,
at 02-01-2010 (mm-dd-yyyy), get out 1 unit ,
at 03-01-2010 (mm-dd-yyyy), get in 1 unit,
the meaning is
by FIFO method at my store, we have final available stock 1 unit of item b.
the result is : 1 unit are come from at 03-01-2010 (1 unit)
item c
at 01-01-2010 (mm-dd-yyyy), get in 100 unit
the meaning is
by FIFO method at my store, we have final available stock 100 unit of item c.
the result is : 100 unit are come from at 01-01-2010 (100 unit)
item d
at 01-01-2010 (mm-dd-yyyy), get in 1000 unit ,
at 02-01-2010 (mm-dd-yyyy), get in 1000 unit ,
at 03-01-2010 (mm-dd-yyyy), get out 500 unit,
at 04-01-2010 (mm-dd-yyyy), get it 1000 unit
the meaning is
by FIFO method at my store, we have final available stock 15 unit of item a.
the result is : 2500 unit are come from at 01-01-2010 (500 unit) and 02-01-2010 (1000 unit) and 04-01-2010 (1000 unit)
please, help me....Thank's
-
Did you have a chance to search for the articles I pointed you at?
I'm sure there is a solution that'll exactly match your requirement.
-
Take a look here
http://ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem
IIUC , you only need to take it as far as the "LastPartialStock" subquery
-
hi.....
i have read your pointer article...., but i am still not understand to be implement into my case.....
would you guide me , to solve my case ?
thank's
-
Try this one which describes the code i wrote.
-
hi dave,
i have try your script at the point article. but the result still not same on my case.
would you write the query base on my case? thank's
regards,
newbi......
-
Hi ,
im not going to give you the answer here ,
but answer me this (in SQL code) :
Can you tell me how much stock you have in your warehouse right now ?
-
hi Dave,
my item stock is about 5000 items, thank's for your help
this is my script for your answer:
select count(distinct item) from tbStockList
-
Ok , so you have 5000 lines of stock,
how would you calculate the amount of stock for each line ?
-
Dave Ballantyne (12/31/2010)
Ok , so you have 5000 lines of stock,how would you calculate the amount of stock for each line ?
hi dave, happy new year....
first , i am create a view to know the last stock at my storage
SELECT ItemLookupCode AS item, SUM(Quantity) AS jumlah
FROM dbo.tb_StockList
GROUP BY ItemLookupCode
HAVING (SUM(Quantity) > 0)
second, i am create a view to know every bought item which in to my storage
SELECT TOP (100) PERCENT a.ItemLookupCode, a.Quantity, a.DateTransferred
FROM dbo.tb_StockList AS a INNER JOIN
dbo.vw_kur_onhand AS b ON a.ItemLookupCode = b.item
WHERE (a.Quantity > 0)
ORDER BY a.ItemLookupCode, a.DateTransferred DESC
would you help me, to solve my case?
thank's
-
Happy new year to you too,
The key is now to find the first/earliest 'IN' stock movement , that still has stock remaining in the warehouse. Since you are operating in a FIFO fashion then when you know this point , you know that 100% of each subsequent stock movement in is still remaining in the warehouse.
To get back to the original links that i gave you, specifically ..
http://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/ , does a real good job of breaking this down and how my query works in a performant fashion.
I would urge you to re-read that article , as this would seem to me be exactly what you would require.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply