Aged Inventory calculations

[Jcfx]

Sql Database, crystal 9. Two tables. Main is ITEM table, secondary is the Order Table. Linked by Item ID.

Output expected is:
ItemID QtyOH MonthsSinceLastTransaction
123456 5 8
32456 4 3
548789 1 0

The current formula I am using for the aging is as follows:
datediff("m",currentdate,maximum({ORDERS.CLOSED},{ITEM.PARTID}))

This works perfectly for all the parts that have a transaction since the date we started using the system. Unfortunately we have a large number of parts that have had no activity at all. The Item table does not retain a permanent date stamp to indicate when the part was first placed in the inventory, but I can use the date we went live as a base.

Here is where my mind turned to jelly, must be Friday I can not come up with a working if then else formula that will look to see if there have been any transactions against the part, if there were, calculate the age from the transaction date, if there were not, calculate the age of the part from 6-28-2004.

Probably really simple, but its just not clicking for me.

Julie Cox
CR Pro Version 9.2

 

[kskid]

Try this

if isnull({orders.closed}) then
datediff("m",currentdate,date(2004,6,28))
else
datediff("m",currentdate,maximum({ORDERS.CLOSED},{ITEM.PARTID}))

 

[Jcfx]

Thank you so much, that did the trick.

And definetly solidified my thought process.



Julie Cox
CR Pro Version 9.2