CODEWITHSUNDEEP
sqlt-sqlselectwhile-loopdatabase-cursor
Klik Kliković
Klik Kliković

Reputation: 384

SQL Select birthdays in specific date range

I have following:

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

What I need is query which will return all Birthdays in range from StartDate to to EndDate.

To be more here is what I expect to get as a result:

Case 1: If date range is set to:

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

Query should return:

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

Case 2: If date range is set to:

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

Query should return:

1 A 2017-05-25

Case 3: If date range is set to:

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

Query should return:

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

Case 4: If date range is set to:

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

Query should return

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

Upvotes: 4

Views: 2107

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Firstly we creates all dates for given range . then apply logic

        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 :

enter image description here

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

At its most basic level, you can do this by creating a list of everyone's birthday for 100 years, then filtering...

with numbers as
(
select 0 as NN
union all
select NN+1
from numbers
where NN < 100
)    
select id, dateadd(yy,NN,BirthDate) as Birthdays
from numbers
cross join @TempTable
where dateadd(yy,NN,BirthDate) between @StartDate and @EndDate

Upvotes: 1

Related Questions