SQL Server Performance

How cud i tune this better

Discussion in 'T-SQL Performance Tuning for Developers' started by Reddy, Jun 24, 2008.

  1. Reddy New Member

    SELECT CombinedTT.Level4Description, CombinedTT.EmployeeName, CombinedTT.PunchType, CombinedTT.DepartmentDescript, SUM((CASE WHEN TimeType = 1 THEN RegT / 60 ELSE 0 END)) AS RegT,
    SUM((CASE WHEN TimeType = 1 THEN OT / 60 ELSE 0 END)) AS OT, SUM((CASE WHEN TimeType = 1 THEN DT / 60 ELSE 0 END)) AS DT,
    SUM((CASE WHEN TimeType = 2 AND PunchType <> 'V' THEN TotalTime / 60 ELSE 0 END)) AS PTO, SUM((CASE WHEN TimeType = 2 AND PunchType <> 'V' THEN totaltime ELSE 0 END) + (CASE WHEN TimeType = 1 THEN RegT ELSE 0 END)) AS RegTotal, SUM((CASE WHEN (PunchType = 'LE') OR (PunchType = 'LC') THEN TotalTime / 60 ELSE (CASE WHEN TimeType <> 4 THEN AutoLunch / 60 ELSE 0 END) END)) AS AutoLunch, SUM((CASE WHEN (((TimeType = 1)) AND ((PunchType <> 'LE') AND (PunchType <> 'LC')) OR (TimeType = 2 AND PunchType <> 'V')) THEN TotalTime / 60 ELSE 0 END)) AS TotalTime, SUM((CASE WHEN ((TimeType = 1)) AND ((PunchType <> 'LE') AND (PunchType <> 'LC')) THEN TotalTime / 60 ELSE 0 END)) AS WorkedTime, SUM((((CASE WHEN TimeType = 2 AND PunchType <> 'V' THEN totaltime ELSE 0 END) + (CASE WHEN TimeType = 1 THEN RegT ELSE 0 END)) + (CASE WHEN TimeType = 1 THEN OT ELSE 0 END) * 1.5) + (CASE WHEN TimeType = 1 THEN DT ELSE 0 END) * 2) AS RTE,
    SUM(CombinedTT.TotalPayDol) AS TotalPay, SUM(CombinedTT.TotalBillDol) AS TotallBill,convert(VARCHAR(6), CombinedTT.StartTime, 112)as Period FROM (SELECT CardID, EmployeeName, StartTime, EndTime, Autolunch, Punch, PunchType, PunchClass, HomeDivision, HomeDivisionDescript, HomeDepartment, HomeDeptDescript, Department, DepartmentDescript, JobNumber, JobNumberDescription, Level4, Level4Description, Level5, Level5Description, StartRounded, EndRounded, RegT, OT, DT, TotalTime, Dollars, Quantity, SchedNo, ShiftNo, ShiftNoDescript, PayRate, TotalPayDol, BillRate, TotalBillDol, TotalDol, PayType, PayTypeDescript, ExceptionType, OTDescription, Notes, Counter, Status, PayPeriodCode, PayPeriodDesc, LastUpdated, OtherID, Paid, AccrueToOT, TimeType, SatPremAmt, SunPremAmt, RegTComp, OTComp, Premium1, ParentRec, Protect, DTComp, StartLocationNo, StartLocationType, StartLocationName, EndLocationNo, EndLocationType, EndLocationName, ShiftGroupDesignator FROM dbo.TT UNION ALL SELECT CardID, EmployeeName, StartTime, EndTime, Autolunch, Punch, PunchType, PunchClass, HomeDivision, HomeDivisionDescript, HomeDepartment, HomeDeptDescript, Department, DepartmentDescript, JobNumber, JobNumberDescription, Level4, Level4Description, Level5, Level5Description, StartRounded, EndRounded, RegT, OT, DT, TotalTime, Dollars, Quantity, SchedNo, ShiftNo, ShiftNoDescript, PayRate, TotalPayDol, BillRate, TotalBillDol, TotalDol, PayType, PayTypeDescript, ExceptionType, OTDescription, Notes, Counter, Status, PayPeriodCode, PayPeriodDesc, LastUpdated, OtherID, Paid, AccrueToOT, TimeType, SatPremAmt, SunPremAmt, RegTComp, OTComp, Premium1, ParentRec, Protect, DTComp, StartLocationNo, StartLocationType, StartLocationName, EndLocationNo, EndLocationType, EndLocationName, ShiftGroupDesignator FROM dbo.ArchTT) AS CombinedTT INNER JOIN dbo.EmployeeT ON CombinedTT.CardID = dbo.EmployeeT.CardID INNER JOIN dbo.PayPeriodT ON dbo.EmployeeT.PayPeriod = dbo.PayPeriodT.PayPeriodCodeWHERE (CombinedTT.TimeType IN ('1', '2', '4')) AND (convert(VARCHAR(6), CombinedTT.StartTime, 112)= '{?Period}') AND (CombinedTT.DepartmentDescript Like '{?ProjectName}')GROUP BY CombinedTT.Level4Description,CombinedTT.DepartmentDescript, CombinedTT.EmployeeName, CombinedTT.PunchType, CombinedTT.StartTimeHAVING (NOT (CombinedTT.PunchType IN ('S', 'V', 'AB')))ORDER BY CombinedTT.DepartmentDescript
  2. techbabu303 New Member

    Table structure and with indexes would help to give good suggestions.
    -Sat
  3. satya Moderator

Share This Page