Seleccionar empleados con cumpleaños en un range dado usando Oracle SQL

Para seleccionar cumpleaños entre dos meses donde FROMDATE y TODATE son algunos parameters en una statement preparada, pensé algo como esto:

select p.id as person_id, ... ... where e.active = 1 and extract(month from TODATE) >= extract(month from e.dateOfBirth) and extract(month from e.dateOfBirth) >= extract(month from FROMDATE) order by extract(month from e.dateOfBirth) DESC, extract(day from e.dateOfBirth) DESC 

¿Cómo se puede mejorar esto para que funcione también con los días?

Hay más de una forma de search ranges de dates en Oracle. Para su escenario, le sugiero que convierta los elementos de mes y día de todas las dates involucradas en numbers.

 select p.id as person_id, ... ... where e.active = 1 and to_number (to_char( e.dateOfBirth, 'MMDD')) between to_number (to_char( FROMDATE, 'MMDD')) and to_number (to_char( TODATE, 'MMDD')) order by extract(month from e.dateOfBirth) DESC, extract(day from e.dateOfBirth) DESC 

Esto no usará ningún índice en la columna e.dateOfBirth . Si eso importa depende de la frecuencia con la que desee ejecutar la consulta.


@AdeelAnsari comenta:

"No me gusta to_char el pnetworkingicado, para hacer uso del índice"

¿Qué índice? Un índice normal en dateOfBirth no va a servir de nada, ya que las inputs de índice llevarán con el elemento year. Por lo tanto, no le ayudará a encontrar todos los loggings de personas nacidas el 23 de diciembre.

Un índice basado en function, o en 11g, una columna virtual con un índice (básicamente lo mismo), es la única forma de indexar partes de una columna de date.

¿Necesita un performance máximo y, por lo tanto, está dispuesto a realizar un cambio en el esquema? ¿O es que el número de loggings es tan pequeño y el performance relativamente poco importante, que desea una consulta que funcione con los datos tal como están?

La manera más simple y rápida de hacerlo es almacenar un segundo campo de datos de nacimiento, pero 'sin' el año. Pongo comillas alnetworkingedor de 'sin' porque una date en realidad no puede tener un año. Entonces, en cambio, basas en otro año.

Volver a dater cada DoB hasta el año 2000 es una buena opción en mi experiencia. Porque incluye un año bisiesto y es un buen número networkingondo. Todos los DoB y FromDate y ToDate funcionarán en el año 2000 …

 WHERE DoB2000 >= FromDate AND DoB2000 <= ToDate 

(Esto supone que también indexa el nuevo campo para hacer una búsqueda rápida; de lo contrario, aún obtendrá un escaneo, aunque PUEDE ser más rápido que la siguiente alternativa).

Alternativamente, puede seguir usando el patrón EXTRACT. Pero eso tendrá una consecuencia desafortunada; es extremadamente desorderado y nunca obtendrá una búsqueda de índice, siempre obtendrá un escaneo de índice. Esto se debe al hecho de que el campo buscado está envuelto en una llamada a function.

 WHERE ( EXTRACT(month FROM e.DateOfBirth) > EXTRACT(month FROM FromDate) OR ( EXTRACT(month FROM e.DateOfBirth) = EXTRACT(month FROM FromDate) AND EXTRACT(day FROM e.DateOfBirth) >= EXTRACT(day FROM FromDate) ) ) AND ( EXTRACT(month FROM e.DateOfBirth) < EXTRACT(month FROM ToDate) OR ( EXTRACT(month FROM e.DateOfBirth) = EXTRACT(month FROM ToDate) AND EXTRACT(day FROM e.DateOfBirth) <= EXTRACT(day FROM ToDate) ) ) 

deberías poder usar

 SELECT * FROM mytbale where dateofbirth between start_dt and end_dt 

alterno:

puede convertir dates al día del año usando:

 to_char( thedate, 'ddd' ) 

luego verifique el range (tenga en count que tiene el mismo problema que la respuesta de @Dems, donde no debe abarcar el final del año como del 25 de diciembre al 10 de enero).

Esta es la consulta que estoy usando para birtdates en los próximos 20 días:

 SELECT A.BIRTHDATE, CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) AGE_NOW, CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE + 20) / 12)) AGE_IN_20_DAYS FROM USERS A WHERE CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) <> CEIL(ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE + 20) / 12)); 
  • ABS (MONTHS_BETWEEN(A.BIRTHDATE, SYSDATE) / 12)) devuelve la edad en formatting 38.9, 27.2, etc.
  • La aplicación de ceil() nos dará la diferencia en años que necesitamos para determinar si esta persona está cerca de tener una date de nacimiento. P.ej.

    1. Edad: 29.9
    2. 29.9 + (20 días) = ​​30.2
    3. ceil (30.1) – ceil (29.9) = 1

Este es el resultado de las consultas al december 16 :

 BIRTHDATE AGE_NOW AGE_IN_20_DAYS 12/29/1981 35 36 12/29/1967 49 50 1/3/1973 44 45 1/4/1968 49 50 

No sé cómo se comporta esto en términos de performance, pero trataría de usar la resta de date regular. Digamos, por ejemplo, que desea días de nacimiento entre el 1 de enero y el 1 de julio. Cualquiera que tenga entre 25 y 25.5 calificaría. Es decir, cualquier persona cuya edad parcial sea inferior a 1/2 año calificaría. La matemática es fácil en 1/2, pero es lo mismo independientemente de la window. En otras palabras, "dentro de un mes" significa +/- 1/12 de un año, dentro de 1 día significa +/- 1/365 de un año, y así sucesivamente.

El año no tiene importancia en este método, así que usaré el año actual al crear la variable.

Además, pensaría en el centro del range, y luego haré valores absolutos (o eso, o siempre usaré una date futura).

Por ejemplo

 select personid from mytable where abs(mod(dob - target_birth_date)) < 1/52 

te daría a todos con un cumpleaños dentro de una semana.

Me doy count de que este código apenas es un pseudocódigo, pero parece que podría permitirte hacerlo, y aún podrías usar índices si lo modificaras un poco. Solo trato de pensar fuera de la caja.

Al final elegimos una solución diferente para arena en donde agregamos el puño para crear la date del aniversario:

 where ... and (to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) > 0 and add_months(e.dateofbirth, (to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) * 12) >:fromDate: and :toDate: > add_months(e.dateofbirth, (to_char(sysdate,'yyyy') - to_char(e.dateofbirth,'yyyy')) * 12) order by extract(month from e.dateofbirth) DESC, extract(day from e.dateofbirth) DESC) 

Esa es la solución !!!

 SELECT * FROM PROFILE prof where to_date(to_char(prof.BIRTHDATE, 'DDMM'), 'DDMM') BETWEEN sysdate AND sysdate+5 

o

 add_months(to_date(to_char(prof.BIRTHDATE, 'DDMM'), 'DDMM'),12) BETWEEN sysdate AND sysdate+5 

Chicos, tengo una solución más simple para este problema

  • paso 1. convierte el mes en número,
  • paso 2. concatenar el día (en dos dígitos) al mes
  • paso 3. luego convierta el resultado en número haciendo to_number (resultado)
  • paso 4. Una vez que tenga esto para la date de inicio y la date de finalización, haga una function entre ellos y termine.

Código de muestra:

 SELECT date_of_birth FROM xxxtable where to_number(to_number(to_char(to_date(substr(date_of_birth,4,3),'mon'),'mm'))||substr(date_of_birth,1,2)) between to_number(to_number(to_char(to_date(substr(:start_date_variable,4,3),'mon'),'mm'))||(substr(:start_date_variable,1,2))) and to_number(to_number(to_char(to_date(substr(:end_date_variable,4,3),'mon'),'mm'))||(substr(:end_date_variable,1,2)));