# Inventory Ageing in SQL

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:

Item No | Trans Date | Doctype | Quantity
Item A | 20/10/2015 | Receipt | 4
Item A | 20/10/2015 | Usage | -2
Item A | 18/11/2015 | Usage | -1
Item A | 12/12/2015 | Transfer| 2

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``````

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``````

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;``````

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;

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