Wow, Lynn, you are a machine! That is awesome!

I wish there was something I could do for you to thank you for all your time on this.

This is just so...

I got an idea on the breaks.

I think we'll add a "type" column to the Schedules_RC table indicating what type of schedule it is, ie ScheduledTime, Break, Lunch, OutOfService etc....

Beyond 2345, yes. I was going to modify the pivot to list the 2400 - 2859 times instead of the 0100, 0200, etc.

o crap, yes. I forgot they modified the routes so now they're varchars(15). I am so sorry for the sloppy work.

The table would then be:

CREATE TABLE #Schedules_RC(

Route VARCHAR(15),

Start_Time VARCHAR(10),

End_Time VARCHAR(10)

)

That would be exactly right:

INSERT INTO #Schedules_RC

(

Route

, Start_Time

, End_Time

...

Sorry, yes. 2400 - 2859.

Not 2500 - 2859.

Here's another trick.

So now that the hours are defined in 15 increments, if I wanted to specify "break" periods where the route is not in service during their start/end time,...

Times could span days but you'd know that if it has a starttime in regular time format and an endtime in 29 hour clock format (ie 2500 - 2859).

This indeed...

Ok, here's the sample data with the varchars:

CREATE TABLE #Schedules_RC

(

Route INT

...

No need to apologize! I was a bit lax in providing that sample code only because I thought it was so easy to describe (me being lazy). There were...

I'm confused.

I followed the instructions listed in the Best Practices link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

And posted the code yesterday at 834pm. Aside from the Set Identity statements (which do err out) it worked...

I did provide sample data yesterday, though not much. Capn's data was essentially the same anyway. And the sample data actually was in the smalldatetime format, so this...

I agree, however, I'm now up against a constraint where the start/end time data that i'll be receiving is in the varchar format which I can't do anything about.

I don't...

This is great stuff. Thanks everyone for your contributions.

I have another question now. Since we've been using smalldatetime as the startime and endtime format, (but I...

I got it. (Didn't think about adding the original Tally cte into the existing one.)

One question on how it's calculating: If i have a route starting at 0900 and ending...