[SQL]找出最近幾天內生日的員工清單

2010-05-07

14499
0
SQL
2010-05-10

[SQL]找出最近幾天內生日的員工清單

前言

當我們要找出某日(@CUR_DATE)後幾天生日的員工要如何做呢? 直覺想法是把年度都指定成同一年後再來DATEDIFF處理，那如果找詢的區間設大一點，造成跨年時，要如何處理呢?

研究

資料準備

(
ID INT IDENTITY(1,1) 
, EMP_ID VARCHAR(32)
, EMP_NAME NVARCHAR(32)
, BIRTHDAY DATETIME
)

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0001', '亂馬客', '1980/06/22')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0002', '亂馬1/2', '1975/01/10')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0003', '亂停車', '1981/12/25')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0004', '亂開車', '1970/05/17')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0005', '亂來', '1972/07/02')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0006', '亂開玩笑', '1972/06/02')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0007', '亂睡覺', '1972/07/01')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0008', '亂打屁', '1972/08/01')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0009', '打死釘', '1979/01/25')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0010', '打不死', '1979/01/29')
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0011', '打打打', '1979/11/29')
SELECT * FROM EMPLOYEE ORDER BY RIGHT(CONVERT(VARCHAR(10), BIRTHDAY, 111), 5)

如何得知某日(@CUR_DATE)後幾天後是否跨年呢? 就是把日期推到截止日期，看年度是否有大於目前的年度。

那些資料要在隔年比較呢? 就是除了看是否有跨年外，還要再比較某日(@CUR_DATE)的月份是否有大於員工的生日。
一般來說@CUR_DATE都是使用GetDate()，因為我要測試不同的基準日期，所以我使用變數來指定值。
測試未跨年
--未跨年 1
DECLARE @BASE_DAYS INT  --最近要過生日的
SET @BASE_DAYS = 31 
DECLARE @CUR_DATE DATETIME --今天的日期
SET @CUR_DATE = CAST('2010/6/15' AS DATETIME)
SELECT *
FROM EMPLOYEE
WHERE  (CASE WHEN YEAR(@CUR_DATE) < YEAR(DATEADD(DAY, @BASE_DAYS , @CUR_DATE))
                AND MONTH(@CUR_DATE) > MONTH(BIRTHDAY)    
    THEN
        --會跨年，所以到下一年度去從後面算回來
        DATEDIFF(DAY, DATEADD(YEAR, 1, CAST(STR(YEAR(@CUR_DATE)) + '/' + STR(MONTH(BIRTHDAY)) + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME)) 
        , DATEADD(DAY,  @BASE_DAYS, @CUR_DATE) )
    ELSE
        --從前面來算
        DATEDIFF(DAY, @CUR_DATE, CAST(STR(YEAR(GETDATE())) + '/' +  STR(MONTH(BIRTHDAY))  + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME)) 
    END)
    BETWEEN 0 AND @BASE_DAYS
ORDER BY RIGHT(CONVERT(VARCHAR(10), BIRTHDAY, 111), 5)
測試跨年
DECLARE @BASE_DAYS INT  --最近要過生日的SET @BASE_DAYS = 75
DECLARE @CUR_DATE DATETIME --今天的日期
SET @CUR_DATE = CAST('2010/11/11' AS DATETIME)

SELECT *
FROM EMPLOYEE
WHERE  (CASE WHEN YEAR(@CUR_DATE) < YEAR(DATEADD(DAY, @BASE_DAYS , @CUR_DATE))
                AND MONTH(@CUR_DATE) > MONTH(BIRTHDAY)    
    THEN
        --會跨年，所以到下一年度去從後面算回來
        DATEDIFF(DAY, DATEADD(YEAR, 1, CAST(STR(YEAR(@CUR_DATE)) + '/' +  STR(MONTH(BIRTHDAY))  + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME)) 
            , DATEADD(DAY,  @BASE_DAYS, @CUR_DATE) )
        
    ELSE
        --從前面來算
        DATEDIFF(DAY, @CUR_DATE
            , CAST(STR(YEAR(GETDATE())) + '/' +  STR(MONTH(BIRTHDAY))  + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME)) 
    END)
    BETWEEN 0 AND @BASE_DAYS
ORDER BY RIGHT(CONVERT(VARCHAR(10), BIRTHDAY, 111), 5)    
看上面的圖，會發現資料排序不正確，所以我們再調整一下Order的部份，加入跨年的處理。
--處理跨年 3 (處理排序)
DECLARE @BASE_DAYS INT  --最近要過生日的
SET @BASE_DAYS = 75
DECLARE @CUR_DATE DATETIME --今天的日期
SET @CUR_DATE = CAST('2010/11/11' AS DATETIME)
SELECT *
FROM EMPLOYEE
WHERE  (CASE WHEN YEAR(@CUR_DATE) < YEAR(DATEADD(DAY, @BASE_DAYS , @CUR_DATE))
                AND MONTH(@CUR_DATE) > MONTH(BIRTHDAY)    
    THEN
        --會跨年，所以到下一年度去從後面算回來
        DATEDIFF(DAY, DATEADD(YEAR, 1, CAST(STR(YEAR(@CUR_DATE)) + '/' + STR(MONTH(BIRTHDAY)) + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME)) 
                , DATEADD(DAY,  @BASE_DAYS, @CUR_DATE) )
        
    ELSE
        --從前面來算
        DATEDIFF(DAY, @CUR_DATE
                , CAST(STR(YEAR(GETDATE())) + '/' +  STR(MONTH(BIRTHDAY))  + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME)) 
    END)
    BETWEEN 0 AND @BASE_DAYS
ORDER BY CASE WHEN YEAR(@CUR_DATE) < YEAR(DATEADD(DAY, @BASE_DAYS , @CUR_DATE))
        AND MONTH(@CUR_DATE) > MONTH(BIRTHDAY) 
        THEN 
            CAST(STR(YEAR(@CUR_DATE) + 1) + RIGHT(CONVERT(VARCHAR(10), BIRTHDAY, 111), 6) AS DATETIME)
        ELSE
            CAST(STR(YEAR(@CUR_DATE) ) + RIGHT(CONVERT(VARCHAR(10), BIRTHDAY, 111), 6) AS DATETIME)
        END 
處理2/29日! 再加入一筆資料是2/29，結果在換年度去轉成日期時，會發生"char 資料類型轉換成 datetime 資料類型時，產生超出範圍的 datetime 值。"的錯誤。原因是因為該年度沒有2/29日，所以在轉日期時，要再加上判斷員工的生日是否為2/29，而比較的年度是否為潤年，不是的話，就回傳-1，該年該員工就沒有生日過了!
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, BIRTHDAY) VALUES('0012', '郭小玉', '1976/2/29')
 --處理跨年 4 (處理2/29問題)
DECLARE @BASE_DAYS INT  --最近要過生日的
SET @BASE_DAYS = 95
DECLARE @CUR_DATE DATETIME --今天的日期
SET @CUR_DATE = CAST('2010/11/11' AS DATETIME)
SELECT *
FROM EMPLOYEE
WHERE  (CASE WHEN YEAR(@CUR_DATE) < YEAR(DATEADD(DAY, @BASE_DAYS , @CUR_DATE))
				AND MONTH(@CUR_DATE) > MONTH(BIRTHDAY)	
	THEN
		--會跨年，所以到下一年度去從後面算回來
		CASE WHEN MONTH(BIRTHDAY) = 2 AND DAY(BIRTHDAY) = 29 
				AND NOT ( (YEAR(@CUR_DATE)+1)%400 = 0 OR ( (YEAR(@CUR_DATE)+1)%4 = 0 AND (YEAR(@CUR_DATE)+1)%100 <> 0 )) 
			THEN
				-1
			ELSE
				DATEDIFF(DAY, DATEADD(YEAR, 1, CAST(STR(YEAR(@CUR_DATE)) + '/' + STR(MONTH(BIRTHDAY)) + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME)) 
						, DATEADD(DAY,  @BASE_DAYS, @CUR_DATE) )
			END
		
	ELSE
		--從前面來算
		CASE WHEN MONTH(BIRTHDAY) = 2 AND DAY(BIRTHDAY) = 29 
				AND NOT ( (YEAR(@CUR_DATE))%400 = 0 OR ( (YEAR(@CUR_DATE))%4 = 0 AND (YEAR(@CUR_DATE))%100 <> 0 )) 
			THEN
				-1
			ELSE
				DATEDIFF(DAY, @CUR_DATE
					, CAST(STR(YEAR(GETDATE())) + '/' +  STR(MONTH(BIRTHDAY))  + '/' +  STR(DAY(BIRTHDAY)) AS DATETIME))
			END
		 
	END)
	BETWEEN 0 AND @BASE_DAYS
ORDER BY CASE WHEN YEAR(@CUR_DATE) < YEAR(DATEADD(DAY, @BASE_DAYS , @CUR_DATE))
		AND MONTH(@CUR_DATE) > MONTH(BIRTHDAY) 
		THEN 
			CAST(STR(YEAR(@CUR_DATE) + 1) + RIGHT(CONVERT(VARCHAR(10), BIRTHDAY, 111), 6) AS DATETIME)
		ELSE
			CAST(STR(YEAR(@CUR_DATE) ) + RIGHT(CONVERT(VARCHAR(10), BIRTHDAY, 111), 6) AS DATETIME)
		END  
以上是我直覺的做法分享給大家，如果有更好的做法，請讓我知道，謝謝!
2010/05/10補充: 一般來說，近期的Base日應該不會超過一年，所以直接以基準日及員工生日的月份來比較即可! 以下顯示改以字串比較及日期比較，字串的話，如果沒有2/29的話，那生日為2/29日的員工還是會被List出來，以日期比較則不會被List出來!
 
--2010/05/10  
--改用月份比較年度是否+1，同時以字串來比較(不處理2/29日) 
--因為如果目前的月份比員工的生日月份大的話，那表示員工的生日年度要加1來比較 
DECLARE @BASE_DAYS INT  --最近要過生日的天數 
SET @BASE_DAYS = 95 
DECLARE @CUR_DATE DATETIME --今天的日期 
SET @CUR_DATE = CAST('2010/12/11' AS DATETIME) 
SELECT * 
FROM EMPLOYEE 
WHERE LTRIM(STR(YEAR(@CUR_DATE)  
+ CASE WHEN MONTH(@CUR_DATE) > MONTH(BIRTHDAY) THEN 1 ELSE 0 END))  
+ RIGHT(CONVERT(VARCHAR , BIRTHDAY, 111), 6) 
BETWEEN CONVERT(VARCHAR, @CUR_DATE, 111)  
    AND CONVERT(VARCHAR, DATEADD(DAY, @BASE_DAYS, @CUR_DATE), 111) 
ORDER BY LTRIM(STR(YEAR(@CUR_DATE)  
+ CASE WHEN MONTH(@CUR_DATE) > MONTH(BIRTHDAY) THEN 1 ELSE 0 END))  
+ RIGHT(CONVERT(VARCHAR , BIRTHDAY, 111), 6)
 
--改用月份比較年度是否+1，同時以日期來比較(處理2/29日) 
--因為如果目前的月份比員工的生日月份大的話，那表示員工的生日年度要加1來比較 
DECLARE @BASE_DAYS INT  --最近要過生日的 
SET @BASE_DAYS = 95 
DECLARE @CUR_DATE DATETIME --今天的日期 
SET @CUR_DATE = CAST('2010/12/11' AS DATETIME) 
SELECT * 
FROM EMPLOYEE 
WHERE CASE WHEN  
      MONTH(BIRTHDAY) = 2 AND DAY(BIRTHDAY) = 29  
      AND NOT ( (YEAR(@CUR_DATE) + CASE WHEN MONTH(@CUR_DATE) > MONTH(BIRTHDAY) THEN 1 ELSE 0 END)%400 = 0  
      OR ( (YEAR(@CUR_DATE) + CASE WHEN MONTH(@CUR_DATE) > MONTH(BIRTHDAY) THEN 1 ELSE 0 END)%4 = 0  
      AND (YEAR(@CUR_DATE) + CASE WHEN MONTH(@CUR_DATE) > MONTH(BIRTHDAY) THEN 1 ELSE 0 END)%100 <> 0 ))  
  THEN 
      --該年沒有 2/29 
      BIRTHDAY 
  ELSE 
      CAST(STR(YEAR(@CUR_DATE)  
      + CASE WHEN MONTH(@CUR_DATE) > MONTH(BIRTHDAY) THEN 1 ELSE 0 END)  
      + RIGHT(CONVERT(VARCHAR , BIRTHDAY, 111), 6) AS DATETIME) 
  END 
BETWEEN @CUR_DATE AND DATEADD(DAY, @BASE_DAYS, @CUR_DATE) 
ORDER BY STR(YEAR(@CUR_DATE)  
+ CASE WHEN MONTH(@CUR_DATE) > MONTH(BIRTHDAY) THEN 1 ELSE 0 END)  
+ RIGHT(CONVERT(VARCHAR , BIRTHDAY, 111), 6)

`測試程式`

近期日期處理_3.rar


                    Hi, 

亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^

亂馬客

https://rainmakerho.github.io/

[SQL]找出最近幾天內生日的員工清單

前言

研究

資料準備

如何得知某日(@CUR_DATE)後幾天後是否跨年呢? 就是把日期推到截止日期，看年度是否有大於目前的年度。

那些資料要在隔年比較呢? 就是除了看是否有跨年外，還要再比較某日(@CUR_DATE)的月份是否有大於員工的生日。

測試未跨年

測試跨年

看上面的圖，會發現資料排序不正確，所以我們再調整一下Order的部份，加入跨年的處理。

`測試程式`

學習網站

標籤雲

系列文章

最新留言