How to select one's name day from first name and birth date?

Question

Hey there. I would really appreciate your help in creating a query that would accomplish the following. The problem is with dynamically getting upcoming what we call "namedays" in europe. Nameday is a tradition in many countries in Europe and Latin America of celebrating on a particular day of the year associated with the one's given name (http://en.wikipedia.org/wiki/Name%5Fday).

What i have is a table of all names and their corresponding date (i store the date in this format "1900-08-22", but we really need month & day)

nameday table:

name   date
----------------
Bob    1900-04-22
Bob    1900-09-04
Frank  1900-01-02
...

The trick is that there might be multiple entries for each name, and someones "nameday" is always first found after ones birthday. So say Bob #1 was born on August 5th, his nameday would fall on September 4th, but if we was born after Sept 4th his nameday would be on April 22nd.

Obviously i have a table with my users

user table:

id   first_name  birth_date
------------------------------------
1    Bob         1975-08-05
2    Frank       1987-01-01
...

So, eveything is based on the birthdate, we need to find when a given person celebrates his/her "nameday" based on the first_name and birthdate. When i need to have is a SQL server query that will be able to get me namedays for the people in my database ;)

If you think it would be easier to split the dates in the "nameday" table into: month & day let me know, we can do that. I need to be able to come up with the most efficient solution for this.

Any help is greatly appreciated,

Wojo

Answer 1

I'd recommend you to keep your dates 1904-based, since 1900 was not a leap year and lacked Feb 29th.

However, here's the query with your data:

WITH    users AS
        (
        SELECT  1 AS id, 'Bob' AS first_name, CAST('1975-08-05' AS DATETIME) AS birth_date
        UNION ALL
        SELECT  2 AS id, 'Frank' AS first_name, CAST('1987-01-01' AS DATETIME) AS birth_date
        ),
        namedays AS
        (
        SELECT  'Bob' AS name, CAST('1900-04-22' AS DATETIME) AS date
        UNION ALL
        SELECT  'Bob' AS name, CAST('1900-09-04' AS DATETIME) AS date
        UNION ALL
        SELECT  'Frank' AS name, CAST('1900-01-02' AS DATETIME) AS date
        )
SELECT  *
FROM    users u
OUTER APPLY
        (
        SELECT  COALESCE(
                (
                SELECT  TOP 1 date
                FROM    namedays nd
                WHERE   nd.name = u.first_name
                        AND nd.date >= DATEADD(year, 1900 - YEAR(u.birth_date), birth_date)
                ORDER BY
                        nd.date
                ),
                (
                SELECT  TOP 1 date
                FROM    namedays nd
                WHERE   nd.name = u.first_name
                ORDER BY
                        nd.date
                )
                ) AS date
        ) dates

See this post in my blog for performance details:

• SQL Server: finding date closest to a birthdate

Answer 2

SELECT user.id, First_name, MIN(nameday.date)
FROM user
    INNER JOIN nameday ON user.id = nameday.userid
WHERE user.birth_date < DateAdd(year, YEAR(u.birth_date) - year(nameday.date), nameday.date)
GROUP BY user.id, first_name

Answer 3

SELECT Min(nameday.[date]), [user].first_name
FROM nameday
INNER JOIN [user] on nameday.name = [user].first_name
WHERE nameday.[date] >= DateAdd(year, -(DatePart(yyyy, birth_date)-1900), birth_date)
GROUP BY [user].first_name

Answer 4

Here you go:

WITH "nameday_long" ("name", "date", "p_month_day") AS (
    SELECT  n."name",
            n."date",
            DATEPART(month, n."date") * 100 + DATEPART(day, n."date")
    FROM    "nameday" n
    UNION ALL
    SELECT  n."name",
            n."date",
            DATEPART(month, n."date") * 100 + DATEPART(day, n."date") + 10000
    FROM    "nameday" n
)

SELECT      u."id",
            u."first_name",
            u."birth_date",
            n."date" AS nameday
FROM        "user" u
LEFT JOIN   "nameday_long" n
        ON  u."first_name" = n."name"
        AND n."p_month_day" = (SELECT MIN(x."p_month_day") FROM "nameday_long" x WHERE x."p_month_day" > DATEPART(month, u."birth_date") * 100 + DATEPART(day, u."birth_date"))

The trick is:

• convert dates to the format that does not have years and it easy to compare. I have chosen integer that would be like MMDD representation of the month-day parts of day.
  • I would even suggest to add a (persistent) computed column to "nameday" table with this formula, but it should not be heavy on computation anyway
• extend those namedays to cover 2 years so that it would be easy to find the first one if the persons' nameday is before its birthday (calendar-wise). In this trick I have added 1000 to the MMDD numeric representation to keep natural sort
• for each person select the first nameday that comes after his/her birthday month-date
• use LEFT JOIN so that people with cool names like "Wojo" would still be shown in the result list even if they do not have a nameday, do they? :)

Answer 5

This seems to be a simple solution:

select  u.id, u.first_name, min(isnull(n.date, n2.date)) as nameday
from    users u
        left join namedays n on u.first_name = n.name and dateadd(year, year(u.birth_date) - 1900, n.date) > u.birth_date
        left join namedays n2 on u.first_name = n2.name and dateadd(year, year(u.birth_date) - 1899, n2.date) > u.birth_date
group by u.id, u.first_name

Explanation: Join the users table with the namedays table (adjusting the namedays.date column to the same year as the birthday). If there is an adjusted date that is after their birthday, this date is used.

If not, the users table is joined with the namedays table again, this time adjusting the date column to the year after the birthday, and use the minimum date that is after their birthday.

This solution returns the correct dates according to your example data, and the situations listed in the problem (Bob's birthday changed to after 9/4 returns Bob's nameday as 4/22).

Answer 6

WITH namedays AS (
 SELECT UPPER(nd.name),
        nd.date,
        MONTH(nd.date) 'month',
        DAY(nd.date) 'day'
   FROM NAMEDAYS nd)
     birthdays AS (
 SELECT u.id,
        UPPER(u.first_name) 'first_name',
        MONTH(u.birth_date) 'month',
        DAY(u.birth_date) 'day'
   FROM USERS u)
SELECT t.id,
       t.first_name,
       MIN(nd.date) 'name_day'
  FROM USERS t
  JOIN birthdays bd ON bd.id = t.id
  JOIN namedays nd ON nd.month >= bd.month AND nd.day >= bd.day AND nd.name = bd.firstname
GROUP BY t.id, t.first_name

Answer 7

DatePart(DayOfYear) is a nice way to compare the dates. And who knew I'd ever learn something culturally new from SO? :-)

declare @nameday table
(
  Name varchar(30),
  Date smalldatetime
)

declare @user table
(
  UserName varchar(30),
  Birthday smalldatetime
)

insert into @nameday
  select 'Bob', '1900-04-22' union
  select 'Bob', '1900-09-04' union
  select 'Frank', '1900-01-02'

insert into @user
  select 'Bob', '1975-08-05' union
  select 'Frank', '1987-01-01'


select UserName, 
       NameDayDate = Date
  from @user as u
    inner join @nameday as nd
      on nd.Name = u.UserName
      and datepart(dayofyear, nd.Date) >= datepart(dayofyear, u.Birthday)
      and not exists (select *
                        from @nameday as ndOlder
                        where ndOlder.Name = nd.Name
                          and datepart(dayofyear, ndOlder.Date) >= datepart(dayofyear, u.Birthday)
                          and ndOlder.Date < nd.Date)