How to avoid repeating the same record in stored procedure using while?

I am writing a stored procedure which will need me to calculate the time dynamically by shift.

enter image description here

Basically the first row are fixed, so the row after that should be able to generate itself with a simple codes like below.

Can anybody help me with this? My while is giving the same records. I have attach an image so that it will be easier to understand.

CREATE TABLE #TimeSlot
(    
         Parameter nvarchar(Max) null,
         DisplayStartTime varchar(32) not null,
         DisplayEndTime nvarchar(50)null,
         CodeEndTime nvarchar(50) null,
         Frequency int null,
         Tolerance int null
)   

INSERT INTO #TimeSlot(Parameter, DisplayStartTime, DisplayEndTime, CodeEndTime, Frequency, Tolerance)
   SELECT DISTINCT 
      Parameter, '23:00:00', 
      LTRIM((RIGHT(CONVERT(VARCHAR(100), DATEADD(minute, convert(int, CONVERT(int, Frequency)), '23:00:00'), 120), 8))) as DisplayEndTime
      ,LTRIM((RIGHT(CONVERT(VARCHAR(100), DATEADD(minute, convert(int, CONVERT(int, Frequency) + Tolerance), '23:00:00'), 120), 8))) as CodeEndTime
,Frequency,Tolerance
   FROM
      tableTest

WHILE (@intFlag <=3)
BEGIN
    INSERT INTO #TimeSlot (Parameter, DisplayStartTime, DisplayEndTime, CodeEndTime, Frequency, Tolerance)
       SELECT
          Parameter, DisplayEndTime,
          LTRIM((RIGHT(CONVERT(VARCHAR(100), DATEADD(minute, convert(int, CONVERT(int, Frequency)), DisplayEndTime), 120), 8))) as DisplayEndTime,
          LTRIM((RIGHT(CONVERT(VARCHAR(100), DATEADD(minute, convert(int, CONVERT(int, Frequency) + Tolerance), DisplayEndTime), 120), 8))) as CodeEndTime,
          Frequency,
          Tolerance 
       FROM 
          #TimeSlot

    SET @intFlag = @intFlag + 1
End


Source: sql

Leave a Reply