SQL Server：查找本周即将生日的查询

在人力资源数据库或不同的社交网站中，一个常见的查询是找出即将到来的员工/订阅者姓名（在本周或未来几天内）。要找出哪些人的生日在未来几天内，非常简单。

--Create table variable to hold our test records
DECLARE  @Workers  TABLE (WorderName VARCHAR(50), DOB DATETIME)
--Insert test records
INSERT INTO @Workers
SELECT 'Ryan','1972-08-24 00:00:00' UNION ALL
SELECT 'James','1985-09-26 00:00:00' UNION ALL
SELECT 'Jasson','1983-08-25 00:00:00' UNION ALL
SELECT 'Tara','1991-09-24 00:00:00' UNION ALL
SELECT 'William','1992-08-19 00:00:00' UNION ALL
SELECT 'Judy','1989-09-23 00:00:00'
--Variable to provide requried number of days
DECLARE @InNextDays INT
SET @InNextDays = 3        
-- Query to find workers, whose birthday is in given number of days
SELECT  *
FROM    @Workers e
WHERE   1 = 
CASE WHEN MONTH(GETDATE()) < MONTH(GETDATE() + @InNextDays)
     THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE() + @InNextDays)
            ANDDAY(DOB) BETWEEN DAY(DATEADD(s, -1, 
                                    DATEADD(mm, DATEDIFF(m, 0, 
                                    GETDATE()) + 1, 0) + 1))
                     AND     DAY(GETDATE()
                                    + @InNextDays) THEN 1
               WHENMONTH(DOB) = MONTH(GETDATE())
                    ANDDAY(DOB) BETWEEN DAY(GETDATE()) + 1
                                 AND     DAY(GETDATE())
                                         + @InNextDays THEN 1
               ELSE0
          END
     ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())
                    ANDDAY(DOB) BETWEEN DAY(GETDATE()) + 1
                                 AND     DAY(GETDATE())
                                         + @InNextDays THEN 1
               ELSE0
          END
END

以下查询将帮助您找到本周生日的员工。

-- Query to find workers, whose birthday is in current week
SELECT  *
FROM    @Workers e
WHERE   1 = CASE WHEN MONTH(GETDATE()) < MONTH(DATEADD(WK, 
                                       DATEDIFF(WK, 0, GETDATE())+1,-1))
THEN CASE WHEN MONTH(DOB) = MONTH(GETDATE()) + 1
            ANDDAY(DOB) >= 1
            ANDDAY(DOB) < DAY(DATEADD(WK,
                                 DATEDIFF(WK, 0, GETDATE())
                                       + 1, -1)) THEN 1 
    WHEN MONTH(DOB) = MONTH(GETDATE())
            ANDDAY(DOB) >= DAY(GETDATE())
            ANDDAY(DOB) <= DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))) THEN 1
      
       ELSE 0 END
  
ELSE CASE WHEN MONTH(DOB) = MONTH(GETDATE())
            ANDDAY(DOB) >= DAY(GETDATE())+1
            ANDDAY(DOB) < DAY(DATEADD(WK,
                                 DATEDIFF(WK, 0, GETDATE())
                                       + 1, -1)) THEN 1
       ELSE 0
  END
END