How to convert Dynamics NAV Lead Time Calculation to a number

Inventory has always been very near and dear to my heart every since I started my APICS courses at Conestoga College 20 years ago and so I’m always excited when people push me a bit further with the Vox BI cubes.  Recently I was asked to create an automatically calculating safety stocked based on a time frame and believe it or not the most complicated part was converting the leadtime into a number.  Thanks to Sbolton (http://community.dynamics.com/nav/f/34/t/42717) I was able to make it work.  I think there is probably a better way of handling it but the TRIM functions didn’t seem to work.

case when RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(dbo.[Cronus Canada Inc_$Item].[Lead Time Calculation], CHAR(1), ‘?ContactAdmin’), CHAR(2), ‘D’), CHAR(3), ‘?ContactAdmin’), CHAR(4), ‘W’),1) = ‘D’ then

ROUND(REPLACE(dbo.[Cronus Canada Inc_$Item].[Lead Time Calculation], CHAR(2), ”) * (dbo.VOX_USAGEMAX.[Daily Max] – dbo.VOX_USAGEMAX.[Daily Average]), 0)

when RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(dbo.[Cronus Canada Inc_$Item].[Lead Time Calculation], CHAR(1), ‘?ContactAdmin’), CHAR(2), ‘D’), CHAR(3), ‘?ContactAdmin’), CHAR(4), ‘W’),1) = ‘W’ then

ROUND(REPLACE(dbo.[Cronus Canada Inc_$Item].[Lead Time Calculation], CHAR(2), ”) * 7 * (dbo.VOX_USAGEMAX.[Daily Max] – dbo.VOX_USAGEMAX.[Daily Average]), 0)

when RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(dbo.[Cronus Canada Inc_$Item].[Lead Time Calculation], CHAR(1), ‘?ContactAdmin’), CHAR(2), ‘D’), CHAR(3), ‘?ContactAdmin’), CHAR(4), ‘W’),1) = ‘?ContactAdmin’ then

0

else 0 end