Sql query for finding birthdays & anniversaries

Question

0.00/5 (No votes)

See more:

I have a CRON task that needs to extract customers with birthdays in a given date range, from a MySQL DB table. The birthday field is indexed and of type DATE.

I want a query which should trigger on every Monday and extract all the customers who have birthday starting from next monday +10 days thereon.

I'm using below query right now but here I have to manually change the month and date range. Need to automate it

Please assist..

What I have tried:

DECLARE @Month INT=01
DECLARE @StarDate INT=01
DECLARE @EndDate INT=08

SELECT distinct Outlets.OutletName, Outlets.Locality, Outlets.City, FORMAT(Customers.DateOfBirth, 'dd/MMM')as DOB,DateOfBirth,
 Customers.CustomerName, Customers.Cell_Number, Customers.EmailId, Customers.Gender, Customers.AgeGroup 
FROM Customers WITH (NOLOCK) INNER JOIN CustomerActivities ON Customers.CustomerId=CustomerActivities.CustomerId 
INNER JOIN Outlets ON CustomerActivities.EntityId=Outlets.OutletId 
WHERE Customers.AccountId='wv5wdvf5v46sf2b16f5b16f2b' 
AND  MONTH (DateOfBirth)=@Month
AND  DAY (DateOfBirth) >=@StarDate AND DAY (DateOfBirth) <= @EndDate
Order By DateOfBirth ASC

Posted 26-Dec-19 19:56pm

Member 14700794

Updated 26-Dec-19 23:24pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-12-26T20:19:00

Solution 1

That won't work well, even manually updated. If - for example - it's the 270th December 2019, your query will miss my wife whose birthday is January 1st because it's in a different month.

Instead of checking the month and day, build two date objects from the DOB and GETDATE using YEAR, MONTH and DAY; and use DATEDIFF to check for "within 10 days".

Posted 26-Dec-19 20:19pm

OriginalGriff

Comments

Member 14700794 27-Dec-19 2:33am

That's what, I have to change the month manually here to fetch data for different months. Have tried the below query as well but it is only giving out data where customer has mentioned the birth year as 2019.

```SELECT Outlets.OutletName, Outlets.Locality, Outlets.City, Customers.DateOfBirth, Customers.CustomerId, Customers.CustomerName, Customers.Cell_Number, Customers.EmailId, Customers.Gender, Customers.AgeGroup
FROM Customers WITH (NOLOCK) INNER JOIN CustomerActivities ON Customers.CustomerId=CustomerActivities.CustomerId
INNER JOIN Outlets ON CustomerActivities.EntityId=Outlets.OutletId
WHERE Customers.AccountId='wv5wdvf5v46sf2b16f5b16f2b'
AND DateOfBirth>(GETDATE() + 6) AND DateOfBirth<(GETDATE() +15)```

OriginalGriff 27-Dec-19 3:54am

So you just ignore what I say then?

Here's a simple starter for you:
DECLARE @TODAY INT = datepart(dayofyear, GETDATE())
SELECT DOB
FROM Student
WHERE (CASE WHEN datepart(dayofyear, DOB) < @TODAY THEN datepart(dayofyear, DOB) + 365 ELSE datepart(dayofyear, DOB) END)
BETWEEN @TODAY and @TODAY + 9

Wendelius · Answer 2 · 2019-12-26T23:25:00

One way is to use CTE to look for next monday and then use the result in your where clause. Consider the following example

SQL

WITH NextDays (ActualDate, DayOfWeek) AS (
   -- recursive query for next dates
   SELECT GETDATE(),
          DATEPART(WEEKDAY, GETDATE())
   UNION ALL
   SELECT DATEADD(day, 1, ActualDate),
          DATEPART(WEEKDAY,  DATEADD(day, 1, ActualDate))
   FROM NextDays nd
   WHERE nd.ActualDate < GETDATE() + 8 --limit the query to relevant dates
),
NextMonday (DateForMonday) AS (
   -- query to fetch next Monday
   SELECT TOP 1
          CAST(nd.ActualDate AS date)
   FROM NextDays nd
   WHERE nd.DayOfWeek = 2 -- limit to Mondays only
   ORDER BY nd.ActualDate
)
-- Look for coming birthdays from customer records
SELECT *
FROM Customers c,
     NextMonday nm -- cartesian join used since 
                   -- only one record is returned from NextMonday 
WHERE c.DateOfBirth BETWEEN nm.DateForMonday AND DATEADD(day, 10, nm.DateForMonday)

Note:
- you need to set datefirst propertly, see SET DATEFIRST (Transact-SQL) - SQL Server | Microsoft Docs[^]
- also you need to change the query to start from tomorrow if this day will not be included, in case current day would be Monday.