I have a website that I'm trying to migrate from classic asp to asp.net. It had a lead schedule, where each sales agent would be featured for the current day, or part of the day.The next day a new agent would be scheduled. It was driven off a database table that had a row for each day in it. So to figure out if a sales agent would show on a day, it was easy, just find today's date in the table. Problem was it ran out rows, and you had to run a script to update the lead days 6 months at a time. Plus if there was ever any change to the schedule, you had to delete all the rows and re-run the script.

So I'm trying to code it where sql server figures that out for me, and no script has to be ran. I have a table like so

CREATE TABLE [dbo].[LeadSchedule](
[leadid] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[sunday] [bit] NOT NULL,
[monday] [bit] NOT NULL,
[tuesday] [bit] NOT NULL,
[wednesday] [bit] NOT NULL,
[thursday] [bit] NOT NULL,
[friday] [bit] NOT NULL,
[saturday] [bit] NOT NULL,
[StartDate] [smalldatetime] NULL,
[EndDate] [smalldatetime] NULL,
[StartTime] [time](0) NULL,
[EndTime] [time](0) NULL,
[order] [int] NULL,

So the user can schedule a sales agent depending on their work schedule. Also if they wanted to they could split certain days, or sales agents by time, So from Midnight to 4 it was one agent, from 4-midnight it was another. So far I've tried using a numbers table, row numbers, goofy date math, and I'm at a loss. Any suggestions on how to handle this purely from sql code? If it helps, the table should always be small, like less than 20 never over 100.

update After a few hours all I've managed to come up with is the below. It doesn't handle filling in days not available or times, just rotates through all the sales agents

with leadTable as ( 
select  leadid,userid,[order],StartDate,
case DATEPART(dw,getdate())
    when 1 then sunday
    when 2 then monday
    when 3 then tuesday
    when 4 then wednesday
    when 5 then thursday
    when 6 then friday
    when 7 then saturday
end as DayAvailable ,
ROW_NUMBER() OVER (ORDER BY [order] ASC) AS ROWID   
from LeadSchedule
where  GETDATE()>=StartDate
and (CONVERT(time(0),GETDATE())>= StartTime or StartTime is null)
and (CONVERT(time(0),GETDATE())<= EndTime or EndTime is null)
)
select userid, DATEADD(d,(number+ROWID-2)*totalUsers,startdate ) leadday from (select *, (select COUNT(1) from leadTable) totalUsers from leadTable
inner join Numbers on 1=1 
where DayAvailable =1  

    ) tb1 order by leadday asc