SQL 选择特定日期范围内的生日

Question

我有以下几点:

DECLARE @TempTable TABLE 
(
 [Id] [int],
 [FirstName] [varchar](40) NULL,
 [Birthdate] [date] NULL
);

insert into @TempTable values (1, 'A', convert(date, '05/25/2017', 101))
insert into @TempTable values (2, 'B', convert(date, '06/25/2017', 101))

我需要的是查询，它将返回从 StartDate 到 EndDate 范围内的所有生日。

在这里更多的是我期望得到的结果:

案例 1:
如果日期范围设置为:

DECLARE @StartDate datetime = '05/01/2017'; 
DECLARE @EndDate datetime = '07/01/2017';

查询应返回:

1 A 2017-05-25
2 B 2017-06-25

案例2:
如果日期范围设置为:

DECLARE @StartDate datetime = '05/01/2017'; 
DECLARE @EndDate datetime = '06/01/2017';

查询应返回:

1 A 2017-05-25

案例3:
如果日期范围设置为:

DECLARE @StartDate datetime = '05/01/2015'; 
DECLARE @EndDate datetime = '07/01/2017';

查询应返回:

1 A 2017-05-25
1 A 2016-05-25
1 A 2015-05-25
2 B 2017-06-25
2 B 2016-06-25
2 B 2015-06-25

案例 4:
如果日期范围设置为:

DECLARE @StartDate datetime = '05/01/2015'; 
DECLARE @EndDate datetime = '06/01/2017';

查询应该返回

1 A 2017-05-25
1 A 2016-05-25
1 A 2015-05-25
2 B 2016-06-25
2 B 2015-06-25

Answer 1

首先，我们为给定范围创建所有日期。然后应用逻辑

        DECLARE @StartDate datetime = '05/01/2015'; 
        DECLARE @EndDate datetime = '06/01/2017';

        ;With DateSequence  as
        (
            Select @StartDate as Bdate
            union all
            Select dateadd(day, 1, Bdate)
            from DateSequence
           where Bdate < @EndDate
        )
        Select ID,FirstName,Bdate as BirthDate from DateSequence 
        cross join @TempTable
        where Bdate between @StartDate and @EndDate and
        month(Bdate)= month(BirthDate) and day(Bdate) = day(BirthDate)
        order by ID asc , Bdate desc
        option (MaxRecursion 2000)

OutPut :