declare @Siteid as  VARCHAR(MAX)
 declare @Processid  VARCHAR(MAX)
 declare @StartDate DATETIME
 declare @EndDate DATETIME
 
 set @Siteid='0,60,61,62,63,53,64,65,66,67,78,68,69,70,54,71,72,47,73,74,75,89,57,76,58,77,79,80,81,86,82,83,46,84,85,56,88,90,87,59,94,91,92,93'
 set @Processid='4'
 set @StartDate='01-oct-2008'
 set @EndDate='01-oct-2009'
 
 DECLARE @Sql VARCHAR(MAX), @Str VARCHAR(MAX),@Sqlmax VARCHAR(MAX)
 DECLARE @x XML
     SET @x='<i>'+REPLACE(@SiteId,',','</i><i>')+'</i>'
		DECLARE @tblsites AS TABLE(siteid INT)
		INSERT INTO @tblsites
		SELECT x.i.value('.','varchar(50)')AS SiteID FROM @x.nodes('//i')x(i)
		
		SET @x=''
		SET @x='<i>'+REPLACE(@ProcessId,',','</i><i>')+'</i>'
		DECLARE @tblprocess AS TABLE(processid INT)
		INSERT INTO @tblprocess
		SELECT x.i.value('.','varchar(50)')AS ProcessId FROM @x.nodes('//i')x(i)

		SELECT 
			
			
            IRM.ReadingDateTime,
            Instruments.InstrumentID,
            Instruments.InstrumentName,
            Instruments.InstrumentOrder,
            Units.DisplayUnit AS 'UnitName',
            DisplayFormula,
            SUM(CASE WHEN Reading>LowThreshold AND (TYPE=3 OR TYPE=33 OR TYPE=333)  THEN 1 ELSE 0 END)OVER(PARTITION BY  lines.LineID,DATEPART(yy,ReadingDateTime))AS InstCnt,
            Sites.SiteName,
            Lines.LineID, 
            Lines.LineName,
        CAST(IRD.Reading AS DECIMAL(18,4)) AS Reading,[TYPE],LowThreshold    
        INTO #TempReadings                   
        FROM InstrumentReadingDetails IRD 
		INNER JOIN InstrumentReadingMaster IRM
		ON IRM.InstrumentReadingMasterID=IRD.InstrumentReadingMasterID
		INNER JOIN Instruments 
		ON Instruments.InstrumentID=IRD.InstrumentID
		INNER JOIN Lines
		ON Lines.LineID =Instruments.LineID
		INNER JOIN POS
		ON POS.POSID=LINEs.POSID
		INNER JOIN Sites
		ON POS.SiteID = Sites.SiteID
		INNER JOIN Units
		ON Units.UnitID=Instruments.UnitId
        INNER JOIN Process
        ON Lines.ProcessId=Process.ProcessId
        INNER JOIN @tblsites tblsites ON tblsites.SiteID=Sites.SiteID
        INNER JOIN @tblprocess tblprocess ON tblprocess.processid=Process.ProcessId
         WHERE  Lines.IsActive=1 AND Instruments.IsActive=1
        AND IRM.ReadingDateTime >=@StartDate AND
            IRM.ReadingDateTime <=@EndDate
GROUP BY 
	          
               IRM.ReadingDateTime,
               Instruments.InstrumentID,
               Instruments.InstrumentName, 
               IRD.Reading,Instruments.InstrumentOrder,
               Units.DisplayUnit,DisplayFormula,[TYPE],LowThreshold,
               SiteName,
               Lines.LineID, 
               Lines.LineName
ORDER BY IRM.ReadingDateTime 

quote:

---

Originally posted by AndrewMurphy

Posting the execution plan would be of benefit to those not familiar with your full setup.

---