Transact SQL


OLTP Query Performance Tuning

Return to Resume   GoTo Solution-Part1   GoTo Solution-Part2

Issue:
Payroll takes too long and fails due to application time out.
EXEC dbo.usp_APP_GenericPayrollUpload_GetRecs_KELL 'KELL', 221010, '9/22/2012', '', 'FileNo', 'R', 'O', 'D', 'F', 'N', 'N'

Tuning Results - (Vast Improvement!): Tuning Results


"Part 1 - Outer Sproc"
ORIGINAL SP (428 Lines):
 DECLARE @Html.Raw("@")TodayMinus6 DATETIME, @Today DATETIME = CONVERT(VARCHAR, GETDATE(), 101)
 SET @TodayMinus6 = (DATEADD(dd,-6,@Today))
 CREATE TABLE #tmpUploadRecs
 (
 ... code
 )
 DECLARE groupCursor CURSOR READ_ONLY FOR
 SELECT
 cg.GroupCode,ISNULL(cg.LateTimeEntryWeeks,0)
 ,ped.PayrollPeriodEndDate, cg.PayrollFreq,ISNULL(cg.StaffingSetupType,'')
 FROM TimeCurrent.dbo.tblClientGroups cg WITH(NOLOCK)
 INNER JOIN TimeHistory.dbo.tblPeriodEndDates ped WITH(NOLOCK)
 ON ped.Client = cg.Client
 AND ped.GroupCode = cg.GroupCode
 WHERE
 cg.Client = @Client
 AND cg.GroupCode BETWEEN @WebGroupCodeLowerLimit AND @WebGroupCodeUpperLimit
 AND cg.StaffingSetupType = '1'
 AND cg.RecordStatus = '1'
 AND cg.IncludeInUpload = '1'
 AND ped.PayrollPeriodEndDate = @PPED;
 OPEN groupCursor
 FETCH NEXT FROM groupCursor INTO @GroupCode, @LateTimeEntryWeeks, @PPEDCursor, @PayrollFreq, @StaffingSetupType
 WHILE (@@fetch_status <> -1)
 BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 INSERT INTO #tmpUploadRecs
 EXEC TimeHistory.dbo.usp_APP_GenericPayrollUpload_GetRecs_KELL_GetData
 @Client, @GroupCode, @PPEDCursor, @PPEDCursor, '', 'FileNo', @REGPAYCODE, @OTPAYCODE, @DTPAYCODE, @PayrollType, '', @TestingFlag
 SET @PPEDCursor = DATEADD(dd, -7, @PPEDCursor)
 END
 END
 FETCH NEXT FROM groupCursor INTO @GroupCode, @LateTimeEntryWeeks, @PPEDCursor, @PayrollFreq, @StaffingSetupType
 END
 CLOSE groupCursor
 DEALLOCATE groupCursor
 SELECT
 Line1,GroupCode,SSN,weDate,AssignmentNo,TimeType FROM #tmpUploadRecs ORDER BY GroupCode,SSN,weDate,AssignmentNo,TimeType,Line1;
 DROP TABLE #tmpUploadRecs
 RETURN

Solution (Part 1): Remove cursor in outer sproc call below. Get all Weeks and Groups in one pass.
NEW SP (360 Lines): DECLARE @TodayMinus6 DATETIME, @Today DATETIME = CONVERT(VARCHAR, GETDATE(), 101) SET @TodayMinus6 = (DATEADD(dd,-6,@Today)) CREATE TABLE #tmpUploadRecs ( ... code ) INSERT INTO #tmpUploadRecs EXEC TimeHistory.dbo.usp_APP_GenericPayrollUpload_GetRecs_KELL_GetData_PerfTuned_NoGroup @Client,@GroupCode,@PPED,@PPED,'','FileNo',@REGPAYCODE,@OTPAYCODE,@DTPAYCODE,@PayrollType,'',@TestingFlag; SELECT Line1,GroupCode,SSN,weDate,AssignmentNo,TimeType FROM #tmpUploadRecs ORDER BY GroupCode,SSN,weDate,AssignmentNo,TimeType,Line1; DROP TABLE #tmpUploadRecs RETURN


"Part 2 - Inner Sproc" ORIGINAL SP (1397 Lines): ( ... code ) INSERT INTO #tmpExport SELECT ( ... code ) DELETE FROM #tmpExport WHERE SunHrs IN ('0', '0.00') AND MonHrs IN ('0', '0.00') AND TueHrs IN ('0', '0.00') AND WedHrs IN ('0', '0.00') AND ThuHrs IN ('0', '0.00') AND FriHrs IN ('0', '0.00') AND SatHrs IN ('0', '0.00') AND TotalHrs IN ('0', '0.00') -- Remove 0 hours from days that have no transactions. We want to keep the 0 if the transactions were all voided or disputed UPDATE #tmpExport SET SunHrs = CASE WHEN SunCnt = 0 THEN '' ELSE SunHrs END, MonHrs = CASE WHEN MonCnt = 0 THEN '' ELSE MonHrs END, TueHrs = CASE WHEN TueCnt = 0 THEN '' ELSE TueHrs END, WedHrs = CASE WHEN WedCnt = 0 THEN '' ELSE WedHrs END, ThuHrs = CASE WHEN ThuCnt = 0 THEN '' ELSE ThuHrs END, FriHrs = CASE WHEN FriCnt = 0 THEN '' ELSE FriHrs END, SatHrs = CASE WHEN SatCnt = 0 THEN '' ELSE SatHrs END DECLARE LineItemCursor CURSOR READ_ONLY FOR SELECT RecordID, BranchID, EmployeeID, weDate, AssignmentNo FROM #tmpExport tmp ORDER BY tmp.SSN, tmp.AssignmentNo, CASE WHEN tmp.DisputedCode = 'Y' THEN 1 ELSE 0 END, CASE WHEN tmp.TimeType = @REGPAYCODE THEN 0 WHEN tmp.TimeType = @OTPAYCODE THEN 1 WHEN tmp.TimeType = @DTPAYCODE THEN 2 ELSE 3 END OPEN LineItemCursor FETCH NEXT FROM LineItemCursor INTO @LI_RecordID, @LI_BranchID, @LI_EmployeeID, @LI_weDate, @LI_AssignmentNo WHILE @@FETCH_STATUS = 0 BEGIN --PRINT 'Line Item Cursor: ' + ISNULL(CAST(@LI_RecordID AS VARCHAR), '') IF (ISNULL(@Prev_BranchID, '') <> @LI_BranchID OR ISNULL(@Prev_EmployeeID, '') <> @LI_EmployeeID OR ISNULL(@Prev_weDate, '') <> @LI_weDate OR ISNULL(@Prev_AssignmentNo, '') <> @LI_AssignmentNo) BEGIN --PRINT 'processing punches' DECLARE PunchCursor CURSOR READ_ONLY FOR SELECT DATEPART(dw, thd.TransDate), InTime, OutTime, tmp.RecordID FROM #tmpExport tmp INNER JOIN TimeHistory..tblTimeHistDetail thd WITH(NOLOCK) ON thd.Client = @Client AND thd.GroupCode = @GroupCode AND thd.PayrollPeriodEndDate = @PPED AND thd.SSN = tmp.SSN AND thd.SiteNo = tmp.SiteNo AND thd.DeptNo = tmp.DeptNo AND NOT (ISNULL(thd.InTime, '1899-12-30 00:00:00.000') = '1899-12-30 00:00:00.000' AND ISNULL(thd.OutTime, '1899-12-30 00:00:00.000') = '1899-12-30 00:00:00.000') AND thd.Hours <> 0 WHERE tmp.RecordID = @LI_RecordID ORDER BY dbo.punchdatetime2(thd.TransDate, thd.InDay, thd.InTime) ASC OPEN PunchCursor FETCH NEXT FROM PunchCursor INTO @PunchWeekDay, @PunchInTime, @PunchOutTime, @SummaryRecordID WHILE @@FETCH_STATUS = 0 BEGIN SET @PunchRecordID = NULL SET @TotalIOHours = NULL SELECT TOP 1 @PunchRecordID = tmp2.RecordID FROM #tmpExport tmp INNER JOIN #tmpExport tmp2 ON tmp2.SSN = tmp.SSN AND tmp2.SiteNo = tmp.SiteNo AND tmp2.DeptNo = tmp.DeptNo AND tmp2.TimeType = 'IO' AND CASE @PunchWeekDay WHEN 1 THEN tmp2.SunHrs WHEN 2 THEN tmp2.MonHrs WHEN 3 THEN tmp2.TueHrs WHEN 4 THEN tmp2.WedHrs WHEN 5 THEN tmp2.ThuHrs WHEN 6 THEN tmp2.FriHrs WHEN 7 THEN tmp2.SatHrs END = '' WHERE tmp.RecordID = @SummaryRecordID ORDER BY tmp2.RecordID IF (@PunchRecordID IS NULL) BEGIN --PRINT 'insert' /*SELECT @TotalIOHours = SUM(te2.TotalHrs) FROM #tmpExport te1 INNER JOIN #tmpExport te2 ON te2.AssignmentNo = te1.AssignmentNo AND te2.BranchID = te1.BranchID AND te2.weDate = te1.weDate AND te2.TimeType IN (@REGPAYCODE, @OTPAYCODE, @DTPAYCODE) WHERE te1.RecordID = @SummaryRecordID*/ INSERT INTO #tmpExport (SSN, EmployeeID, EmpName, FileBreakID, weDate, AssignmentNo, Last4SSN, CollectFrmt, ReportingInt, BranchID, GroupID, TimesheetDate, SunHrs, MonHrs, TueHrs, WedHrs, ThuHrs, FriHrs, SatHrs, TotalHrs, TotalAssignmentHrs, TimeType, Confirmation, TransType, Individual, [Timestamp], ExpenseMiles, ExpenseDollars, [Status], Optional1, Optional2, Optional3, Optional4, Optional5, Optional6, Optional7, Optional8, Optional9, [AuthTimestamp], [ApprovalUserID], [AuthEmail], [AuthConfirmNo], [AuthComments], WorkRules, Rounding, WeekEndDay, IVR_Count, WTE_Count, SiteNo, DeptNo, SortSequence, Line1, GroupCode, PayrollType, SnapshotDateTime, MaxTHDRecordID, PayOnly,BPO,DisputedCode) SELECT SSN, EmployeeID, EmpName, FileBreakID, weDate, AssignmentNo, Last4SSN, CollectFrmt, ReportingInt, BranchID, GroupID, TimesheetDate, SunHrs = '', MonHrs = '', TueHrs = '', WedHrs = '', ThuHrs = '', FriHrs = '', SatHrs = '', TotalAssignmentHrs, TotalAssignmentHrs, TimeType = 'IO', Confirmation, TransType, Individual, [Timestamp], ExpenseMiles, ExpenseDollars, [Status], Optional1, Optional2, Optional3, Optional4, Optional5, Optional6, Optional7, Optional8, Optional9, [AuthTimestamp], [ApprovalUserID], [AuthEmail], [AuthConfirmNo], [AuthComments], WorkRules, Rounding, WeekEndDay, IVR_Count, WTE_Count, SiteNo, DeptNo, SortSequence, Line1, GroupCode, PayrollType, SnapshotDateTime, MaxTHDRecordID, PayOnly,BPO,DisputedCode FROM #tmpExport WHERE RecordID = @SummaryRecordID SET @PunchRecordID = SCOPE_IDENTITY() END IF (@PunchWeekDay = 1) BEGIN --PRINT 'update day 1' UPDATE #tmpExport SET SunHrs = REPLACE((LEFT(CONVERT(VARCHAR(5), @PunchInTime, 108), 5) + '_' + LEFT(CONVERT(VARCHAR(5), @PunchOutTime, 108), 5)), ':', '') WHERE RecordID = @PunchRecordID END IF (@PunchWeekDay = 2) BEGIN --PRINT 'update day 2' UPDATE #tmpExport SET MonHrs = REPLACE((LEFT(CONVERT(VARCHAR(5), @PunchInTime, 108), 5) + '_' + LEFT(CONVERT(VARCHAR(5), @PunchOutTime, 108), 5)), ':', '') WHERE RecordID = @PunchRecordID END IF (@PunchWeekDay = 3) BEGIN --PRINT 'update day 3' UPDATE #tmpExport SET TueHrs = REPLACE((LEFT(CONVERT(VARCHAR(5), @PunchInTime, 108), 5) + '_' + LEFT(CONVERT(VARCHAR(5), @PunchOutTime, 108), 5)), ':', '') WHERE RecordID = @PunchRecordID END IF (@PunchWeekDay = 4) BEGIN --PRINT 'update day 4' UPDATE #tmpExport SET WedHrs = REPLACE((LEFT(CONVERT(VARCHAR(5), @PunchInTime, 108), 5) + '_' + LEFT(CONVERT(VARCHAR(5), @PunchOutTime, 108), 5)), ':', '') WHERE RecordID = @PunchRecordID END IF (@PunchWeekDay = 5) BEGIN --PRINT 'update day 5' UPDATE #tmpExport SET ThuHrs = REPLACE((LEFT(CONVERT(VARCHAR(5), @PunchInTime, 108), 5) + '_' + LEFT(CONVERT(VARCHAR(5), @PunchOutTime, 108), 5)), ':', '') WHERE RecordID = @PunchRecordID END IF (@PunchWeekDay = 6) BEGIN --PRINT 'update day 6' UPDATE #tmpExport SET FriHrs = REPLACE((LEFT(CONVERT(VARCHAR(5), @PunchInTime, 108), 5) + '_' + LEFT(CONVERT(VARCHAR(5), @PunchOutTime, 108), 5)), ':', '') WHERE RecordID = @PunchRecordID END IF (@PunchWeekDay = 7) BEGIN --PRINT 'update day 7' UPDATE #tmpExport SET SatHrs = REPLACE((LEFT(CONVERT(VARCHAR(5), @PunchInTime, 108), 5) + '_' + LEFT(CONVERT(VARCHAR(5), @PunchOutTime, 108), 5)), ':', '') WHERE RecordID = @PunchRecordID END FETCH NEXT FROM PunchCursor INTO @PunchWeekDay, @PunchInTime, @PunchOutTime, @SummaryRecordID END CLOSE PunchCursor DEALLOCATE PunchCursor SET @Prev_BranchID = @LI_BranchID SET @Prev_EmployeeID = @LI_EmployeeID SET @Prev_weDate = @LI_weDate SET @Prev_AssignmentNo = @LI_AssignmentNo END FETCH NEXT FROM LineItemCursor INTO @LI_RecordID, @LI_BranchID, @LI_EmployeeID, @LI_weDate, @LI_AssignmentNo END CLOSE LineItemCursor DEALLOCATE LineItemCursor UPDATE #tmpExport SET #tmpExport.AuthEmail = CASE WHEN bkp.RecordID IS NOT NULL THEN bkp.Email ELSE CASE WHEN #tmpExport.ApprovalUserID <> 0 THEN (SELECT CASE WHEN ISNULL(Email, '') = '' THEN LEFT(FirstName + ' ' + LastName, 100) ELSE Email END FROM TimeCurrent.dbo.tblUser WHERE UserID = #tmpExport.ApprovalUserID) ELSE 'NO APPROVER EMAIL' END END , #tmpExport.TransType = CASE WHEN #tmpExport.IVR_Count > 0 THEN 'I' ELSE CASE WHEN #tmpExport.WTE_Count > 0 THEN 'W' ELSE 'W' END END FROM #tmpExport INNER JOIN TimeHistory.dbo.tblTimeHistDetail thd WITH(NOLOCK) ON thd.RecordID = #tmpExport.MaxTHDRecordID LEFT JOIN TimeHistory..tblTimeHistDetail_BackupApproval bkp WITH(NOLOCK) ON bkp.THDRecordId = #tmpExport.MaxTHDRecordID LEFT JOIN TimeCurrent..tblUser as Usr WITH(NOLOCK) ON usr.UserID = isnull(thd.AprvlStatus_UserID,0) UPDATE #tmpExport SET Line1= AssignmentNo + @Delim + Last4SSN + @Delim + CollectFrmt + @Delim + ReportingInt + @Delim + BranchID + @Delim + GroupID + @Delim + TimesheetDate + @Delim + CASE WHEN MonHrs IN ('0', '0.00', '0000', '') THEN '' WHEN TimeType = 'IO' THEN MonHrs ELSE RIGHT('0000' + REPLACE(MonHrs, '.', ''), 4) END + @Delim + CASE WHEN TueHrs IN ('0', '0.00', '0000', '') THEN '' WHEN TimeType = 'IO' THEN TueHrs ELSE RIGHT('0000' + REPLACE(TueHrs, '.', ''), 4) END + @Delim + CASE WHEN WedHrs IN ('0', '0.00', '0000', '') THEN '' WHEN TimeType = 'IO' THEN WedHrs ELSE RIGHT('0000' + REPLACE(WedHrs, '.', ''), 4) END + @Delim + CASE WHEN ThuHrs IN ('0', '0.00', '0000', '') THEN '' WHEN TimeType = 'IO' THEN ThuHrs ELSE RIGHT('0000' + REPLACE(ThuHrs, '.', ''), 4) END + @Delim + CASE WHEN FriHrs IN ('0', '0.00', '0000', '') THEN '' WHEN TimeType = 'IO' THEN FriHrs ELSE RIGHT('0000' + REPLACE(FriHrs, '.', ''), 4) END + @Delim + CASE WHEN SatHrs IN ('0', '0.00', '0000', '') THEN '' WHEN TimeType = 'IO' THEN SatHrs ELSE RIGHT('0000' + REPLACE(SatHrs, '.', ''), 4) END + @Delim + CASE WHEN SunHrs IN ('0', '0.00', '0000', '') THEN '' WHEN TimeType = 'IO' THEN SunHrs ELSE RIGHT('0000' + REPLACE(SunHrs, '.', ''), 4) END + @Delim + RIGHT('0000' + REPLACE(CAST(TotalHrs AS VARCHAR), '.', ''), CASE WHEN TotalHrs >= 100 THEN 5 ELSE 4 END) + @Delim + TimeType + @Delim + Confirmation + @Delim + TransType + @Delim + Individual + @Delim + [Timestamp] + @Delim + ExpenseMiles + @Delim + ExpenseDollars + @Delim + [Status] + @Delim + Optional1 + @Delim + Optional2 + @Delim + Optional3 + @Delim + Optional4 + @Delim + Optional5 + @Delim + Optional6 + @Delim + Optional7 + @Delim + Optional8 + @Delim + Optional9 + @Delim + AuthTimestamp + @Delim + AuthEmail + @Delim + AuthConfirmNo + @Delim + AuthComments + @Delim + WorkRules + @Delim + Rounding + @Delim + WeekEndDay + @Delim + PayOnly + @Delim + BPO + @Delim + DisputedCode SELECT SSN , EmployeeID , EmpName , FileBreakID , weDate , AssignmentNo , SiteNo , DeptNo , Line1 , GroupCode , PayrollType , SnapshotDateTime , TimeType FROM #tmpExport ORDER BY SSN, AssignmentNo, CASE TimeType WHEN 'R' THEN 1 WHEN 'O' THEN 2 WHEN 'D' THEN 3 WHEN 'IO' THEN 4 END
Solution (Part2): Remove cursors in "inner" called sproc below and find better way of date matching. Handle all Weeks and Groups in one pass. Accomplished by CTEs, Windowing Functions, and PIVOT.
NEW SP (1274 Lines): ( ... code ) INSERT INTO #tmpExport SELECT ( ... code ) UPDATE #tmpExport SET SunHrs = CASE WHEN SunCnt = 0 THEN '' ELSE SunHrs END ,MonHrs = CASE WHEN MonCnt = 0 THEN '' ELSE MonHrs END ,TueHrs = CASE WHEN TueCnt = 0 THEN '' ELSE TueHrs END ,WedHrs = CASE WHEN WedCnt = 0 THEN '' ELSE WedHrs END ,ThuHrs = CASE WHEN ThuCnt = 0 THEN '' ELSE ThuHrs END ,FriHrs = CASE WHEN FriCnt = 0 THEN '' ELSE FriHrs END ,SatHrs = CASE WHEN SatCnt = 0 THEN '' ELSE SatHrs END; CREATE INDEX ncixE_MTHDRID ON #tmpExport (MaxTHDRecordID); CREATE INDEX ncixE_SATSDEB ON #tmpExport (SSN,AssignmentNo,TimeType,SiteNo,DeptNo,EmployeeID,BranchID); CREATE INDEX ncixE_MTHDRID_IVRC_WTEC_AUID ON #tmpExport (MaxTHDRecordID,IVR_Count,WTE_Count,ApprovalUserID); UPDATE EX SET AuthEmail = CASE WHEN bkp.RecordID IS NOT NULL THEN bkp.Email ELSE CASE WHEN EX.ApprovalUserID <> 0 THEN(SELECT CASE WHEN ISNULL(Email,'') = '' THEN LEFT(FirstName + ' ' + LastName,100) ELSE Email END FROM TimeCurrent.dbo.tblUser WHERE UserID = EX.ApprovalUserID) ELSE 'NO APPROVER EMAIL' END END ,EX.TransType = CASE WHEN EX.IVR_Count > 0 THEN 'I' ELSE CASE WHEN EX.WTE_Count > 0 THEN 'W' ELSE 'W' END END FROM #tmpExport EX INNER JOIN #tmpBaseData tbd ON tbd.RecordID = EX.MaxTHDRecordID LEFT JOIN TimeHistory.dbo.tblTimeHistDetail_BackupApproval bkp WITH(NOLOCK) ON bkp.THDRecordId = EX.MaxTHDRecordID; --IOHours SELECT 'SprocStep' = 'IOHours', GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ,RankOrder = RANK() OVER ( PARTITION BY GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ORDER BY TransDate, CASE InDay WHEN 7 THEN CASE tintDayOfWeek WHEN 1 THEN InDay - 7 ELSE InDay END WHEN 1 THEN CASE tintDayOfWeek WHEN 7 THEN InDay + 7 ELSE InDay END ELSE InDay END,InTime ) ,strIOValue = REPLACE((LEFT(CONVERT(VARCHAR(5),InTime,108),5) + '_' + LEFT(CONVERT(VARCHAR(5),OutTime,108),5)),':','') FROM #tmpBaseData WHERE NOT (InTime IS NULL AND OutTime IS NULL) AND NOT (InTime = '00:00:00.0000000' AND OutTime = '00:00:00.0000000') AND [Hours] <> 0 --PivotedIOHrs ;WITH HrsIOData AS ( SELECT GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ,RankOrder = RANK() OVER ( PARTITION BY GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ORDER BY TransDate, CASE InDay WHEN 7 THEN CASE tintDayOfWeek WHEN 1 THEN InDay - 7 ELSE InDay END WHEN 1 THEN CASE tintDayOfWeek WHEN 7 THEN InDay + 7 ELSE InDay END ELSE InDay END,InTime ) ,strIOValue = REPLACE((LEFT(CONVERT(VARCHAR(5),InTime,108),5) + '_' + LEFT(CONVERT(VARCHAR(5),OutTime,108),5)),':','') FROM #tmpBaseData WHERE NOT (InTime IS NULL AND OutTime IS NULL) AND NOT (InTime = '00:00:00.0000000' AND OutTime = '00:00:00.0000000') AND [Hours] <> 0 ) SELECT 'SprocStep' = 'PivotedIOHrs', GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,RankOrder ,SunHrs = [1],MonHrs = [2],TueHrs = [3] ,WedHrs = [4],ThuHrs = [5],FriHrs = [6],SatHrs = [7] FROM HrsIOData PIVOT ( MIN(strIOValue) FOR tintDayOfWeek IN ([1],[2],[3],[4],[5],[6],[7]) ) P --cteResults_IO ;WITH HrsIOData AS ( SELECT GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ,RankOrder = RANK() OVER ( PARTITION BY GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ORDER BY TransDate, CASE InDay WHEN 7 THEN CASE tintDayOfWeek WHEN 1 THEN InDay - 7 ELSE InDay END WHEN 1 THEN CASE tintDayOfWeek WHEN 7 THEN InDay + 7 ELSE InDay END ELSE InDay END,InTime ) ,strIOValue = REPLACE((LEFT(CONVERT(VARCHAR(5),InTime,108),5) + '_' + LEFT(CONVERT(VARCHAR(5),OutTime,108),5)),':','') FROM #tmpBaseData WHERE NOT (InTime IS NULL AND OutTime IS NULL) AND NOT (InTime = '00:00:00.0000000' AND OutTime = '00:00:00.0000000') AND [Hours] <> 0 ) ,PivotedIOHrs AS ( SELECT GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,RankOrder ,SunHrs = [1],MonHrs = [2],TueHrs = [3] ,WedHrs = [4],ThuHrs = [5],FriHrs = [6],SatHrs = [7] FROM HrsIOData PIVOT ( MIN(strIOValue) FOR tintDayOfWeek IN ([1],[2],[3],[4],[5],[6],[7]) ) P ) SELECT SprocStep = 'cteResults_IO', ETimeType = E.TimeType,X.RankOrder ,NewRankOrder = DENSE_RANK() OVER (PARTITION BY E.GroupCode,E.weDate,E.SSN,E.EmployeeID,E.AssignmentNo,E.SiteNo,E.DeptNo ORDER BY CASE E.TimeType WHEN 'R' /*@XXPAYCODE*/ THEN 0 ELSE 1 END) ,E.SSN,E.EmployeeID,E.EmpName ,E.FileBreakID,E.weDate,E.AssignmentNo ,E.SiteNo,E.DeptNo ,Line1 = E.AssignmentNo + @Delim + E.Last4SSN + @Delim + E.CollectFrmt + @Delim + E.ReportingInt + @Delim + E.BranchID + @Delim + E.GroupID + @Delim + E.TimesheetDate + @Delim + ISNULL(X.MonHrs,'') + @Delim + ISNULL(X.TueHrs,'') + @Delim + ISNULL(X.WedHrs,'') + @Delim + ISNULL(X.ThuHrs,'') + @Delim + ISNULL(X.FriHrs,'') + @Delim + ISNULL(X.SatHrs,'') + @Delim + ISNULL(X.SunHrs,'') + @Delim + RIGHT('0000' + REPLACE(CAST(E.TotalAssignmentHrs AS VARCHAR),'.',''),CASE WHEN E.TotalAssignmentHrs >= 100 THEN 5 ELSE 4 END) + @Delim + 'IO' --TransType + @Delim + E.Confirmation + @Delim + E.TransType + @Delim + E.Individual + @Delim + E.[Timestamp] + @Delim + E.ExpenseMiles + @Delim + E.ExpenseDollars + @Delim + E.[Status] + @Delim + E.Optional1 + @Delim + E.Optional2 + @Delim + E.Optional3 + @Delim + E.Optional4 + @Delim + E.Optional5 + @Delim + E.Optional6 + @Delim + E.Optional7 + @Delim + E.Optional8 + @Delim + E.Optional9 + @Delim + E.AuthTimestamp + @Delim + E.AuthEmail + @Delim + E.AuthConfirmNo + @Delim + E.AuthComments + @Delim + E.WorkRules + @Delim + E.Rounding + @Delim + E.WeekEndDay + @Delim + E.PayOnly + @Delim + E.BPO + @Delim + E.DisputedCode ,E.GroupCode,E.PayrollType ,SnapshotDateTime = GETDATE() ,TimeType = 'IO' FROM #tmpExport E INNER JOIN PivotedIOHrs X ON X.GroupCode = E.GroupCode AND X.weDate = E.weDate AND X.SSN = E.SSN AND X.EmployeeID = E.EmployeeID AND X.AssignmentNo = E.AssignmentNo AND X.BranchID = E.BranchID AND X.SiteNo = E.SiteNo AND X.DeptNo = E.DeptNo WHERE ( (E.TimeType = @REGPAYCODE AND E.ForOrderBy = 1) OR (E.TimeType <> @REGPAYCODE AND X.RankOrder = 1) ) ;WITH HrsIOData AS ( SELECT GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ,RankOrder = RANK() OVER ( PARTITION BY GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,tintDayOfWeek ORDER BY TransDate, CASE InDay WHEN 7 THEN CASE tintDayOfWeek WHEN 1 THEN InDay - 7 ELSE InDay END WHEN 1 THEN CASE tintDayOfWeek WHEN 7 THEN InDay + 7 ELSE InDay END ELSE InDay END,InTime ) ,strIOValue = REPLACE((LEFT(CONVERT(VARCHAR(5),InTime,108),5) + '_' + LEFT(CONVERT(VARCHAR(5),OutTime,108),5)),':','') FROM #tmpBaseData WHERE NOT (InTime IS NULL AND OutTime IS NULL) AND NOT (InTime = '00:00:00.0000000' AND OutTime = '00:00:00.0000000') AND [Hours] <> 0 ) ,PivotedIOHrs AS ( SELECT GroupCode,weDate,SSN,EmployeeID,AssignmentNo,BranchID,SiteNo,DeptNo,TotalHrs,RankOrder ,SunHrs = [1],MonHrs = [2],TueHrs = [3] ,WedHrs = [4],ThuHrs = [5],FriHrs = [6],SatHrs = [7] FROM HrsIOData PIVOT ( MIN(strIOValue) FOR tintDayOfWeek IN ([1],[2],[3],[4],[5],[6],[7]) ) P ) ,cteResults AS ( SELECT ETimeType = E.TimeType,X.RankOrder ,NewRankOrder = DENSE_RANK() OVER (PARTITION BY E.GroupCode,E.weDate,E.SSN,E.EmployeeID,E.AssignmentNo,E.SiteNo,E.DeptNo ORDER BY CASE E.TimeType WHEN 'R' /*@XXPAYCODE*/ THEN 0 ELSE 1 END) ,E.SSN,E.EmployeeID,E.EmpName ,E.FileBreakID,E.weDate,E.AssignmentNo ,E.SiteNo,E.DeptNo ,Line1 = E.AssignmentNo + @Delim + E.Last4SSN + @Delim + E.CollectFrmt + @Delim + E.ReportingInt + @Delim + E.BranchID + @Delim + E.GroupID + @Delim + E.TimesheetDate + @Delim + ISNULL(X.MonHrs,'') + @Delim + ISNULL(X.TueHrs,'') + @Delim + ISNULL(X.WedHrs,'') + @Delim + ISNULL(X.ThuHrs,'') + @Delim + ISNULL(X.FriHrs,'') + @Delim + ISNULL(X.SatHrs,'') + @Delim + ISNULL(X.SunHrs,'') + @Delim + RIGHT('0000' + REPLACE(CAST(E.TotalAssignmentHrs AS VARCHAR),'.',''),CASE WHEN E.TotalAssignmentHrs >= 100 THEN 5 ELSE 4 END) + @Delim + 'IO' --TransType + @Delim + E.Confirmation + @Delim + E.TransType + @Delim + E.Individual + @Delim + E.[Timestamp] + @Delim + E.ExpenseMiles + @Delim + E.ExpenseDollars + @Delim + E.[Status] + @Delim + E.Optional1 + @Delim + E.Optional2 + @Delim + E.Optional3 + @Delim + E.Optional4 + @Delim + E.Optional5 + @Delim + E.Optional6 + @Delim + E.Optional7 + @Delim + E.Optional8 + @Delim + E.Optional9 + @Delim + E.AuthTimestamp + @Delim + E.AuthEmail + @Delim + E.AuthConfirmNo + @Delim + E.AuthComments + @Delim + E.WorkRules + @Delim + E.Rounding + @Delim + E.WeekEndDay + @Delim + E.PayOnly + @Delim + E.BPO + @Delim + E.DisputedCode ,E.GroupCode,E.PayrollType ,SnapshotDateTime = GETDATE() ,TimeType = 'IO' FROM #tmpExport E INNER JOIN PivotedIOHrs X ON X.GroupCode = E.GroupCode AND X.weDate = E.weDate AND X.SSN = E.SSN AND X.EmployeeID = E.EmployeeID AND X.AssignmentNo = E.AssignmentNo AND X.BranchID = E.BranchID AND X.SiteNo = E.SiteNo AND X.DeptNo = E.DeptNo WHERE ( (E.TimeType = @REGPAYCODE AND E.ForOrderBy = 1) OR (E.TimeType <> @REGPAYCODE AND X.RankOrder = 1) ) UNION SELECT ETimeType = '',RankOrder = 0,NewRankOrder = 0 ,SSN,EmployeeID,EmpName ,FileBreakID,weDate,AssignmentNo ,SiteNo,DeptNo ,Line1 = AssignmentNo + @Delim + Last4SSN + @Delim + CollectFrmt + @Delim + ReportingInt + @Delim + BranchID + @Delim + GroupID + @Delim + TimesheetDate + @Delim + CASE WHEN MonHrs IN ('0','0.00','0000','') THEN '' ELSE RIGHT('0000' + REPLACE(MonHrs,'.',''),4) END + @Delim + CASE WHEN TueHrs IN ('0','0.00','0000','') THEN '' ELSE RIGHT('0000' + REPLACE(TueHrs,'.',''),4) END + @Delim + CASE WHEN WedHrs IN ('0','0.00','0000','') THEN '' ELSE RIGHT('0000' + REPLACE(WedHrs,'.',''),4) END + @Delim + CASE WHEN ThuHrs IN ('0','0.00','0000','') THEN '' ELSE RIGHT('0000' + REPLACE(ThuHrs,'.',''),4) END + @Delim + CASE WHEN FriHrs IN ('0','0.00','0000','') THEN '' ELSE RIGHT('0000' + REPLACE(FriHrs,'.',''),4) END + @Delim + CASE WHEN SatHrs IN ('0','0.00','0000','') THEN '' ELSE RIGHT('0000' + REPLACE(SatHrs,'.',''),4) END + @Delim + CASE WHEN SunHrs IN ('0','0.00','0000','') THEN '' ELSE RIGHT('0000' + REPLACE(SunHrs,'.',''),4) END + @Delim + RIGHT('0000' + REPLACE(CAST(TotalHrs AS VARCHAR),'.',''),CASE WHEN TotalHrs >= 100 THEN 5 ELSE 4 END) + @Delim + TimeType + @Delim + Confirmation + @Delim + TransType + @Delim + Individual + @Delim + [Timestamp] + @Delim + ExpenseMiles + @Delim + ExpenseDollars + @Delim + [Status] + @Delim + Optional1 + @Delim + Optional2 + @Delim + Optional3 + @Delim + Optional4 + @Delim + Optional5 + @Delim + Optional6 + @Delim + Optional7 + @Delim + Optional8 + @Delim + Optional9 + @Delim + AuthTimestamp + @Delim + AuthEmail + @Delim + AuthConfirmNo + @Delim + AuthComments + @Delim + WorkRules + @Delim + Rounding + @Delim + WeekEndDay + @Delim + PayOnly + @Delim + BPO + @Delim + DisputedCode ,GroupCode,PayrollType ,SnapshotDateTime = GETDATE() ,TimeType FROM #tmpExport ) SELECT SSN,EmployeeID,EmpName ,FileBreakID,weDate,AssignmentNo ,SiteNo,DeptNo ,Line1 ,GroupCode,PayrollType ,SnapshotDateTime ,TimeType FROM cteResults WHERE NewRankOrder IN (0,1) --AND Line1 IS NOT NULL ORDER BY SSN,AssignmentNo ,CASE TimeType WHEN 'R' THEN 1 WHEN 'O' THEN 2 WHEN 'D' THEN 3 WHEN 'IO' THEN 4 END