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 @Maxdays int; --counter

select @Maxdays = Max(dateDiff(dd,dateStart,dateEnd)) from #mytable --find max of dates that span multiple days

while @Maxdays > 0 --while loop

begin

insert into #mytable(dateStart, dateEnd) -- inserts a new row that is plus one day of the start date if it spans multiple days

select Dateadd(day, 1,datestart), dateend

from #mytable

where Datediff (dd,Convert(date,DateStart), Convert(date,DateEnd)) =@Maxdays

update #mytable -- changes the date end so that its not found again in the insert part

set dateend=dateadd(d,-@Maxdays,dateend)

where Datediff (dd,Convert(date,DateStart), Convert(date,DateEnd)) = @Maxdays;

set @maxdays = @maxdays - 1 -- minus count

end

How ever i have never made a stored proc, if any one could help me create one that would be great.

Also if anyone has a different way, i would love to hear it. The less temp tables and stored procs the better