CTE to INSERT seperate line for each day between two dates, with the same info, but only that day in the arrival/departure columns

Question

HI,

I am trying to collate data from multiple table into on temporary table (which i have done) , but I am not being asked for a separate line for each date in between the arrival and departure dates.

My Temporary table insert looks like this

	INSERT @results
	SELECT a.reservation_id,arrival,date_created,arrival,departure,adults,children,j.type_code,a.room_rate,d.phase,b.guest_title,b.guest_initial,b.guest_name,c.country_name,e.source,a.group_id,k.group_name,i.source_name,n.group_name,f.rate_name,nights,g.hotelcurrency_name,h.segment_name,Sum(o.charge),0,0
	FROM reservations a , @findGuest b , @countryCodes c, reservation_status d, @business_sources e, @rate_type f, @hotelCurrency_ID g, @segment h, @source i, @roomInfo j, @Group_details k, @vatpercent L, @subsegment n , @accommodation_charge o
	WHERE a.guest_id = b.guest_id
	and b.country_id = c.country_id
	and d.status_id = a.status_id
	and a.business_source_id = e.source_id
	and a.rate_id = f.rate_id
	and a.rate_id = h.rate_id
	and a.business_source_id = i.source_id
	and a.type_id = j.type_id
	and a.group_id = k.group_id
	and a.reservation_id = o.reservation_id
	and COALESCE(a.subsegment_id,0)=n.group_id
	GROUP BY a.reservation_id, arrival,date_created,arrival,departure,adults,children,j.type_code,a.room_rate,d.phase,guest_title,guest_initial,guest_name,c.country_name,e.source,a.group_id,k.group_name,i.source_name,n.group_name,f.rate_name, nights, g.hotelcurrency_name, h.segment_name,o.charge

and the output looks like 



etc..

now, both these reservations needs a line 

a) the On_date to be a each night of the booking is for ( for a line for 18th, 19th, and 20th (they check out on the last day)) . Same details for each, except  the ON_date must be each day in the range between Ci_date and CO_date.

I think I need a CTE. Ive done one that SELECTs * FROM my temporary table (kinda pointless, but at least I now I can get the CTE working, kinda)

; WITH cte
AS
(SELECT * from @results )
INSERT into @cteresults 
(reservation_id,
 on_date,
 booking_date,
 ci_date,
 co_date,
 persons_adult,
 persons_child,
 room_type,
 total_amount,
 booking_status,
 guest_title,
 guest_initial,
 guest_name,
 nationatalty,
 booking_channel,
 group_id,
 group_discription,
 res_source,
 market_segment,
 rate_code,
 los,
 currency,
 ms_group,
 room_rev_xvat,
 vat_amount,
 service_charge
)

SELECT * FROM cte

SELECT * FROM @cteresults 

But, the help I need now is, how can I get the the CTE to loop round the rows , making a line of each of the days inbetween ci_date and CO_date ? 

I have a column called los (Length Of Stay) , 3 in the above cases, so I thought I could use this to create a loop, but, I'm note sure.

Could someone show me how please ? 

Thanks in advance 

Answer 1

Hi, Note for Future self (and anyone else looking for an answer), I found it here (https://stackoverflow.com/questions/16358959/sql-how-to-convert-row-with-date-range-to-many-rows-with-each-date)  and I replaced the CTE code with this

INSERT @cteresults
select reservation_id,dateadd(d, v.number, d.ci_date) Stay_date,booking_date,ci_date,co_date,persons_adult,persons_child,room_type,total_amount,booking_status,guest_title,guest_initial,guest_name,nationatalty,booking_channel,group_id,group_discription,res_source,market_segment,rate_code,los,currency,ms_group,room_rev_xvat,vat_amount,service_charge 
from @results d
join master..spt_values v on v.type='p' and v.number between 0 and datediff(d,ci_date, co_date)-1