Removing subquery of select statement used multiple times to optimize the performance

Removing subquery of select statement used multiple times to optimize the
performance

I have a following query, this query uses a subquery in the select clause
multiple times as
SELECT DISTINCT CAST(decCostFactor as decimal(9,4))
FROM tblsubteam WITH (NOLOCK)
WHERE intstore = st.intStore
AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4)
I want to remove this subquery. I tried using group by CAST(decCostFactor
as decimal(9,4)), but now it ask to include other columns as well in the
group by cluase.
Any help is really appreciated.
The main query is
DECLARE @Region int=10006
SELECT
st.strRegion,
st.intStore,
st.strStoreName,
tblSticker.intStickerNo ,
tblSticker.strTeamNo AS strTeam ,
tblSticker.dtmFixtureStartDate AS dtmStartDate ,
tblSticker.intAreaNo ,
SUBSTRING ( tblSticker.strMiscText , 8 , 30 ) AS strSection ,
tblDetail.intLineNum ,
tblDetail.strBarcode ,
tblDetail.intBarcodeLength ,
tblDetail.intBarcodeType ,
tblDetail.strBarcodeEntrySw AS strKeyBarcode ,
tblDetail.fltPrice AS fltPrice ,
tblDetail.fltQty AS fltQty ,
tblDetail.strPTCCode4 AS strKeyPrice ,
tblDetail.strPTCCode5 AS strKeyQty ,
SUBSTRING ( tblDetail.strMiscText , 1 , 1 ) AS strNOF ,
SUBSTRING ( tblDetail.strClientText , 1 , 30 ) AS strDesc ,
SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) AS strSubTeam ,
SUBSTRING ( tblDetail.strMiscText , 16 , 25 ) AS strSubTeamDesc ,
SUBSTRING ( tblDetail.strClientText , 34 , 3 ) AS strSubDept ,
SUBSTRING ( tblDetail.strClientText , 37 , 3 ) AS strClass ,
SUBSTRING ( tblDetail.strClientText , 40 , 3 ) AS strSubClass ,
SUBSTRING ( tblDetail.strMiscText , 41 , 7 ) AS strCostFactor ,
SUBSTRING ( tblDetail.strMiscText , 41 , 1 ) AS strPerishableFlag ,
tblSubTeam.blnBreakoutCost,
fltPriceLb = tblDetail.fltClientMisc1,
fltCostLb = CASE
WHEN tblDetail.decCost > 0
THEN tblDetail.decCost
ELSE tblDetail.fltClientMisc1 *
( SELECT DISTINCT CAST(decCostFactor as decimal(6,4))
FROM tblsubteam WITH (NOLOCK)
WHERE intstore = st.intStore
AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4)
)
END,
strVendor = CASE WHEN tblDetail.strVendor IS NULL THEN 'NA' ELSE
tblDetail.strVendor END,
fltWeightSouth = CASE
WHEN tblDetail.fltClientMisc1 > 0
THEN (tblDetail.fltPrice / tblDetail.fltClientMisc1)
WHEN SUBSTRING(tblDetail.strMiscText,49,1) <> 'R'
AND tblDetail.fltClientMisc1 = 0
THEN cast(SUBSTRING(tblDetail.strClientText,43,6) as float)
ELSE 0
END,
fltCostSouth = ISNULL(CASE
WHEN SUBSTRING(tblDetail.strMiscText,50,1) = 'C'
THEN (tblDetail.fltPrice )
ELSE
-- To calculate cost for Subteam 3100 in the south region -
Thomas - 2/19/2008
CASE
WHEN (SUBSTRING(tblDetail.strMiscText,12,4) = '3100')
AND (Substring(tblDetail.strMiscText,1,1) <> 'N')
THEN fltprice *
( SELECT DISTINCT CAST(decCostFactor as decimal(6,4))
FROM tblsubteam WITH (NOLOCK)
WHERE intstore = st.intStore
AND strsubteam = SUBSTRING(tblDetail.strMiscText, 12, 4)
)
ELSE CASE
WHEN tblDetail.fltClientMisc1 > 0 and tblDetail.decCost > 0
-- modified by TA to remedy Extended Retail for weighted
items
THEN (tblDetail.fltPrice / tblDetail.fltClientMisc1) *
tblDetail.decCost
ELSE CASE
WHEN (Substring(tblDetail.strMiscText,1,1) = 'N')
Or (tblDetail.strBarcode = '00000000000000')
THEN CASE
WHEN (fltprice = 0 and tblDetail.decCost > 0)
THEN CASE
WHEN SUBSTRING(tblDetail.strClientText,43,6) <> ''
THEN CASE
WHEN CAST(SUBSTRING(tblDetail.strClientText,43,6) as
float) > 0
THEN
CAST(SUBSTRING(tblDetail.strClientText,43,6)
as float)*tblDetail.decCost
ELSE fltTotalUnits*tblDetail.decCost
END
ELSE 0 * tblDetail.decCost
END
ELSE CASE
WHEN (Substring(tblDetail.strMiscText,41,7)) = ''
THEN fltprice * 0
ELSE fltprice *
Cast(Substring(tblDetail.strMiscText,41,7)
as decimal(6,4))
END
END
ELSE CASE
WHEN (fltprice > 0 and tblDetail.decCost = 0)
THEN fltprice *
( SELECT DISTINCT CAST(decCostFactor as decimal(6,4))
FROM tblsubteam WITH (NOLOCK)
WHERE intstore = st.intStore
AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4)
)
ELSE CASE
WHEN (fltprice = 0 and tblDetail.decCost > 0)
THEN CASE
WHEN CAST(SUBSTRING(tblDetail.strClientText,43,6) as
float) > 0
THEN
CAST(SUBSTRING(tblDetail.strClientText,43,6)
as float) * tblDetail.decCost
ELSE tblDetail.decCost
END
ELSE tblDetail.decCost
END
END
END
END
END
END,0),
fltItemCost = ISNULL(CASE
WHEN ((((Substring(tblDetail.strMiscText,41,7) = '00.0000')
or (tblDetail.fltClientMisc1 = 0))
or (tblDetail.decCost = 0))
and Substring(tblDetail.strMiscText,1,1) <> 'N')
THEN CASE
WHEN (fltprice > 0 and tblDetail.decCost = 0)
THEN fltprice *
( SELECT DISTINCT CAST(decCostFactor as decimal(9,4))
FROM tblsubteam WITH (NOLOCK)
WHERE intstore = st.intStore
AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4)
)
ELSE CASE
WHEN (fltprice = 0 and tblDetail.decCost > 0)
THEN tblDetail.decCost
ELSE CASE
WHEN (Substring(tblDetail.strMiscText,41,7)) = ''
THEN fltprice * 0
ELSE fltprice *
Cast(Substring(tblDetail.strMiscText,41,7) as
decimal(9,4))
END
END
END
ELSE CASE
WHEN (Substring(tblDetail.strMiscText,1,1) = 'N')
Or (tblDetail.strBarcode = '00000000000000')
THEN fltprice * Cast(Substring(tblDetail.strMiscText,41,7)
as decimal(9,4))
ELSE CASE
WHEN (fltprice > 0 and tblDetail.decCost = 0)
THEN fltprice *
( SELECT DISTINCT CAST(decCostFactor as decimal(9,4))
FROM tblsubteam WITH (NOLOCK)
WHERE intstore = st.intStore
AND strsubteam = SUBSTRING(tblDetail.strMiscText,12,4)
)
ELSE CASE
WHEN (Substring(tblDetail.strMiscText,41,7)) = ''
THEN fltprice * 0
ELSE fltprice *
Cast(Substring(tblDetail.strMiscText,41,7) as
decimal(9,4))
END
END
END
END, 0),
strWeight = SUBSTRING(tblDetail.strClientText, 43, 6)
FROM
tblSticker WITH ( NOLOCK )
INNER JOIN tblDetail WITH ( NOLOCK )
ON tblSticker.intStore = tblDetail.intStore
AND tblSticker.intStickerNo = tblDetail.intStickerNo
AND tblSticker.dtmStickerDate = tblDetail.dtmStickerDate
LEFT OUTER JOIN tblsubteam
ON tblDetail.intStore = tblSubTeam.intStore
AND SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) = tblSubTeam.strSubTeam
inner join tblStore st on st.intStore=tblDetail.intStore
--fix add join to store. Kevin 2/8/07
-- AND SUBSTRING ( tblDetail.strMiscText , 12 , 4 ) = tblSubTeam.strSubTeam
--fix fix move join to store outside of compound conditional. Eddie 9/2/11
WHERE
((@Region is not null and st.intRegion=@Region) or @Region is null)
AND st.intStore < 90000
AND ( tblSticker.intStickerNo NOT BETWEEN 334717100
AND 334717299 )
AND tblSticker.strRescanSW = 'N'
AND ( tblSticker.strEmptyStatus = ' '
OR tblSticker.strEmptyStatus = '*' )
ORDER BY st.strStoreName,
tblSticker.intStickerNo ,
tblDetail.intLineNum