如何在T-SQL查询中传递日期的逻辑?

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

我正在使用SQL Server 2014。我的数据库中有一个名为“ReservationStayDate”的表(大约有100万条记录)。摘录如下:

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):

…这个名单还在继续。我需要根据下面的标准从ReservationStayDate表中提取数据(这是Excel文件中的一个表):

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'.

…名单上还有大约100个PromoNames和他们各自的日期标准。这里的逻辑是，在预订日期表中，预订日期在“2016-01-10”和“2016-01-30”之间，而预订日期在“2016-02-08”和“2016-02-15”之间的所有记录将被标记为“Promo1”。

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

我将这个Excel文件以其原始格式导出到SQL Server数据库中的一个表(称为PromoName)中。

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.

…等等。如果ResaID不属于PrmoName表中提到的每个PromoName，那么该ResaID的输出将在T-SQL输出的PromoName列下显示一个NULL(如上面ResaID 321所示)。每次修改我的T-SQL查询来为PromoName表中提到的每个PromoNames提取数据，都是非常繁琐的。

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.

我希望能够将我的PromoName表与预订日期表连接在一起，但是我仍然被卡在如何连接和如何编写能够满足我期望的输出的T-SQL逻辑上。

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

假设:PromoNames和StayDates之间没有重叠的日期。

Any ideas on how to achieve this?

关于如何实现这一点，有什么想法吗?

2 个解决方案

#1

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

我认为你可以通过使用LEFT JOIN和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
('Promo1','2016-01-10','2016-01-30','2016-02-08','2016-02-15')

select a.ResaID, 
    min(a.StayDate) MinStayDate, 
    max(a.StayDate) MaxStayDate, 
    a.BookingDate BookingDate, 
    b.PromoName
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

#2

SELECT ResaID,MIN(StayDate) MINStayDate,MAX(StayDate)   
MAXStayDate,BookingDate,A.PromoName
FROM ReservationStayDate
LEFT OUTER JOIN
(
  SELECT PromoName,BookingDateStart,BookingDateEnd
  FROM PromoName
)A ON BookingDate BETWEEN BookingDateStart AND BookingDateEnd
GROUP BY ResaID,BookingDate,PromoName