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