检查生日是否在跨越年假的 6 个月跨度之间

Question

我正在尝试编写SQL代码(使用SQL开发人员(，以检查一个人在过去6个月的保险期内是否过生日。

这就是我的代码当前的样子。

SELECT DRIVER_KEY, CASE WHEN BDAY BETWEEN EFFDAY AND EXPDAY THEN 1 ELSE 0 END AS BDAYIND FROM (
SELECT DISTINCT A.DRIVER_KEY
, TO_CHAR(A.BIRTH_DATE,'mm/dd') AS BDAY          
, TO_CHAR(SUBSTR(A.EFFECTIVE_DATE_KEY,5,2)||'/'||SUBSTR(A.EFFECTIVE_DATE_KEY,7,2) ) AS EFFDAY
, TO_CHAR(SUBSTR(A.EXPIRATION_DATE_KEY,5,2)||'/'||SUBSTR(A.EXPIRATION_DATE_KEY,7,2) ) AS EXPDAY
FROM DRIVER_TABLE A
);

它有效 - 只要该术语不跨越年份的中断。但是，我的代码目前说 01/25 不在 09/19 和 03/19 之间......我该如何解决这个问题？

Answer 1

编辑：正如APC指出的那样，我的解决方案不适用于闰年。我通常会删除这篇文章，但它已经被选为问题的答案。我更新了下面的代码，以使用Brian Leach解决方案中的年份逻辑而不是to_date字符串。请对布莱恩或APC的答案投赞成票。

这是我使用任意日期的创建语句：

create table DRIVER_TABLE
(
BIRTH_DATE date,
EFFECTIVE_DATE_KEY date,
EXPIRATION_DATE_KEY date
);
insert into DRIVER_TABLE
values(to_date('05/01/1980','MM/DD/YYYY'),
to_date('11/01/2016','MM/DD/YYYY'),
to_date('04/01/2017','MM/DD/YYYY'));

下面是查询：

select case when BirthdayEFFYear between EFFECTIVE_DATE_KEY and EXPIRATION_DATE_KEY 
or BirthdayEXPYear between EFFECTIVE_DATE_KEY and EXPIRATION_DATE_KEY
or to_number(EXPIRATION_DATE_KEY - EFFECTIVE_DATE_KEY) / 365 > 1
then 1 else 0 end BDAYIND
from(
select add_months(BIRTH_DATE,12 * (extract(year from EFFECTIVE_DATE_KEY) - extract(year from BIRTH_DATE))) BirthdayEFFYear,
add_months(BIRTH_DATE,12 * (extract(year from EXPIRATION_DATE_KEY) - extract(year from BIRTH_DATE))) BirthdayEXPYear,
EFFECTIVE_DATE_KEY,EXPIRATION_DATE_KEY
from DRIVER_TABLE A
)

SQLFiddle

Answer 2

将日期比较为日期，而不是字符串。

显然EFFECTIVE_DATE_KEY在前四个字符中包含年份，因此以下内容应该为您提供所需的内容：

SELECT DRIVER_KEY,
CASE
WHEN BDAY BETWEEN EFFDAY AND EXPDAY THEN 1
ELSE 0
END AS BDAYIND
FROM (SELECT DISTINCT A.DRIVER_KEY,
A.BIRTH_DATE AS BDAY,
TO_DATE(A.EFFECTIVE_DATE_KEY, 'YYYYMMDD') AS EFFDAY,
TO_DATE(A.EXPIRATION_DATE_KEY, 'YYYYMMDD') AS EXPDAY
FROM DRIVER_TABLE A);

祝你好运。

Answer 3

'01/25'

不在'09/19'和'03/19'之间，因为当第二个参数小于第一个参数时between()永远不会成立。你落入这个陷阱是因为你在处理字符串。使用 DATE 数据类型处理日期总是更容易。

看起来您的列effective_date和expiry_date可能不是存储为日期，而是存储为字符串;不幸的是，这是一个常见的数据建模错误。 如果是这样，则需要先将它们强制转换为 DATE，然后再应用以下内容。

此解决方案有一个子查询，该子查询从driver_table中选择相关列，并计算每个驱动程序的当前年龄(以年为单位(。年龄用于派生最后一个生日，然后在主查询中将其与保险条款的范围进行比较。因为我们推导出一个实际日期，所以我们可以使用 Oracle 的标准日期算术，以便正确计算bdayind。

SQL> with cte as (
2       select driver_key
3              , date_of_birth
4              , trunc(months_between(sysdate, date_of_birth)/12) as age
5              , add_months(date_of_birth, 12 * (trunc(months_between(sysdate, date_of_birth)/12))) as last_birthday
6              , effective_date
7              , expiry_date 
8        from driver_table
9    )
10  select driver_key
11         , date_of_birth as dob
12         , age
13         , effective_date as eff_date
14         , expiry_date as exp_date
15         , last_birthday as last_bday
16         , case 
17              when last_birthday between effective_date and expiry_date 
18              then 1
19              else 0 end as bdayind
20  from cte
21  /   
DRIVER_KEY DOB        AGE EFF_DATE  EXP_DATE  LAST_BDAY    BDAYIND
---------- --------- ---- --------- --------- --------- ----------
12 02-APR-98   19 01-DEC-16 31-MAY-17 02-APR-17          1
22 02-APR-98   19 01-JAN-17 30-JUN-17 02-APR-17          1
32 02-SEP-98   18 01-DEC-16 31-MAY-17 02-SEP-16          0
42 02-SEP-98   18 01-JAN-17 30-JUN-17 02-SEP-16          0
SQL> 

子查询生成age和last_birthday仅用于演示目的。在现实生活中，您只需要last_birthday列。

Answer 4

此解决方案与其他解决方案略有不同，如下所示：

1. 它适用于任何有效日期和到期日期之间的任何生日
2. 它占了闰年

raw_data只是为示例设置日期：

WITH
raw_data
AS
(SELECT DATE '1963-08-03' AS birthday
, DATE '2017-04-01' AS effectiveday
, DATE '2017-10-31' AS expirationday
, 'Billy' AS name
FROM DUAL
UNION ALL
SELECT DATE '1995-03-20' AS birthday
, DATE '2017-04-01' AS effectiveday
, DATE '2017-10-31' AS expirationday
, 'Sue' AS name
FROM DUAL
UNION ALL
SELECT DATE '1997-01-15' AS birthday
, DATE '2016-12-01' AS effectiveday
, DATE '2017-05-31' AS expirationday
, 'Olga' AS name
FROM DUAL),
mod_data
AS
(SELECT raw_data.*
, ADD_MONTHS (
birthday
, (extract(year from effectiveday) - extract (year from birthday)) * 12
)
effectiveanniversary
, ADD_MONTHS (
birthday
, (extract(year from expirationday) - extract (year from birthday)) * 12
)
expirationanniversary
FROM raw_data)
SELECT name, mod_data.birthday, effectiveday, expirationday
, CASE
WHEN effectiveanniversary BETWEEN effectiveday AND expirationday
OR expirationanniversary BETWEEN effectiveday AND expirationday
THEN
1
ELSE
0
END
found_between
FROM mod_data
NAME  BIRTHDAY     EFFECTIVEDAY  EXPIRATIONDAY  FOUND_BETWEEN                          
Billy 1963/08/03   2017/04/01    2017/10/31     1                                      
Sue   1995/03/20   2017/04/01    2017/10/31     0                                      
Olga  1997/01/15   2016/12/01    2017/05/31     1