>Would you post the stored procedure please. It has to do with the optimizer. But I like to see the source before making a definitive comment.
>
>-Mike

CREATE PROCEDURE mst_Percentage(@Month smallint, @year int, @cDistmgr varchar(10))
AS

declare @m1 float
declare @m2 float
declare @m3 float
declare @m4 float
declare @y1 float
declare @y2 float
declare @y3 float
declare @y4 float


SET @Y1 = @YEAR
SET @Y3 = @YEAR
SET @M1 = @MONTH
SET @M3 = @MONTH - 2

IF @M1 = 2 BEGIN
SET @M3 = 12
SET @Y3 = @Y3 - 1
END ELSE BEGIN
IF @M1 = 1 BEGIN
SET @M3 = 11
SET @Y3 = @Y3 - 1
END
END
SET @M4 = @M1
IF @M4 = 12 BEGIN
SET @M4 = 1
SET @Y1 = @Y1 + 1
END ELSE BEGIN
SET @M4 = @M4 + 1
END

select asof=CAST (CAST(@month AS VARCHAR)+'/01/'+CAST(@year AS VARCHAR) AS DATETIME),
NTRPESOS=sum(case when @month = t.nmonth and @year = t.nyear then t.ntrpesos else 0 end),
t.CEMPCODE,
t.CCUSTCODE,
t.CCUSTNAME,
t.CTERRCODE,
t.CLASTNAME,
t.CMI,
t.CDISTMGR,
t.CDIVISION,
t.CDIVICODE,
t.CDISTRICT,
t.CBRAND,
nBrand=(select sum(M.NTRPESOS) from zzz m where m.nmonth=@month and m.nyear=@year and t.cbrand = m.cbrand and m.CDISTmgr = @cDistMgr),
nPercentage1=((select sum(M.NTRPESOS) from x2 m where m.nmonth=@month and m.nyear=@year and t.cbrand = m.cbrand and m.CDISTmgr = @cDistMgr)),
nAverage=(case when sum(t.ntrpesos) = 0 then 0 else sum(t.ntrpesos) end),
t.NMONTH,
t.NYEAR,
t.NPSHARE
from zzz t
where
(CAST (CAST (t.nMONTH AS VARCHAR)
+ '/01/' + CAST (t.nYear AS VARCHAR)
AS DATETIME)
>= CAST (CAST(@M3 AS VARCHAR)+'/01/'+CAST(@Y3 AS VARCHAR) AS DATETIME) AND
CAST (CAST (t.nMONTH AS VARCHAR)
+ '/01/' + CAST (t.nYear AS VARCHAR)
AS DATETIME)
< CAST (CAST(@M4 AS VARCHAR)+'/01/'+CAST(@Y1 AS VARCHAR) AS DATETIME)) and
t.CDISTmgr = @cDistMgr
group by
t.CEMPCODE,
t.CCUSTCODE,
t.CCUSTNAME,
t.CTERRCODE,
t.CLASTNAME,
t.CMI,
t.CDISTMGR,
t.CDIVISION,
t.CDIVICODE,
t.CDISTRICT,
t.CBRAND,
-- x1.NTRPESOS,
t.NMONTH,
t.NYEAR,
-- x2.NTRempcode,
t.NPSHARE
order by t.CEMPCODE, t.CBRAND, t.CCUSTCODE