How to fill column basing on two other columns

Question

I have table LessonHour with empty Number column.

TABLE [dbo].[LessonHour]
(
    [Id] [uniqueidentifier] NOT NULL,
    [StartTime] [time](7) NOT NULL,
    [EndTime] [time](7) NOT NULL,
    [SchoolId] [uniqueidentifier] NOT NULL,
    [Number] [int] NULL
)

How can I fill up the table with Number for each LessonHour so it would be the number of lesson hour in order? The LessonHours cannot cross each other. Every school has defined its own lesson hour schema.

Example set of data http://pastebin.com/efWCtUbv

What'd I do:

1. Order by SchoolId and StartTime
2. Use Cursor to insert into row next number, starting from 1 every time the SchoolId changes.

---

Edit:

Solution with cursor

select -- top 20
        LH.[Id],
        [StartTime],
        [EndTime],
        [SchoolId]
into #LH
from
    LessonHour as LH
    join RowStatus as RS on LH.RowStatusId = RS.Id
where 
    RS.IsActive = 1

select * from #LH order by SchoolId, StartTime

declare @id uniqueidentifier, @st time(7), @et time(7), @sid uniqueidentifier
declare @prev_sid uniqueidentifier = NEWID()
declare @i int = 1
declare cur scroll cursor for
select * from #LH order by SchoolId, StartTime
open cur;
fetch next from cur into @id, @st, @et, @sid
while @@FETCH_STATUS = 0
begin
    --print @prev_sid
    if @sid <> @prev_sid
    begin
        set @i = 1
    end

    update LessonHour set Number = @i where Id = @id

    print @i
    set @i = @i + 1

    set @prev_sid = @sid
    fetch next from cur into @id, @st, @et, @sid
end;
close cur;
deallocate cur;

drop table #LH

This is the result I was after http://pastebin.com/iZ8cnA6w

Answer 1

Merging the information from the StackOverflow questions SQL Update with row_number() and How do I use ROW_NUMBER()?:

with cte as (
    select number, ROW_NUMBER() OVER(partition by schoolid order by starttime asc) as r from lessonhour
)
update cte
set number = r

Answer 2

Would this work

CREATE TABLE [dbo].[LessonHour]
(
    [Id] [uniqueidentifier] NOT NULL,
    [StartTime] [time](7) NOT NULL,
    [EndTime] [time](7) NOT NULL,
    [SchoolId] [uniqueidentifier] NOT NULL,
    [Number] AS DATEDIFF(hour,[StartTime],[EndTime])
)

So if I understand the question correctly you require a calculated column which takes in the values of [StartTime] and [EndTime] and returns the number of hours for that lesson as an int. The above table definition should do the trick.