user3115933 user3115933 - 8 months ago 44
SQL Question

How to pass on this logic about dates in my T-SQL Query?

I am using SQL Server 2014. I have a table (around 1 million records) in my database called 'ReservationStayDate'. An extract is shown below:

ResaID StayDate BookingDate
253 2016-02-10 2016-01-15
253 2016-02-11 2016-01-15
253 2016-02-12 2016-01-15
321 2016-05-03 2016-02-21
321 2016-05-04 2016-02-21

...and the list goes on.
I need to extract data from the ReservationStayDate table based on the criteria below (which is a table in an Excel file):

PromoName BookingDateStart BookingDateEnd StayDateStart StayDateEnd
Promo1 2016-01-10 2016-01-30 2016-02-08 2016-02-15
Promo2 2016-03-04 2016-04-30 2016-06-01 2016-06-14
Promo3 2016-03-06 2016-04-20 2016-06-20 2016-06-27

...and the list goes on with around 100 PromoNames and their respective date criterias. The logic here is that all records in the ReservationStayDate table WHERE BookingDate is between ' 2016-01-10' and '2016-01-30' AND StayDate is between '2016-02-08' and '2016-02-15' will be tagged as 'Promo1'.

I have exported this Excel file in its original format into a table (called PromoName) in my SQL Server database.

The output I am looking for is as follows:

ResaID MinStayDate MaxStayDate BookingDate PromoName
253 2016-02-10 2016-02-12 2016-01-15 Promo1
321 2016-05-03 2016-05-04 2016-02-21 NULL

...and so on.
If a ResaID does not fall in the criterias mentioned for each PromoName in the PrmoName Table, then the output for that ResaID will show a NULL under the PromoName Column of the T-SQL output (as shown for ResaID 321 above).
It gets really tedious to modify my T-SQL query each time to extract data for each of the PromoNames mentioned in the PromoName Table.

I would like to be able to JOIN my PromoName table with the ReservationStayDate table but I am stuck as to how do the join and also as to how to write the T-SQL logic that will meet my desired output.

Assumption: there are no overlapping dates between PromoNames and StayDates.

Any ideas on how to achieve this?

Answer Source

I think you can achieve this by using LEFT JOIN and GROUP BY:

create table #ReservationStayDate(ResaID int,  StayDate date,     BookingDate date)

insert into #ReservationStayDate values
 (253,     '2016-02-10',    '2016-01-15'),
 (253,     '2016-02-11',    '2016-01-15'),
 (253,     '2016-02-12',    '2016-01-15'),
 (321,     '2016-05-03',    '2016-02-21'),
 (321,     '2016-05-04',    '2016-02-21')

create table #PromoName(
    PromoName varchar(50),   
    BookingDateStart date,  
    BookingDateEnd date,  
    StayDateStart date,   
    StayDateEnd date)

insert into #PromoName values

select a.ResaID, 
    min(a.StayDate) MinStayDate, 
    max(a.StayDate) MaxStayDate, 
    a.BookingDate BookingDate, 
from #ReservationStayDate a
left join #PromoName b on a.BookingDate 
                                  between b.BookingDateStart and b.BookingDateEnd
and a.StayDate between b.StayDateStart and b.StayDateEnd
group by a.ResaID, a.BookingDate, b.PromoName