Splitting rows into multiple rows based on dates

[Bisquite]

I need to create a new table based on bookings placed in our system.
Currently the booking information contains data like:-

BookingID StartDate EndDate Amount
1 01/01/2005 31/03/2005 £100
2 15/02/2005 16/03/2005 £50

I need to split these lines up based on the start and end dates. This will be used to charge the client on a monthly basis as follows:-

BookingID MonthStart MonthEnd Amount
1 01/01/2005 31/01/2005 £33.33
1 01/02/2005 28/02/2005 £33.33
1 01/03/2005 31/03/2005 £33.33
2 15/02/2005 28/02/2005 £25.00
2 01/03/2005 16/03/2005 £25.00

I know this is probably easier in VB.net or similar, but it would be nice to do this in a stored procedure.

Has anyone ever done anything like this before in Transact-SQL?

Thanks

 

[vongrunt]

Do you have some kind of calendar table handy?

If not, things are a bit more complex... but not too much.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Bisquite]

We have tables that store all the months and all the days. Is that what you mean?

 

[vongrunt]

OK.

Can you post expected results for more complex cases like:

BookingID StartDate EndDate Amount
3 29/01/2005 01/02/2005 100
4 18/02/2005 12/05/2005 100

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

I guess number of days elapsed in month is not relevant. In that case...

Here is sample data.

create table myTable 
(	bookingID int,
	startDate smalldatetime,
	EndDate smalldatetime,
	Amount smallmoney
)

set dateformat dmy
insert into myTable values (1, '01/01/2005', '31/03/2005', 100)
insert into myTable values (2, '15/02/2005', '16/03/2005', 50)

create table Calendar (calDate smalldatetime)

declare @d smalldatetime; set @d = '20000101'
while year(@d) < 2007
begin
	insert into Calendar (calDate) values (@d)
	set @d = @d + 1
end

And query that splits bookings based on dates:

select T.bookingID,
case when T.startDate > C.BoM Then T.startDate Else C.BoM End as startDate,
case when T.endDate < C.EoM Then T.endDate Else C.EoM End as endDate,
T.Amount/(1+datediff(mm, T.startDate, T.EndDate))
from myTable T
inner join
(	select min(calDate) as BoM, max(calDate) as EoM
	from Calendar
	group by Year(calDate), Month(calDate) 
) C
on T.StartDate <= C.EoM and T.EndDate >= C.BoM
order by T.bookingID, C.BoM

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Bisquite]

I think the logic behind the amount calculations will have to be based on a standard 30 day month, but I am yet to decide this.
So I think for simplicity it's probably best to ignore the amounts for now and concentrate on just splitting the dates.

So for your examples we would get the following:-

BookingID StartDate EndDate
3 29/01/2005 31/01/2005
3 01/02/2005 01/02/2005
4 18/02/2005 28/02/2005
4 01/03/2005 31/03/2005
4 01/04/2005 30/04/2005
4 01/05/2005 12/05/2005

Appreciate the help, thanks.

 

[vongrunt]

Then we guess the same... check my previous post

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Bisquite]

You replied whilst I was typing...
Thanks for the code, I'll take a look at it tomorrow.