December 16, 2015 at 7:38 am
I'm trying to write a query to calculate my inventory ageing but am struggling to get it to work.
Appreciate any help.
This is my example with inventory going in and out of a warehouse:
itemid | date | qty
1 | 2009-06-01 | 10
1 | 2009-07-01 | -5
1 | 2009-08-01 | 5
1 | 2009-08-01 | -5
1 | 2009-09-01 | 10
1 | 2009-09-01 | -2
The result set should show:
The sale on 2009-07-01 was allocated to the purchase dated 2009-06-01
The sale on 2009-08-01 was allocated to the purchase dated 2009-06-01
This removes all stock from that date so it can be ignored now.
The sale on 2009-09-01 was allocated to the purchase dated 2009-08-01
So the ageing for the remaining inventory balance as of today should be.
date qtybal
2009-08-01 3
2009-09-01 10
I believe I need to use the row_number () over partition statement but have had no luck so far.
USE [tempdb]
GO
drop table InvAge
CREATE TABLE [dbo].[InvAge](
[Itemid] int NOT NULL,
[Doctype] varchar (10) NOT NULL,
[Location] varchar (10) NOT NULL,
[Dates] datetime NOT NULL,
[QTY] int NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [InvAge]
SELECT 1, 'Receipt', '10GEI', '2009-06-01', 10 UNION ALL
SELECT 1, 'Sale','10GEI','2009-07-01', -5 UNION ALL
SELECT 1, 'Transfer', '10GEI','2009-08-01', 5 UNION ALL
SELECT 1, 'Sale', '10GEI','2009-08-01', -5 UNION ALL
SELECT 1, 'Receipt', '10GEI','2009-09-01', 10 UNION ALL
SELECT 1, 'Sale', '10GEI','2009-09-01', -2;
with cte as(
select itemid, location, qty, dates, row_number() over (partition by itemid, location order by itemid, location) as rank from invage
)
SELECT a.rank, a.itemid, a.location, a.qty, a.dates from cte a
left join cte b on a.itemid=b.itemid and a.location = b.location and a.rank = b.rank-1;
December 16, 2015 at 8:46 am
This looks remarkably similar to:
December 16, 2015 at 9:10 am
Thanks.
I saw this post as well.
It is similiar as it demonstrates what the value of stock is using FIFO.
I need to know the ageing of my remaining stock balance.
With a balance of 5 items today, I need to know which ones are up to 30 days old and which ones are older etc.
December 22, 2015 at 6:58 am
Have it sorted now with the example from the above link.
September 10, 2017 at 3:56 pm
boettger.andreas - Tuesday, December 22, 2015 6:58 AMHave it sorted now with the example from the above link.
Do you mind sharing your query that got this to work?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy