Thank you Luis, the articles were new to me and I'm always looking for something to learn.

Also thank you Sean, this example was exactly what I was looking for....

Thank you for the suggestion. However as i am new to SQL i think it would take to long to learn to use ssis and remake the whole proc.

Maybe on...

Thank you, I like the synonym one as its more like what i wanted.

Just to be sure, the performance and space for the synonym shouldn't be anything noteworthy right?

Not sure how it works (ill have to study Over and Partition By) but all i had to do was add a extra case statement for the dateEnd Time being...

After a week of trying different things i finally got a idea that works.

creating a stored proc that would inserts new dates for each date that spans multiple days

declare...

Not necessarily in the sample data i added times when it was from 8am to 9am or 2pm...

Kingston Dhasian (6/20/2013)

Great job posting the sample data and the structure of the table:-)

Can you also provide the expected results based on your sample data

That will help us to test...

Sorry, here is the data.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP...