ChrisM@Work (1/23/2013)
Have you tried figuring out the filters and applying to the inner query? Something like this:
DECLARE @YEAR INT, @MONTH INT
SELECT @YEAR = 2005, @MONTH = 6
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = CONVERT(DATETIME,CAST(@YEAR AS CHAR(4))+RIGHT('0'+CAST(@MONTH AS VARCHAR(2)),2)+'01',112)
SET @EndDate = DATEADD(mm,13,@StartDate)
SELECT @StartDate, @EndDate -- eyeball
SELECT
c.Reference,
mc.MPAN--,
--ElementID,
--mm = MONTH(hhdatetime),
--yy = YEAR(hhdatetime)
FROM CONTRACTS.MPAN_Contract mc
join CONTRACTS.ContractElementUnitInstance ui
on mc.ContractID = ui.ContractID
join CONTRACTS.Contract c
on ui.ContractID = c.id
and ui.ElementID in (2,3,4)
join CONTRACTS.CalendarAllHH cal
on cal.HHDatetime between ui.Startdate and ui.Enddate
and cal.HHDatetime between mc.startdate and mc.enddate
WHERE cal.HHDatetime >= @StartDate AND cal.HHDatetime < @EndDate
GROUP BY c.Reference, mc.MPAN, ElementID, MONTH(hhdatetime), YEAR(hhdatetime)
I did wonder if the view was created to allow direct use of the @Month & @Year parameters.
I also assume the group by clause is there to remove duplicates - any ideas on if that or distinct performs better?