math martinez (11/19/2012)
Hi Jerome!You don't need to use just to match the year and the month of your parameter. Try this query
SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (YEAR(EndDate) = YEAR(@date1)) AND (MONTH(EndDate) = MONTH(@date1))
Hope i can help:-)
This is a little easier to read than the original post but has the same issues with SARGability. It will cause a full scan of the table because the functions in the where clause are referencing columns in the table. That means the optimizer has to evaluate every single row to see if it is a match.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/