Add time to smalldatetime

Question

I want to run an UPDATE query to set a sequenced time on a smalldatetime column. All dates are set to a general smalldatetime with hh:min:ss of 00:00:00 but I want to update it with a specific time automatically. The table schema is below (only showing relevant columns). The date in question is named OriginalTargetDate. The time of the first record will be 08:30:00 and then increment by X minutes based on a capacity integer (will use 6 as an example). So using a future date such as 2021-06-16 the first record should be 2021-06-16 08:30:00 and then the 2nd row for that date would be 2021-06-16 10:00:00 etc. So if 6 jobs will have that date then the time would increment by approx 90 minutes.

Below is schema.

CREATE TABLE [dbo].[RepairOrder](
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [RepairOrderID] [int] NULL,
    [OriginalTargetDate] [smalldatetime] NULL,
    [JobSize] [char](1) NULL,
 CONSTRAINT [PK_RepairOrder] PRIMARY KEY CLUSTERED 
(
    [RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

Hi @David Chase ,

Please refer below updated statement:

;WITH numbering AS (  
 SELECT OriginalTargetDate,  
       start_time = convert(smalldatetime, '20210616 08:30'),  
        end_time = convert(smalldatetime, '20210616 16:00'),   
        rowno = row_number() OVER(partition by OriginalTargetDate ORDER BY RecordID),  
        cnt = COUNT(*) over (partition by OriginalTargetDate)  
FROM   RepairOrder  
)  
UPDATE numbering  
SET  OriginalTargetDate = dateadd(MINUTE, (rowno - 1) * datediff(MINUTE, start_time, end_time)/(cnt-1), start_time)  

select * from [RepairOrder]  

EDIT on 2021-06-16:

;WITH numbering AS (  
SELECT OriginalTargetDate,  
    start_time = convert(smalldatetime, '20210616 08:30'),  
    end_time = convert(smalldatetime, '20210616 16:00'),   
    rowno = row_number() OVER(partition by cast(OriginalTargetDate as date) ORDER BY RecordID),  
    cnt = COUNT(*) over (partition by cast(OriginalTargetDate as date))  
FROM   RepairOrder  
)  
UPDATE numbering  
SET  OriginalTargetDate = dateadd(MINUTE, (rowno - 1) * datediff(MINUTE, start_time, end_time)/(cnt-1), start_time)  
  
select * from [RepairOrder]  

Output:

RecordID RepairOrderID OriginalTargetDate JobSize  
58393 64684 2021-06-16 08:30:00 NULL  
58399 64719 2021-06-16 10:00:00 NULL  
58433 64711 2021-06-16 11:30:00 NULL  
58439 64716 2021-06-16 13:00:00 NULL  
58454 64722 2021-06-16 14:30:00 NULL  
58460 64728 2021-06-16 16:00:00 NULL  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I don't follow exactly. Fir you talk about six-minutes increments and then all of a sudden it is ninety minutes.

But nevertheless, I think this should get you started:

; WITH numbering AS (
   SELECT OriginalTargetDate, start_time = convert(smalldatetime, '2021016 08:30'),
          rowno = row_number() OVER(PARTITION BY RepairOrderID ORDER BY RecordID)
   FROM   RepairOrder
)
UPDATE numbering
SET    OriginalTargetDate = dateadd(MINUTE, 6 * (rowno - 1), start_time)
WHERE   ...

If this too much off, please provide sample data as INSERT statements together with the expected result.

Answer 3

Sorry for not being clear. The 6 is for number of jobs in a day. So that between 8:30am and 4:00pm (08:30:00 and 16:00:00) there should be 6 time intervals with times of 08:30:00, 10:00:00, 11:30:00, 13:00:00, 14:30:00 and 16:00:00

If there were less than 6 jobs per day then the time gaps for that day would be larger.

I think what you have may work with a little math tweaking and the UPDATE would go against the RepairOrder table.

Answer 4

So here is another guess. Untested, due to lack of test data.

; WITH numbering AS (
   SELECT OriginalTargetDate, start_time = convert(smalldatetime, '2021016 08:30'),
          end_time = convert(smalldatetime, '2021016 16:00'), 
          rowno = row_number() OVER(PARTITION BY RepairOrderID ORDER BY RecordID),
          cnt = COUNT(*) OVER(PARTITION BY RepairOrderID)
   FROM   RepairOrder
)
UPDATE numbering
SET    OriginalTargetDate = dateadd(MINUTE, (rowno - 1) * datediff(MINUTE, start_time, end_time)/cnt, start_time)

Answer 5

I will do my best to give you an example of the data. Bear with me as I do not have a spreadsheet or table that I can use here.
I would add a WHERE to the CTE as follows.

FROM RepairOrder
WHERE DATEADD(d,DATEDIFF(d,0,RO.OriginalTargetDate),0) = '2021-06-16'

This gets me all records that need to be updated and the first one will get the time of 08:30:00
Sample data:

RecordID RepairOrderID OriginalTargetDate
58393 64684 2021-06-16 00:00:00
58399 64719 2021-06-16 00:00:00
58433 64711 2021-06-16 00:00:00
58439 64716 2021-06-16 00:00:00
58454 64722 2021-06-16 00:00:00
58460 64728 2021-06-16 00:00:00