# Update sequence of time where hour, min, AM/PM in separate columns?

I'm dealing with a 3rd party database to update a series of schedules. To make things simple, my table looks like this.

## |Hour | Min | | 7 | 0 | | 7 | 0 | | 7 | 0 | | 7 | 0 |

There are over 100 rows, I need to update the entire table so that each MIN is incremented by 10 and once that incrementation reaches 50, the HOUR row is incremented by 1.

Example

|Hour | Min |
| 7 | 10 |
| 7 | 20 |
| 7 | 30 |
| 7 | 40 |
| 7 | 50 |
| 8 | 0 |
| 8 | 10 |
| 8 | 20 |
| 8 | 30 |
| 8 | 40 |
| 8 | 50 |
| 9 | 0 |

There is of course and ID column and I have a where portion of the update clause that will be the same. I tried it by this, but this didn't work, I can't seem to break out once it hits 59.

``````declare @min int
declare @hour int
set @min=0
set @hour=6

if @min = 59
set @hour = @hour + 1

update dbo.Jobs set @min = starttimeminute = @min + 1
update dbo.Jobs set starttimehour = @hour, @min = 0``````

I'm a bit confused, since you said "increment by 10" but then added "1".

But something like this:

``````UPDATE dbo.Jobs
SET min = CASE WHEN min >= 50 THEN (min + 10) - 60 ELSE min + 10 END,
hour = CASE WHEN min >= 50
THEN CASE WHEN hour = 23 THEN 0 ELSE hour + 1 END
ELSE hour END``````

Thanks, I just tried that query and it sets every single value of my min= 10, I ran it again and it sets it to 20.

Yes it's an increment by 10. Also it's a 12 hour format, but once the min goes to 50 the hour should increment by 1, while the min goes to 0.

Since you've provided no directly useable test data at all, I can't help you further.

Well this here is the create table statement.

``````USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

GO

CREATE TABLE [dbo].[ScheduleJobs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[starttimehour] [int] NULL,
[starttimeminute] [int] NULL,
[startTimeAmPm] [varchar](50) NULL,
CONSTRAINT [PK_ScheduleJobs] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

GO
``````

The data is pretty simple. Fill the starttimehour with 7, fill the startimemintue with 0 and fill the AM/PM portion with AM.

Something like this perhaps:

``````declare @add_min int=10;

update s
set s.starttimehour=s.starttimehour+floor((s.starttimeminute+t.mins)/60)
,s.starttimeminute=(s.starttimeminute+t.mins)%60
,s.starttimeampm=convert(varchar(7)
,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
,(s.starttimeminute+t.mins)%60
,cast('00:00' as time)
)
)
,0
)
from dbo.schedulejobs as s
inner join (select ss.id
,(row_number() over(order by ss.id)-1)*@add_min as mins
from dbo.schedulejobs as ss
) as t
on t.id=s.id
;
``````

Or if you want to be able to subtract aswell:

``````declare @add_min int=-7;

update s
set s.starttimehour=datepart(hour
,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
,(s.starttimeminute+t.mins)%60
,cast('00:00' as time)
)
)
)
,s.starttimeminute=datepart(minute
,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
,(s.starttimeminute+t.mins)%60
,cast('00:00' as time)
)
)
)
,s.starttimeampm=convert(varchar(7)
,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
,(s.starttimeminute+t.mins)%60
,cast('00:00' as time)
)
)
,0
)
from dbo.schedulejobs as s
inner join (select ss.id
,(row_number() over(order by ss.id)-1)*@add_min as mins
from dbo.schedulejobs as ss
) as t
on t.id=s.id
;
``````

Hi I have got the below .. Please see if it works for you

``````SET statistics time, io ON;

go

-- dropping temp tables
IF Object_id('tempdb.dbo.#ScheduleJobs', 'U') IS NOT NULL
DROP TABLE #schedulejobs

IF Object_id('tempdb.dbo.#tally1', 'U') IS NOT NULL
DROP TABLE #tally1

go

-- creating tables
CREATE TABLE #schedulejobs
(
id                INT IDENTITY (1, 1),
[starttimehour]   [INT] NULL,
[starttimeminute] [INT] NULL,
CONSTRAINT [PK_ScheduleJobs] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]

CREATE TABLE #tally1
(
rownum1 INT NULL,
min1    INT NULL
)

go

-- Populate tables with data
DECLARE @ID INT = 1

WHILE @ID < 1001
BEGIN
INSERT INTO #schedulejobs
(starttimehour,
starttimeminute)
VALUES      (7,
0)

SET @ID = @ID + 1
END

go

INSERT INTO #tally1
VALUES      (1,
0),
(2,
10),
(3,
30),
(4,
40),
(5,
50),
(6,
59)

go

-- Final Select to give Result
SELECT a.starttimehour,
a.starttimeminute,
( a.starttimehour + a.batchno ) % 24,
b.min1,
CASE
WHEN ( ( a.starttimehour + a.batchno ) % 24 ) BETWEEN 0 AND 12 THEN
'AM'
ELSE 'PM'
END AS AMPM
FROM   (SELECT id,
1 + ( ( Row_number()
OVER (
ORDER BY id) - 1 ) % 7 )                        AS
rownum,
Floor(Cast(Row_number()
OVER (
ORDER BY (SELECT 1)) AS DECIMAL(10, 2)) / 7) AS
BatchNo,
Floor(Cast(Row_number()
OVER (
ORDER BY (SELECT 1)) AS DECIMAL(10, 2)) / 7) AS
BatchNo1,
starttimehour,
starttimeminute
FROM   #schedulejobs) a
JOIN #tally1 b
ON a.rownum = b.rownum1

go

SET statistics time, io OFF;
``````

Will this work for you?

``````Declare @ScheduleJobs As Table (
ID int Identity(1, 1) Primary Key
, StartTimeHour int Not Null
, StartTimeMinute int Not Null
, StartTimeAMPM char(2) Not Null
);

Insert Into @ScheduleJobs (StartTimeHour, StartTimeMinute, StartTimeAMPM)
Select StartTimeHour = 7
, StartTimeMinute = 0
, 'AM'
From dbo.Tally t
Where t.N <= 100;

With calc (NewDateTime)
As (
Select dateadd(hour, s.StartTimeHour, dateadd(minute, 10 * (row_number() over(Order By (Select 1)) - 1), 0))
From @ScheduleJobs s
)
Select *
, NewStartTimeHour = datepart(hour, c.NewDateTime)
, NewStartTimeMinute = datepart(minute, c.NewDateTime)
, NewStartTimeAMPM = format(c.NewDateTime, 'tt')
From calc c;
``````