DECLARE @Service_Level   FLOAT

SET @Service_Level	=	0.95


SELECT 
		A.SKU,
		dbo.fnINV_GET_NORMSINV(@Service_Level) as  SAFETY_FACTOR,
		C.STDDEV,
		100 as ORDER_QTY,
		A.COST,
		B.Lead_Time,
		@Service_Level	as SERVICE_LEVEL,
		dbo.fnINV_GET_NORMSINV(@Service_Level)*ISNULL(C.STDDEV,0)*ISNULL(A.COST,0)*SQRT(ISNULL(B.Lead_Time,0)/30) as SS,
		(100 /2)*ISNULL(A.COST,0)+(dbo.fnINV_GET_NORMSINV(@Service_Level)*ISNULL(C.STDDEV,0)*ISNULL(A.COST,0)*SQRT(ISNULL(B.Lead_Time,0)/30))as INV_COST 
FROM IO_CM_PART_MASTER		A(NOLOCK),
	 IO_CM_PART_PLANNING	B(NOLOCK),
(
	SELECT SKU,STDEV(ISNULL(total_forecast,0)) as STDDEV 
	FROM VIO_DF_FORECAST_DATA(NOLOCK)
	GROUP BY SKU
)as C
WHERE A.SKU				=	B.SKU
AND B.SKU				=	C.SKU
AND A.SKU				=	C.SKU
AND A.Logical_delete	=	'N'

DECLARE @Service_Level   FLOAT
SET @Service_Level	=	0.95

DECLARE @NORMSINV FLOAT
SET @NORMSINV = dbo.fnINV_GET_NORMSINV(@Service_Level)

SELECT 
		A.SKU,
		@NORMSINV as  SAFETY_FACTOR,
		C.STDDEV,
		100 as ORDER_QTY,
		A.COST,
		B.Lead_Time,
		@Service_Level	as SERVICE_LEVEL,
		@NORMSINV*ISNULL(C.STDDEV,0)*ISNULL(A.COST,0)*SQRT(ISNULL(B.Lead_Time,0)/30) as SS,
		(100 /2)*ISNULL(A.COST,0)+(@NORMSINV*ISNULL(C.STDDEV,0)*ISNULL(A.COST,0)*SQRT(ISNULL(B.Lead_Time,0)/30))as INV_COST 
FROM IO_CM_PART_MASTER		A(NOLOCK)
INNER JOIN IO_CM_PART_PLANNING	B(NOLOCK) ON A.SKU = B.SKU
INNER JOIN (SELECT SKU,STDEV(ISNULL(total_forecast,0)) as STDDEV 
			FROM VIO_DF_FORECAST_DATA(NOLOCK)GROUP BY SKU )as C ON B.SKU = C.SKU
																AND A.SKU =	C.SKU
WHERE A.Logical_delete	= 'N'

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"