Inventory Ageing in SQL

wibni · December 16, 2015, 11:35am

I'm using SQL Server 2012.
I'm trying to do a query which will calculate the inventory ageing in groups, i.e. <30 days, 31 to 60 days and > 60 days
My table looks like this:

I now have 3 items in stock.
Based on today's date 16/12/2015, the 1 item left over from the receipt on 20/10/1015 is 31 to 60 days old.
The 2 items from the transfer are less than 30 days old.

I've gotten as far as below SQL statement. This however is calculating the aging backwards based on todays date, i.e 1 item is < 30 days, 2 items are 31 to 60 days.
I need it to calculate the aging based on items coming in and going out, basically the same as FIFO.

Would appreciate any advice on how this can be achieved.

SELECT icv.ITEMNO AS ItemNumber, icv.LOCATION, SUM(icv.TRANSCOST) AS Cost, SUM(icv.QUANTITY) AS Quantity, icv.DATEBUS AS TransactionDate, DATEDIFF(dd,CONVERT(DATETIME, LEFT(icv.Datebus,4) + '-' + SUBSTRING(CONVERT(VARCHAR(8),icv.Datebus),5,2) + '-' + RIGHT(icv.Datebus,2)), getdate()) AS Ageing    
FROM ICIVAL AS icv INNER JOIN ICITEM AS ict ON icv.ITEMNO = ict.ITEMNO
WHERE icv.itemno ='101010260284' and icv.location = '10gei'
GROUP BY icv.LOCATION, icv.ITEMNO, icv.DATEBUS

sz1 · December 16, 2015, 1:19pm

Depending on how you want to group or look at the data?, you can use a simple CASE for aggregation.

SELECT [<30 Days] = CASE 
WHEN DATEDIFF(DAY, icv.Datebus, GETDATE()) < 30
THEN 1 ELSE 0 END,
[31 to 60 Days] = CASE
WHEN DATEDIFF(DAY, icv.Datebus, GETDATE()) >= 31
AND DATEDIFF(DAY, icv.Datebus, GETDATE()) < 60
THEN 1 ELSE 0 END,
[>60 Days] = CASE
WHEN DATEDIFF(DAY, icv.Datebus, GETDATE()) > 60
THEN 1 ELSE 0 END

wibni · December 16, 2015, 2:11pm

Thanks for your reply.
My SQL statement is wrong. I don't think I can use DATEDIFF and GETDATE.
My statement and your's will group the transactions based on today's date.
The result I'm looking for is to group my remaining qty of 3 into the aging groups based on when their were receipted into inventory.

If we look at this example:

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 should be
date qtybal
2009-08-01 3
2009-09-01 10

I need to do it with row_number over partition I believe but below statement does not work either.

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 dates,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;

jcelko · December 18, 2015, 6:48pm

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements.

You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). But you picked the worst possible display format! Why?

We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

in groups, i.e. <30 days, 31 to 60 days and > 60 days
My table looks like this: <<

We do not use the old Sybase CONVERT() string functions today.

And this table has no name, and no key?? Thanks! Here is my guess At your posting.

CREATE TABLE Inventory_Transactions
(item_name CHAR(6) NOT NULL,
trans_date DATE NOT NULL,
trans_type CHAR(8) NOT NULL
CHECK (trans_type IN ('receipt', 'usage', 'ransfer')),
trans_qty INTEGER NOT NULL
CHECK (trans_qty <> 0),
PRIMARY KEY (item_name, trans_date, trans_type)
);

INSERT INTO Inventory_Transactions
VALUES
('Item A', '2015-10-20', 'Receipt', 4),
('Item A', '2015-10-20', 'Usage', -2),
('Item A', '2015-11-18', 'Usage', -1),
('Item A', '2015-12-12', 'Transfer', 2);

I now have 3 items in stock. <,

No, you have only 'Item A' in stock. Look at the sample data.

And why are you handling dates as if they are in 1960's COBOL strings? We put a lot of work in ANSI X3H2 to get temporal data types and you throw it all out. We do not use the old Sybase/UNIX getdate() today; we have had CURRENT_TIMESTAMP for decades now.

Where is the rest of the DDL? Where is the other table in your query? We have to do more guessing for you. Here is a running total query:

SELECT item_name, trans_date, trans_type, trans_qty,
SUM (trans_qty) OVER (PARTITION BY item_name
ORDER BY trans_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS current_trans_qty_tot,
DATEDIFF (DAY, trans_date, CURRENT_TIMESTAMP) AS trans_age
FROM Inventory_Transactions
WHERE trans_date <= CURRENT_TIMESTAMP;

wibni · December 29, 2015, 6:46am

I was able to solve my problem with help from the below post.

jcelko - Please don't reply to my posts any more....