Code:
SELECT DISTINCT
a.STORE_CD
,SUBSTRING(a.STORE_NAME, 5, LEN(a.STORE_NAME) - 4)
,a.ITM_CD
,a.DES
,b.DEPT_LONG
,b.SUB_DEPT_LONG
,b.SUB_DEPT_FULL
,b.CLASS_LONG
,b.SUB_CLASS_LONG
,a.STATUS
,a.SKU_TYPE
,d.VE_CD
,d.GERS_VE_CD
,ISNULL(c.BUYER_CLASS, 'M')
,ISNULL(c.REPL_FLAG, 'N')
,ISNULL(c.VISUAL_PRESENTATION, 0)
,ISNULL(c.JOB_SIZE, 0)
,c.START_DT
,c.STOP_DT
,CAST(e.RET_PRC) AS [RET_PRC]
,a.SQFT_CONV_FACTOR
,CAST(e.RET_PRC / a.SQFT_CONV_FACTOR) AS [RET_PRC_SQFT]
,a.BOX_QTY AS [Box Qty]
,a.BOX_QTY * SQFT_CONV_FACTOR AS [Sqft per Box]
FROM (SELECT DISTINCT STORE_CD, STORE_NAME, ITM_CD, DES, STATUS, SKU_TYPE, DEPT, SUB_DEPT, CLASS, SUB_CLASS, VE_CD, SQFT_CONV_FACTOR, MMS_STORE_TYPE, OPEN_DT, dbo.MMS_ITM.BOX_QTY FROM DM.dbo.MMS_ITM CROSS JOIN DM.dbo.MMS_STORE) AS a
JOIN dbo_MMS_ITM_HIERARCHY AS b
ON a.DEPT=b.DEPT AND a.SUB_DEPT=b.SUB_DEPT AND a.CLASS=b.CLASS AND a.SUB_CLASS=b.SUB_CLASS
LEFT OUTER JOIN dbo_E3_ASR_SKU AS c
ON a.ITM_CD = c.ITM_CD AND a.STORE_CD = c.STORE_CD
INNER JOIN dbo_MMS_VE AS d
ON a.VE_CD = d.VE_CD
INNER JOIN dbo_MMS_ITM_STORE AS e
ON a.STORE_CD = e.STORE_CD AND a.ITM_CD = e.ITM_CD
INNER JOIN dbo_MMS_STORE AS f
ON c.STORE_CD = f.STORE_CD
INNER JOIN dbo_MMS_REGION AS g
ON f.REGION = ISNULL(g.REGION_CD,0)
WHERE a.MMS_STORE_TYPE IN ('S', ' ')
AND a.STORE_CD = 101
AND a.STATUS NOT IN ('I');