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

  • 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 


    • Edited by G-Oker Wednesday, March 25, 2020 4:43 PM
    Wednesday, March 25, 2020 4:42 PM

All replies

  • 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

    Friday, March 27, 2020 9:10 AM