Link to home
Start Free TrialLog in
Avatar of yadavdep
yadavdepFlag for India

asked on

Alternative to sql server cursor

I have a requirement to run a complex formula and update columns in a table. For this I need to loop through rows in table.
Currently I am using sql cursor for it but the end customer get Timeout error in Asp.net application because this  in one SP I have three nested cursors.

I need to know what alternatives would be helpful for me without lossing to much of performance?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Without the script it will be hard to help you.
Avatar of HainKurt
HainKurt
Flag of Canada image
I would say, dont use cursor and convert it to single DML but...  not sure if it is possible :)
ASKER CERTIFIED SOLUTION
Avatar of Russell Fox
Russell Fox
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of yadavdep
yadavdep
Flag of India image

ASKER

Here is the two SPs

ALTER PROCEDURE [dbo].[CalculateGroupJobActualTime] -- 41276
      @DayID BIGINT  
AS  
BEGIN
      BEGIN TRY  
            BEGIN TRANSACTION CalculateGroupJobActualTime;        
             
            DECLARE @StartTime DATETIME, @CrewEmpCount INT, @JobTimeIDs NVARCHAR(MAX),@ManHours DECIMAL(18,2)
            DECLARE  @AcualManHrs DECIMAL(18,2),@CurrentDayID BIGINT,@GroupJobOrder INT
                  DECLARE @TotalGroupJobsCount INT, @TotalGroupJobsTIME DECIMAL(18,2)

                  SELECT @CrewEmpCount = COUNT(*)       FROM DayCrewEmployee WHERE DayID=@DayID                   
                  SELECT  @ManHours = SUM(DATEDIFF(second, StartTime, EndTime) / 3600.0)/@CrewEmpCount FROM JobTime Where DayID = @DayID      

                  SET @TotalGroupJobsTime = (SELECT SUM(DATEDIFF(second, StartTime, EndTime) / 3600.0)/@CrewEmpCount FROM JobTime Where DayID = @DayID)
                  SET @TotalGroupJobsCount = (SELECT COUNT(*) FROM DAY WHERE ParentDayID = @DayID OR DayID = @DayID)
                      
                  CREATE TABLE #TempTime
                  (  
                        TempTimeID BIGINT IDENTITY(1,1),
                        StartTime DATETIME,
                        EndTime DATETIME                        
                  )
                  INSERT INTO #TempTime(StartTime,EndTime)
                  SELECT  DISTINCT StartTime,EndTime
                  FROM JobTime
                  WHERE DayID= @DayID
                  ORDER BY StartTime
                 
                  --Delete JObtime as it contain whole group time for the parent DayID                                    
                  DELETE JT FROM JobTime JT
                  INNER JOIN Day D   ON D.DayID = JT.DayID
                  WHERE D.ParentDayID= @DayID OR D.DayID = @DayID
                  --END
                  
           

                  DECLARE  ActualTime CURSOR LOCAL  FOR          
                                    SELECT DayID ,ISNULL(GroupJobOrder, 0)
                                    FROM Day
                                    WHERE ParentDayID= @DayID OR DayID = @DayID
                                    ORDER BY GroupJobOrder ASC
                  
                  OPEN ActualTime          
                  FETCH NEXT FROM ActualTime          
                  INTO @CurrentDayID ,@GroupJobOrder
                  
                  WHILE @@FETCH_STATUS = 0          
                  BEGIN              
                        IF(SELECT SUM(budgetedManhrs) FROM Day WHERE ParentDayID=@DayID OR DayID = @DayID ) > 0
                        BEGIN
                              SET @AcualManHrs = ((SELECT budgetedManhrs FROM Day WHERE DayID = @CurrentDayID )/
                              (SELECT SUM(budgetedManhrs) FROM Day WHERE ParentDayID = @DayID OR DayID = @DayID )) * @ManHours
                                     
                              IF @StartTime IS NULL
                                    SET @StartTime = (SELECT TOP 1 StartTime FROm #TempTime)
                                    -- SELECt 'main',@AcualManHrs,@CurrentDayID,@GroupJobOrder,@StartTime as startTime,DATEADD(ss,@AcualManHrs*60*60,@StartTime) as EndTime,@ManHours
                              IF (SELECT TOP 1 EndTime FROM #TempTime) >= DATEADD(ss,@AcualManHrs*60*60,@StartTime)
                              BEGIN                                    
                                    INSERT INTO JobTime(DayID,EmployeeID, StartTime, EndTime,ManHour)    
                                    SELECT @CurrentDayID, EmployeeID, @StartTime, DATEADD(ss,@AcualManHrs*60*60,@StartTime),DATEDIFF(second, @StartTime, DATEADD(ss,@AcualManHrs*60*60,@StartTime)) / 3600.0 FROM DayCrewEmployee WHERE DayID=@CurrentDayID  
                                                            
                                    UPDATE Day SET ManHours = @AcualManHrs ,StartTime = @StartTime ,EndTime = DATEADD(ss,@AcualManHrs*60*60,@StartTime),
                                    JobCharge = ISNULL(CASE WHEN (BillType  = 'T' AND IsBillAsChargePerJob=0) THEN
                                                      CASE WHEN (MinimumCharge > (HourlyRate * (SELECT Sum(JobTime.ManHour) FROM JobTime WHERE DayID = Day.DayID))) THEN MinimumCharge
                                                      ELSE (HourlyRate * (SELECT Sum(JobTime.ManHour) FROM JobTime WHERE DayID = Day.DayID)) END
                                                      ELSE JobCharge END, 0) WHERE DayID = @CurrentDayID
                                                                    
                                    --Calculate Travel Time                                        
                                    SELECT @JobTimeIDs = COALESCE(@JobTimeIDs + ', ', '') + CAST(JobTimeID AS NVARCHAR) FROM JobTime WHERE DayID=@DayID
                                    IF  @GroupJobOrder = 0
                                          EXEC [CalculateTravelTime] @CurrentDayID, FALSE, @JobTimeIDs
                                     
                                    SET @StartTime = DATEADD(ss,@AcualManHrs*60*60,@StartTime)
                                    --IF (SELECT TOP 1 EndTime FROM #TempTime) = DATEADD(ss,@AcualManHrs*60*60,@StartTime)
                                    IF ((SELECT TOP 1 EndTime FROM #TempTime) = DATEADD(ss,@AcualManHrs*60*60,@StartTime) AND ((SELECT COUNT(*) FROM #TempTime) > 1))
                                    BEGIN
                                          DELETE TOP (1) FROM   #TempTime
                                          SET @StartTime = NULL
                                    END

                                    EXECUTE [UpdateDayManRatePerHour] @CurrentDayID                                    

                              END
                              ELSE
                              BEGIN      
                              --For multy time entry for ex - gropu time is 8-9,10-12
                                    DECLARE @TempAcualManHrs  DECIMAL(18,2)
                                    SET @AcualManHrs = ((SELECT budgetedManhrs FROM Day WHERE DayID = @CurrentDayID )/
                                                            (SELECT SUM(budgetedManhrs) FROM Day WHERE ParentDayID = @DayID OR DayID = @DayID )) * @ManHours
                                     
                                    WHILE @AcualManHrs > 0
                                    BEGIN                                    
                                          IF @StartTime IS NULL
                                                SET @StartTime = (SELECT TOP 1 StartTime FROm #TempTime)
                                          
                                          SET @TempAcualManHrs = (SELECT TOP 1 DATEDIFF(second, @StartTime, EndTime) / 3600.0 FROM #TempTime)
                                          --SELECt @CurrentDayID,@StartTime,@TempAcualManHrs AS TEmp,@AcualManHrs AS ActualManHrs
                                     
                                          IF  @TempAcualManHrs < @AcualManHrs
                                          BEGIN                                          
                                                UPDATE Day SET ManHours = @AcualManHrs ,StartTime = @StartTime WHERE DayID = @CurrentDayID
                                                
                                                INSERT INTO JobTime(DayID,EmployeeID, StartTime, EndTime,ManHour)    
                                                SELECT @CurrentDayID, EmployeeID, @StartTime, DATEADD(ss,@TempAcualManHrs*60*60,@StartTime),@TempAcualManHrs FROM DayCrewEmployee WHERE DayID=@CurrentDayID  
                                                                                     
                                                --Calculate Travel Time    
                                                SELECT @JobTimeIDs = COALESCE(@JobTimeIDs + ', ', '') + CAST(JobTimeID AS NVARCHAR) FROM JobTime WHERE DayID=@DayID
                                                IF  @GroupJobOrder = 0
                                                      EXEC [CalculateTravelTime] @CurrentDayID, FALSE, @JobTimeIDs
                                                                                                 
                                                IF (SELECT TOP 1 EndTime FROM #TempTime) = DATEADD(ss,@TempAcualManHrs*60*60,@StartTime)
                                                BEGIN
                                                      DELETE TOP (1) FROM   #TempTime
                                                      SET @StartTime = NULL
                                                END
                                                ELSE                                                
                                                      SET @StartTime = DATEADD(ss,@TempAcualManHrs*60*60,@StartTime)
                                                SET @AcualManHrs = @AcualManHrs - @TempAcualManHrs
                                          END
                                          ELSE                                    
                                          BEGIN
                                                UPDATE Day SET EndTime = DATEADD(ss,@AcualManHrs*60*60,@StartTime),
                                                                              JobCharge = ISNULL(CASE WHEN (BillType  = 'T' AND IsBillAsChargePerJob=0) THEN
                                                                              CASE WHEN (MinimumCharge > (HourlyRate * (SELECT Sum(JobTime.ManHour) FROM JobTime WHERE DayID = Day.DayID))) THEN MinimumCharge
                                                                              ELSE (HourlyRate * (SELECT Sum(JobTime.ManHour) FROM JobTime WHERE DayID = Day.DayID)) END
                                                                              ELSE JobCharge END, 0)WHERE DayID = @CurrentDayID

                                                INSERT INTO JobTime(DayID,EmployeeID, StartTime, EndTime,ManHour)    
                                                SELECT @CurrentDayID, EmployeeID, @StartTime, DATEADD(ss,@AcualManHrs*60*60,@StartTime),DATEDIFF(second, @StartTime, DATEADD(ss,@AcualManHrs*60*60,@StartTime)) / 3600.0 FROM DayCrewEmployee WHERE DayID=@CurrentDayID  
                                                        
                                                --Calculate Travel Time                                                    
                                                SELECT @JobTimeIDs = COALESCE(@JobTimeIDs + ', ', '') + CAST(JobTimeID AS NVARCHAR) FROM JobTime WHERE DayID=@DayID
                                                IF  @GroupJobOrder = 0
                                                      EXEC [CalculateTravelTime] @CurrentDayID, FALSE, @JobTimeIDs

                                                SET @StartTime = DATEADD(ss,@AcualManHrs*60*60,@StartTime)
                                                 
                                                SET @AcualManHrs = @AcualManHrs - @TempAcualManHrs
                                          END
                                    END
                              END
                  END
                        ELSE
                        BEGIN
                              -- WHEN ALL THE GROUP JOBS HAVE 0 BUDGETED TIME

                              --SET @AcualManHrs = ((SELECT budgetedManhrs FROM Day WHERE DayID = @CurrentDayID )/ (SELECT SUM(budgetedManhrs) FROM Day WHERE ParentDayID = @DayID OR DayID = @DayID )) * @ManHours

                              SET @AcualManHrs = (@TotalGroupJobsTime / @TotalGroupJobsCount)
                                     
                              IF @StartTime IS NULL
                                    SET @StartTime = (SELECT TOP 1 StartTime FROm #TempTime)

                                    
                              IF (SELECT TOP 1 EndTime FROM #TempTime) >= DATEADD(ss,@AcualManHrs*60*60,@StartTime)
                              BEGIN
                                    INSERT INTO JobTime(DayID,EmployeeID, StartTime, EndTime,ManHour)    
                                    SELECT @CurrentDayID, EmployeeID, @StartTime, DATEADD(ss,@AcualManHrs*60*60,@StartTime),DATEDIFF(second, @StartTime, DATEADD(ss,@AcualManHrs*60*60,@StartTime)) / 3600.0 FROM DayCrewEmployee WHERE DayID=@CurrentDayID  
                                                            
                                    UPDATE Day SET ManHours = @AcualManHrs ,StartTime = @StartTime ,EndTime = DATEADD(ss,@AcualManHrs*60*60,@StartTime),
                                    JobCharge = ISNULL(CASE WHEN (BillType  = 'T' AND IsBillAsChargePerJob=0) THEN
                                                      CASE WHEN (MinimumCharge > (HourlyRate * (SELECT Sum(JobTime.ManHour) FROM JobTime WHERE DayID = Day.DayID))) THEN MinimumCharge
                                                      ELSE (HourlyRate * (SELECT Sum(JobTime.ManHour) FROM JobTime WHERE DayID = Day.DayID)) END
                                                      ELSE JobCharge END, 0) WHERE DayID = @CurrentDayID
                                                                    
                                    --Calculate Travel Time                                        
                                    SELECT @JobTimeIDs = COALESCE(@JobTimeIDs + ', ', '') + CAST(JobTimeID AS NVARCHAR) FROM JobTime WHERE DayID=@DayID
                                    IF  @GroupJobOrder = 0
                                          EXEC [CalculateTravelTime] @CurrentDayID, FALSE, @JobTimeIDs
                                     
                                    SET @StartTime = DATEADD(ss,@AcualManHrs*60*60,@StartTime)
                                    --IF (SELECT TOP 1 EndTime FROM #TempTime) = DATEADD(ss,@AcualManHrs*60*60,@StartTime)
                                    IF ((SELECT TOP 1 EndTime FROM #TempTime) = DATEADD(ss,@AcualManHrs*60*60,@StartTime) AND ((SELECT COUNT(*) FROM #TempTime) > 1))
                                    BEGIN
                                          DELETE TOP (1) FROM   #TempTime
                                          SET @StartTime = NULL
                                    END

                                    EXECUTE [UpdateDayManRatePerHour] @CurrentDayID                        
                              END
                        END
                  FETCH NEXT FROM ActualTime          
                  INTO @CurrentDayID  ,@GroupJobOrder        
                  END  
                  CLOSE ActualTime          
                  DEALLOCATE ActualTime
            COMMIT TRANSACTION CalculateGroupJobActualTime;  
      END TRY    
      BEGIN CATCH  
            ROLLBACK TRANSACTION CalculateGroupJobActualTime;  

            DECLARE @ErMessage NVARCHAR(2048), @ErSeverity INT, @ErState INT
            SELECT @ErMessage = '[ ERROR_NUMBER : ' + CAST(ERROR_NUMBER() AS VARCHAR(50)) + ' ] ' +  '[ ERROR_LINE : ' + CAST(ERROR_LINE() AS VARCHAR(50)) + ' ] ' + ERROR_MESSAGE(),
            @ErSeverity = ERROR_SEVERITY(), @ErState = ERROR_STATE()
            RAISERROR (@ErMessage, @ErSeverity, @ErState)
      END CATCH;
END
Avatar of yadavdep
yadavdep
Flag of India image

ASKER

ALTER PROCEDURE [dbo].[CalculateTravelTime]    
      @RecordID BIGINT = 31074,
      @ReCalculateTravel BIT = 1,
      @JobTimeIDs NVARCHAR(500) = ''
AS  
BEGIN
 
 --Process All crew one by one  
      DECLARE  @CrewID BIGINT    
      DECLARE  @Date Date =NULL
      if @RecordID IS NOT NULL
      BEGIN
      SELECT @Date = StartTime FROM Day WHERE DayID= @RecordID
      END
      DECLARE  TempCursor CURSOR FOR          
       SELECT Distinct CrewID FROM Day WHERE  (DayID =  @RecordID AND  @RecordID IS NOT  NULL) OR( @RecordID IS NULL AND 1=1)
      OPEN TempCursor          
      FETCH NEXT FROM TempCursor          
      INTO @CrewID
      WHILE @@FETCH_STATUS = 0          
      BEGIN        
            DECLARE @PreJobtravelTime Decimal(18,2)=0.0                  
            --Process All job if a crew for travel time calculation  
            DECLARE @Dates Date  
            DECLARE  JobCursor CURSOR FOR  
            SELECT DISTINCT CONVERT (DATE, JT.StartTime) StartDates FROM JObTime JT  
            INNER JOIN Day D ON D.DayID=JT.DayID AND D.CrewID=@CrewID  
            --WHERE CONVERT(DATE, JT.StartTime) = @Date OR @Date IS NULL
            ORDER BY StartDates Asc  
            OPEN JobCursor  
            FETCH NEXT FROM JObCursor          
            INTO @Dates  
            WHILE @@FETCH_STATUS = 0          
            BEGIN    
                  -- Process all Employee  
                  DECLARE @EmployeeID BIGINT  
                  DECLARE  EmployeeCursor CURSOR FOR  
                  SELECT DISTINCT  JT.EmployeeID From JobTime JT INNER JOIN  
                  Day on Day.DayID = JT.DayID AND Day.CrewID = @CrewID  
                  WHERE CONVERT (DATE, JT.StartTime) = @Dates  
                  
                  OPEN EmployeeCursor  
                  FETCH NEXT FROM EmployeeCursor          
                  INTO @EmployeeID
                  WHILE @@FETCH_STATUS = 0          
                  BEGIN
                    
                  DECLARE @Seq INT=0  
                  DECLARE @PreviousJobTimeID BIGINT = NULL  
                  DECLARE @PreviousEndTime DATETIME = NULL  
                  DECLARE @PreviousDayID BIGINT =0
                  DECLARE @DistinctDayRecord INT =0
                  
                  SELECT  Distinct @DistinctDayRecord = Count(JT.DayID) FROM JobTime JT  
                  INNER JOIN Day ON Day.DayID=JT.DayID AND Day.CrewID = @CrewID  
                  INNER JOIN DayCrewEmployee DCE ON DCE.CrewID= Day.CrewID  
                  WHERE CONVERT (DATE, JT.StartTime) = @Dates AND JT.EmployeeID = @EmployeeID
                  
                  DECLARE @DayID BIGINT,@StartTime DATETIME, @EndTime DATETIME , @JobTimeID BIGINT  
                  DECLARE  JobTimeCursor CURSOR FOR  
                  SELECT Distinct JT.DayID, JT.StartTime, JT.EndTime, JT.JobTimeID FROM JobTime JT  
                  INNER JOIN Day ON Day.DayID=JT.DayID AND Day.CrewID = @CrewID  
                  INNER JOIN DayCrewEmployee DCE ON DCE.CrewID= Day.CrewID  
                  WHERE CONVERT (DATE, JT.StartTime) = @Dates AND JT.EmployeeID = @EmployeeID  --DCE.EmployeeID = @EmployeeID  
                  ORDER BY JT.StartTime ASC  
                                    OPEN JobTimeCursor  
                  FETCH NEXT FROM JobTimeCursor          
                  INTO @DayID, @StartTime, @EndTime, @JobTimeID  

                  WHILE @@FETCH_STATUS = 0          
                  BEGIN
             
                  DECLARE @ExistEmpTimeEntry BIT=0      
                  
                  IF EXISTS(SELECt * FROM TimeClock WHERE EntryDate = @Dates AND EmployeeID = @EmployeeID)
                  BEGIN
                        SET @ExistEmpTimeEntry = 1
                  END
                  --If nd Time Is null Set End time Equal To Start Time
                  IF @EndTime IS NULL
                        SET @EndTime = @StartTime
                   SELECT @dayID AS DayID, @ExistEmpTimeEntry AS ExistEmpTimeEntry,@JobTimeID AS JobTimeID, @ReCalculateTravel AS ReCalculateTravel,
                   @EmployeeID AS EmployeeID, @JobTimeIDs AS JobTimeIDs,@Seq AS Seq,@PreviousEndTime AS PreviousEndTime,@Dates AS Dates
                   ,@StartTime AS Starttime, @EndTime As EndTime
                  -- Reset travel hours in JobTime  
                  
                  DECLARE @TravelTM Decimal(18,2)=0.00  
                  IF (SELECt Top 1 DayID FROM JobTime WHERE EmployeeID = @EmployeeID
                  AND CONVERT (DATE, StartTime) = @Dates  AND EndTime <= @StartTime ) =@DayID
                  BEGIN            
                        SET @TravelTM +=0.00                        
                  END
                  ELSE IF @Seq = 0  
                  BEGIN  
                        IF @ExistEmpTimeEntry = 0
                        BEGIN
                              SET @TravelTM +=0.00
                        END
                        ELSE
                        BEGIN
                              SELECT @TravelTM =  @TravelTM +  ISNULL(SUM(CAST(DATEDIFF(ss, Checkin, CheckOut ) AS DECIMAL(18,2)) / 3600 ),0.00) FROM TimeClock    
                              WHERE EntryDate = @Dates AND EmployeeID = @EmployeeID AND CheckOut <= @StartTime  

                              SELECT @TravelTM =  @TravelTM +  ISNULL(@TravelTM,0.0) + ISNULL(SUM(CAST(DATEDIFF(ss, Checkin, @StartTime ) AS DECIMAL(18,2)) / 3600 ),0.00) FROM TimeClock    
                              WHERE EntryDate = @Dates AND EmployeeID = @EmployeeID AND Checkin  < @StartTime AND ( Checkout > @StartTime  OR Checkout IS NULL )
                        END
                         --SELECT JobTimeID ,@JobTimeID as AJobTime,@JobTimeIDs AS JobTimeIDs FROM JOBTIME
                        --IF @PreviousJobTimeID <> @JobTimeID      
                        --BEGIN                              
                        --      SET @TravelTM =0.00
                        --END                                     
                  END  
                  ELSE  
                  BEGIN       
                        IF @ExistEmpTimeEntry = 0
                        BEGIN            
                              SELECT @TravelTM += CAST(DATEDIFF(ss, @PreviousEndTime, @StartTime ) AS DECIMAL(18,2)) / 3600                               
                        END
                        ELSE IF EXISTS(SELECT  * FROM TimeClock    
                                    WHERE EntryDate = @Dates AND EmployeeID = @EmployeeID
                                    AND (Checkin  <= @PreviousEndTime AND Checkout >= @StartTime  
                                    OR(Checkin <= @PreviousEndTime AND (CheckOut <= @StartTime  AND CheckOut > @PreviousEndTime))
                                    OR(Checkin >= @PreviousEndTime AND CheckOut <= @StartTime )
                                    --  IF  checkout is null
                                    OR(Checkin <= @PreviousEndTime AND CheckOut IS NULL )
                                    OR(( Checkin >  @PreviousEndTime AND Checkin < @StartTime )AND CheckOut IS NULL )
                                    -- End
                                    OR (( Checkin  >= @PreviousEndTime AND Checkin  < @StartTime ) AND Checkout >= @StartTime  ))
                              )
                              OR
                                     EXISTS( SELECt * FROM TimeClock WHERE EntryDate = @Dates AND EmployeeID = @EmployeeID
                                    AND (( Checkin>= @StartTime AND Checkin <=@EndTime ) OR
                                    ( Checkout>= @StartTime AND Checkout <=@EndTime ))
                                    
                                    )
                        BEGIN                                     
                              SELECT @TravelTM =  
                              SUM(
                              CASE WHEN (Checkin  <= @PreviousEndTime AND  ( Checkout >= @StartTime  OR Checkout IS NULL )) THEN  CAST(DATEDIFF(ss, @PreviousEndTime, @StartTime ) AS DECIMAL(18,2))    
                              WHEN (Checkin <= @PreviousEndTime AND (CheckOut <= @StartTime  AND CheckOut > @PreviousEndTime)) THEN  CAST(DATEDIFF(ss, @PreviousEndTime, CheckOut ) AS DECIMAL(18,2))    
                              WHEN ( Checkin <= @PreviousEndTime  AND Checkout IS NULL ) THEN CAST(DATEDIFF(ss, @PreviousEndTime, @StartTime ) AS DECIMAL(18,2))    
                              WHEN (Checkin >= @PreviousEndTime AND CheckOut <= @StartTime ) THEN CAST(DATEDIFF(ss, Checkin, CheckOut ) AS DECIMAL(18,2))    
                              --IF  checkout is null
                              WHEN (Checkin <= @PreviousEndTime AND CheckOut IS NULL) THEN CAST(DATEDIFF(ss, @PreviousEndTime, @StartTime ) AS DECIMAL(18,2))    
                              WHEN (( Checkin >  @PreviousEndTime AND Checkin < @StartTime )AND CheckOut IS NULL ) THEN CAST(DATEDIFF(ss, Checkin, @StartTime ) AS DECIMAL(18,2))    
                              -- END
                              WHEN (( Checkin  >= @PreviousEndTime AND Checkin  < @StartTime ) AND Checkout >= @StartTime) THEN CAST(DATEDIFF(ss, Checkin, @StartTime ) AS DECIMAL(18,2))   END                          
                              )/3600 FROM TimeClock    
                              WHERE EntryDate = @Dates AND EmployeeID = @EmployeeID
                              AND (Checkin  <= @PreviousEndTime AND ( Checkout >= @StartTime  OR Checkout IS NULL )
                              OR(Checkin <= @PreviousEndTime AND (CheckOut <= @StartTime AND CheckOut > @PreviousEndTime))
                              OR ( Checkin <= @PreviousEndTime  AND Checkout IS NULL )
                              OR(Checkin >= @PreviousEndTime AND CheckOut <= @StartTime )
                              --  IF  checkout is null
                              OR(Checkin <= @PreviousEndTime AND CheckOut IS NULL )
                              OR(( Checkin >  @PreviousEndTime AND Checkin < @StartTime )AND CheckOut IS NULL )
                              -- End
                              OR (( Checkin  >= @PreviousEndTime AND Checkin  < @StartTime ) AND Checkout >= @StartTime  ))
                        END
                        ELSE
                        BEGIN            
                         SELECT @TravelTM += CAST(DATEDIFF(ss, @PreviousEndTime, @StartTime ) AS DECIMAL(18,2))/ 3600
                        END
                        
                          IF @TravelTM < 0
                                SET @TravelTM =0
                                
                        --IF @PreviousJobTimeID <> @JobTimeID      
                        --BEGIN                              
                        --      SET @TravelTM =0.00
                        --END                          
                  END                       
                  
                   IF EXISTS( SELECT * FROM JObTime WHERE (JobTimeID = @JobTimeID  AND @ReCalculateTravel = 1 ) OR (
                                          JobTimeID IN (Select Data FROM SplitData(@JobTimeIDs,',')) AND JobTimeID = @JobTimeID)     )
                        BEGIN
                              IF @Seq = 0
                              BEGIN                                                  
                                    UPDATE JobTime SET TravelHours  = ISNULL(@TravelTM,0.00) WHERE JobTimeID = @JobTimeID
                              END      
                              ELSE
                              BEGIN
                                    UPDATE JobTime SET TravelHours  += ISNULL(@TravelTM,0.00)/2 WHERE JobTimeID = @PreviousJobTimeID  
                                    UPDATE JobTime SET TravelHours  = ISNULL(@TravelTM,0.00)/2 WHERE JobTimeID = @JobTimeID
                              END                                                      
                        END      

                  IF  @Seq = @DistinctDayRecord-1 --@PreviousDayID =0 AND @RecordID IS NOT NULL
                  SET @PreviousDayID= @DayID
                  
                  SET @Seq += 1
                  -- Set the Pre PreJobTimeID                     
                  SET @PreviousJobTimeID = @JobTimeID  
                  SET @PreviousEndTime = @EndTime  

                  FETCH NEXT FROM JobTimeCursor          
                  INTO  @DayID, @StartTime, @EndTime, @JobTimeID  
                  END  

                  CLOSE JobTimeCursor          
                  DEALLOCATE JobTimeCursor  
                    
                  
                  FETCH NEXT FROM EmployeeCursor          
                  INTO @EmployeeID  
                  END  

                  CLOSE EmployeeCursor          
                  DEALLOCATE EmployeeCursor  
             
             
            FETCH NEXT FROM JobCursor          
            INTO  @Dates  
            END  
             
            CLOSE JobCursor          
            DEALLOCATE JobCursor  

      FETCH NEXT FROM TempCursor          
      INTO @CrewID            
      END    

      CLOSE TempCursor          
      DEALLOCATE TempCursor
      --Select @RecordID,@PreviousDayID
            --Update TravelTime in the day table
      --UPDATE Day SET TravelTime =ISNULL((SELECt SUM(TravelHours) FROM JObTime WHERE JObtime.DayID=Day.DayID),TravelTime) /(SELECT Count(Distinct EmployeeID) FROM DayCrewEmployee WHERE DayCrewEmployee.DayID =Day.DayID )
      --WHERE (Day.DayID= Day.DayID AND  @RecordID IS NULL  ) OR (CONVERT(DATE, Day.StartTime) = @Date)  --Day.DayID IN (@RecordID,@PreviousDayID) OR (Day.DayID= Day.DayID AND @RecordID IS NULL )
      
      UPDATE Day SET TravelTime =ISNULL((SELECt SUM(TravelHours) FROM JObTime WHERE JObtime.DayID=Day.DayID),TravelTime)  
      WHERE (Day.DayID= Day.DayID AND  @RecordID IS NULL  ) OR (CONVERT(DATE, Day.StartTime) = @Date)  --Day.DayID IN (@RecordID,@PreviousDayID) OR (Day.DayID= Day.DayID AND @RecordID IS NULL )
END
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Can you use the functionality of adding code into a code block? Will be much easier for us to copy the code then.
Thank you.