stale dated Inventory - Sap Business one(B1) - 9.2

Question

I have the below query working in SAP B1. but this caters age only from the last purchase date.

I want to have a report based on the date of purchase(and not the date of last purchased)

The Oitm table only gives the last purchased date.

Is there any other way to work around this?

Example: 10 pieces of a product is purchased in January, and till April we sold only 3 pieces, then in April we further purchased 5 Pieces. I want to have the age of 7 units that are left from January purchase and age of 5 pieces from April purchase in their respective age column.

The below report is giving me age of all the 12 units from the date of April.

SELECT distinct T0.ITEMCODE , T1.[ItemName], T4.[FirmName], T0.ONHAND as 'In Stock', 
    T0.[WhsCode], T0.[AvgPrice] * T0.[OnHand] AS "Total In Stock" ,
    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND END '<45 Days(Qty)',
    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND*T0.AVGPRICE END '<45 Days(Value)',
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END '45 to 90 Days(Qty)' ,
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.[AvgPrice] * T0.[OnHand] END '45 to 90 Days(Value)',
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 90 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 120 THEN T0.ONHAND END '90 to 120 Days(Qty)' ,
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 90 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 120 THEN T0.[AvgPrice] * T0.[OnHand] END '90 to 120 Days(Value)',
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 120 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 360 THEN T0.ONHAND END '120 to 360 Days(Qty)' ,
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 120 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 360 THEN T0.[AvgPrice] * T0.[OnHand] END '120 to 360 Days(Value)',
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 360 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 720 THEN T0.ONHAND END '360 to 720 Days(Qty)' ,
    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 360 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 720 THEN T0.[AvgPrice] * T0.[OnHand] END '360 to 720 Days(Value)',
    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 720 THEN T0.ONHAND END '>720 Days(Qty)',
    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 720 THEN T0.[AvgPrice] * T0.[OnHand] END '>720 Days(Value)'
    FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE
    INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD left join ibt1 t3
       on t3.itemcode = t0.itemcode and t3.whscode = t0.whscode
    INNER JOIN OMRC T4 ON T1.[FirmCode] = T4.[FirmCode]
    WHERE
    T0.ONHAND>0