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
