SQL Server 2005 Little by little, what day?

Question

I have a table called Jobs that tracks jobs and their next run time. One of the planning options allows you to complete the task several times a week. I use bitwise comparisons to tell which day is next (well ... I'm trying anyway.) So for example. I have a table like this ..

JobID NextRunTime DaysOfWeek 1 12-26-2011 21 

My bitwise enumeration looks like this.

 Monday = 1 Tuesday = 2 Wednesday = 4 Thursday = 8 Friday = 16 Saturday = 32 Sunday = 64. 

So, we know that this work should work on Monday, Wednesday, Friday. (12-26-2011) - Monday, so when it is updated, it should start again on 12-28-2011, but I can’t come up with an algorithm that allows me to programmatically install a new NextRunTime.

This is the method I'm trying to get with some pseudo code for what I'm having problems with.

 IF OBJECT_ID('tempdb..#DaysSchedule') IS NOT NULL DROP TABLE #DaysSchedule CREATE TABLE #DaysSchedule ( Monday int, Tuesday Int, Wednesday Int, Thursday INT, Friday INT, Saturday INT, Sunday INT ) INSERT INTO #DaysSchedule (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday) Values(21 & 1,21 & 2,21 & 4,21 & 8,21 & 16 ,21 & 32,21 & 64) 

This gives us a table that looks like this:

 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 1 0 4 0 16 0 0 

From here (halfway) the pseudo-code is easy.

 for (int i=1; i<7, i++) { thisDay = DATENAME(dw, DATEADD(day, i, nextRunTime)) -- we add one day if (column named thisDay contains a value > 0) -- if that days value > 0 begin We add the difference of thisDay to NextRunTime to NextRunTime and we're done. end } 

Answer 1

NOTE. I will not comment on the idea of ​​representing multiple data elements in one field. This may or may not be appropriate in this case, I am simply commenting on how to make this idea an idea.

The problem that you are facing is that the information is practically inconsistent with its use.

Currently ... - Retrieving a Day from NextRunTime
- Identify the BIT representing this day

- SEARCH for the next bit set in the unit, if necessary, on the bike before the start - Determine the distance traveled in this search
- Add this distance to NextRunTime

It is simply inefficient or simple.

I would recommend recording the number of days to add instead to reach the next scheduled date.

Examples:

 -----15 = Saturday and Sunday Only 1111111 = Every Day 11113-- = Every Weekday 2-2-3-- = Monday, Wednesday, Friday 

This changes the algorithm to ... - Extracting a Day from NextRunTime
- identify the character in this position
- Pass it int | - Add many days to NextRunTime

This avoids searching and counting, replacing it with direct search.

This allows the use of dead ends or more complex plans. This may be an advantage or a disadvantage depending on your situation ...

 1111100 = Every weekday for a week, then stop 2222222 = Every other day, on a two week cycle 

Answer 2

Would it be so bad to use three lines to simulate three days? eg

 INSERT INTO Jobs (JobID, NextRunTime, RepeatOption) VALUES (1, '2011-12-26', 'Y'); INSERT INTO RepeatJobs (JobID, RepeatOption, DaysOffset) VALUES (1, 'Y', 2), (1, 'Y', 4); 

---

If you need to be beaten, how about creating a lookup table, for example.

 VALUES (1, 'Monday'), (2, 'Tuesday'), (3, 'Monday'), (3, 'Tuesday'), (4, 'Wednesday'), (5, 'Monday'), (5, 'Wednesday'), (6, 'Tuesday'), (6, 'Wednesday'), (7, 'Monday'), (7, 'Tuesday'), (7, 'Wednesday'), (8, 'Thursday'), (9, 'Monday'), (9, 'Thursday'), (10, 'Tuesday'), (10, 'Thursday'), (11, ... 

... but instead of Monday, Tuesday, Wednesday, etc. store the offset on days from the set day of the week, say Sunday, then round your NextRunTime until Sunday, and then add the offset, etc.