Quitar caracteres como Enter y Tabulado

Varios usuarios han reportado problemas al generar un reporte, mediante una consulta SQL, donde el resultado de cada fila lo entrega en dos o más líneas. También las columnas quedan desplazadas, apareciendo filas con más columnas que las debidas.

La mayoría de las veces este problema se debe a que un dato extraído en el reporte contiene los caracteres de control Enter y/o Tabulado.

Para evitar este problema se debe de reemplazar el o los caracteres de control por un espacio o cualquier otro carácter (puede ser incluso carácter nulo).

El Enter está formado por un salto de línea (carácter 13) y un retorno de carro (carácter 10). Para quitarlo se debe de reemplazar esta secuencia de caracteres por un espacio o cualquier texto que se desee.

El Tabulado es representado con el carácter número 9, por lo que para quitarlo se reemplaza por un espacio o el carácter deseado.

Quitar caracteres de control (Enter y Tabulado) de columnas en una consulta con Oracle:

select datos_sin_problema, replace(nombre_del_campo, chr(13)||chr(10), ' ') dato_sin_enter, replace(nombre_del_campo, chr(9), ' ') dato_sin_tabulado, replace(replace(nombre_del_campo, chr(13)||chr(10), ' '), chr(9), ' ') dato_sin_enter_ni_tabulado from nombre_de_tabla;

En esta consulta se extraen el o los datos sin problemas, y los datos con problemas se extraen en 3 formas: quitando solo el Enter, quitando solo el Tabulado y por último quitando Enter y Tabulado. Se utilizan las funciones REPLACE para sustituir los caracteres, y CHR para obtener la representación del carácter. Los caracteres se concatenan con el operador ||.

Quitar caracteres de control (Enter y Tabulado) de columnas en una consulta con Microsoft Access:

select datos_sin_problema, replace(nombre_del_campo,chr(13) + chr(10), ' ') as dato_sin_enter, replace(nombre_del_campo,chr(9), ' ') as dato_sin_tabulado, replace(replace(nombre_del_campo, chr(13) + chr(10), ' '), chr(9), ' ') as dato_sin_enter_ni_tabulado from nombre_de_tabla;

Al igual que en Oracle, esta consulta se extraen el o los datos sin problemas, y para los datos que tienen problemas se extraen en 3 formas: solo Enter, solo Tabulado y por último Entero y Tabulado.

Se utilizan las funciones REPLACE para sustituir los caracteres, y CHR para obtener la representación del carácter. La diferencia es que el texto se concatena con el operador +.

La solución presentada corrige el problema, pero es preferible evitarlo. Antes que nada se debe de analizar si el campo en cuestión debería o no almacenar estos caracteres de control. Por ejemplo si se trata de nombre del cliente no lo debería tener, pero si se trata de observaciones es válido que lo tenga.

En caso de que no sea válido, es preferible corregir el problema de raíz. Si el sistema con que se introducen los datos permite caracteres de control, se recomienda modificar dicho sistema de manera de que antes de grabar se realice la “limpieza” del texto.

Una vez controlado el ingreso de los datos, se deberá corregir los datos que ya están almacenados con caracteres de control, para esto se utiliza una simple sentencia UPDATE con las funciones antes mostradas.

Quitar caracteres de control (Enter y/o Tabulado) con Oracle:

Quitar el carácter Enter con Oracle:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(13)||chr(10), ' ') where nombre_del_campo like '%' || chr(13)||chr(10) || '%';

Quitar el carácter Tabulado con Oracle:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '%' || chr(9) ||'%';

Quitar ambos caracteres:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, replace(nombre_del_campo, chr(13)||chr(10), ' '), chr(9), ' ') where nombre_del_campo like '%' || chr(9) ||'%' or nombre_del_campo like '%' || chr(13)||chr(10) || '%';

Quitar caracteres de control (Enter y/o Tabulado) con Microsoft Access:

Quitar el carácter Enter con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(13) + chr(10), ' ') where nombre_del_campo like '*' + chr(13) + chr(10) + '*';

Quitar el carácter Tabulado con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '*' + chr(9) + '*';

Quitar ambos caracteres con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '*' + chr(9) + '*' or nombre_del_campo like '*' + chr(13) + chr(10) + '*';

Si se desea quitar algún otro carácter de control, primero se deberá de identificar de cual carácter se trata y seguir los pasos presentados.

Escribir números con letras con Oracle

Siguiendo la misma lógica que se utilizó para escribir un número con letras con planillas de cálculo, aquí se mostrará como realizar esta misma operación con Oracle.

Como primer paso se debe de crear la tabla con los nombres de los números 0 al 999, ésta se llamará nombres_de_numeros y tendrá la siguiente estructura:

CREATE TABLE NOMBRES_DE_NUMEROS ( NUMERO NUMBER(3) NOT NULL, NOMBRE VARCHAR2(50) NOT NULL );

Se definirá una clave primaria:

ALTER TABLE NOMBRES_DE_NUMEROS ADD ( CONSTRAINT PK_NOMBRES_DE_NUMEROS PRIMARY KEY (NUMERO) USING INDEX );

Esta tabla se puede configurar para que se almacene en el KEEP de manera de que Oracle la mantenga en memoria para mejorar el rendimiento.

Una vez creada la tabla se deberá cargar la información con los nombres de los números de 0 al 999 disponible aquí.

Consulta SQL para escribir un número con letras en Oracle:

Como se han presentando errores en la edición de este artículo, el código SQL se encuentra disponible aquí.Cambiar la consulta sobre dual por la tabla donde se encuentren los números a convertir.

Si esta operación se requiere con alta frecuencia, analizar la posibilidad de crear una función Oracle que realice la conversión de manera evitar accesos repetidos y simplificar la sintaxis.

Escribir o convertir números a letras

Una necesidad frecuente es escribir un número con letras (convertir número en letras), las planillas de cálculo al igual que los manejadores de base de datos no disponen de una herramienta para dicha tarea, pero es factible realizarla.

En la web hay disponibles diversos complementos y macros en Visual Basic para el caso de Microsoft Excel, y ejemplos con funciones, también para Microsoft Excel que utilizando columnas intermedias. El código de estas funciones se puede convertir al lenguaje que se requiera.
Independientemente del lenguaje con que se implemente en grandes rasgos el criterio para escribir un número con letras es el siguiente:

• Tomar la parte entera del número.
• Considerar la casuística especial para el cero.
• Separar las cifras en miles (grupos de a 3 pociones a partir de la derecha), se tendrán tantos grupos como se deseen implementar, para números normales pueden tomarse: miles de millones, millones, miles, y unidades. La cifra de cada grupo se debe de tratar de la misma manera por lo que la misma funcionalidad utilizada en una es válida para el resto.
  
  • Ver casuísticas especiales como ser el cien.
  • Tomar el valor de la centena de una lista predefinida (ciento, doscientos, ...).
  • Si la decena es corresponde a 1 (10 al 19), tomarlo de una lista exclusiva de para estos (diez, once, doce ...).
  • Tomar el valor de las unidades de una lista predefinida (un, dos, tres, ...).
  • Tener consideraciones con los valores como el 1 = uno / un, 20 (veinte, veinti).

• Luego de convertir cada cifra de miles se debe de poner su denominación: mil, millones, etc.
• Por último si se desean incluir los decimales se deberán de poner en forma fraccionaria, o utilizar la misma función para escribirlos con letra (normalmente se utiliza la primer opción).

Aquí se pretende publicar una función que sea relativamente fácil de comprender, sin el uso de macros, ni columnas adicionales de manera que sea fácil de insertar en una hoja de cálculo.

A diferencia de las funciones disponibles la que se presenta aquí parte de una lista pre convertida de los números del 0 al 999, como se trata de solo 1000 números no ocupa espacio y tendrá un mayor rendimiento.

Función para escribir números con letras con Microsoft Excel en español:

=SI(A2<0;"menos ";"") & ESPACIOS(SI(TRUNCAR(A2)=0;"cero";SI(ABS(A2) > POTENCIA(10;3*3);BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*3));1000);N!$A$1:$B$1001;2;0) & " mil millones ";"")&SI(ABS(A2)>POTENCIA(10;3*2); BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*2));1000) ;N!$A$1:$B$1001;2;0) & SI(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*2));1000)=1; " millón "; " millones ");"")&SI(ABS(A2) > POTENCIA(10;3*1); BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*1)); 1000); N!$A$1:$B$1001;2;0) & " mil ";"") & BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*0));1000);N!$A$1:$B$1001;2;0)) & SI(Y(DERECHA(TRUNCAR(ABS(A2));1)="1"; DERECHA(TRUNCAR(ABS(A2));2)<>"11");"o";""))&SI((ABS(A2)-TRUNCAR(ABS(A2)))<>0;" con "&(TEXTO(ABS(A2-TRUNCAR(A2))*100;"0"))&"/100";"")

Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

La lista de los números junto el ejemlpo completo para Microsoft Excel está disponible aquí.

Función para escribir números con letras con OpenOffice.org Calc:

=IF(A2<0;"menos>POWER(10;3*3);VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*3));1000);$N.$A$1:$B$1001;2;0)&" mil millones ";"")&IF(ABS(A2)>POWER(10;3*2);VLOOKUP(MOD(TRUNC(ABS(A2) / POWER(10;3*2));1000);$N.$A$1:$B$1001;2;0)&IF(MOD(TRUNC(ABS(A2) / POWER(10;3*2));1000)=1;" millón ";" millones ");"")&IF(ABS(A2)>POWER(10;3*1);VLOOKUP(MOD(TRUNC(ABS(A2) / POWER(10;3*1));1000);$N.$A$1:$B$1001;2;0)&" mil ";"")& VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*0));1000);$N.$A$1:$B$1001;2;0))& IF(AND(RIGHT(TRUNC(ABS(A2));1)="1";RIGHT(TRUNC(ABS(A2));2)<>"11");"o";""))&IF((ABS(A2)-TRUNC(ABS(A2)))<>0;" con "&(TEXT(ABS(A2-TRUNC(A2))*100;"0"))&"/100";"")

Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

La lista de los números junto con el ejemplo completo para OpenOffice.org Calc está disponible aquí. Función para escribir números con letras con Microsoft Excel en ingles, Google Docs, Numbers:

Con respecto al anterior aquí cambia la dirección de la lista de números.

=IF(A2<0;"menos>POWER(10;3*3);VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*3));1000);N!$A$1:$B$1001;2;0)&" mil millones ";"")&IF(ABS(A2)>POWER(10;3*2); VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*2));1000);N!$A$1:$B$1001;2;0)& IF(MOD(TRUNC(ABS(A2)/POWER(10;3*2));1000)=1;" millón ";" millones ");"")&IF(ABS(A2)>POWER(10;3*1);VLOOKUP(MOD(TRUNC(ABS(A2)/ POWER(10;3*1));1000);N!$A$1:$B$1001;2;0)&" mil ";"")&VLOOKUP(MOD(TRUNC(ABS(A2) / POWER(10;3*0));1000);N!$A$1:$B$1001;2;0))& IF(AND(RIGHT(TRUNC(ABS(A2));1)="1";RIGHT(TRUNC(ABS(A2));2)<>"11");"o";""))&IF((ABS(A2)-TRUNC(ABS(A2)))<>0;" con "&(TEXT(ABS(A2-TRUNC(A2))*100;"0"))&"/100";"")

Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

Como usar este ejemplo en todas las planillas de cálculo:

Para el uso de esta operación en planillas de cálculo se deberá utilizar el propio archivo que se encuentra disponible, o en el caso de desarrollarlo de cero se deben de realizar los siguientes pasos:

• Crear o utilizar una hoja en blanco con el nombre N, y copiar los datos con los nombres del 0 al 999 (información disponibles en los archivos de ejemplo).
• En otra hoja introducir un número de prueba en la celda A2.
• Poner la función presentada en la celda B2, al hacerlo deberá de mostrar en frase el número introducido en A2.
• Si se quiere mostrar el número en otra celda que no sea A2 y B2 se debe de mover (cortar y pegar) la celda A2 a la primer o única fila donde estará el número a convertir, y mover la celda B2 donde se quiere el primer resultado (otra opción es cambiar A2 por la celda correspondiente en la propia formula).

Una vez familiarizado con el uso de la misma se pueden omitir pasos, incluso dejar la lista en un archivo separado.

Función para escribir números con letras con Oracle:

Para simplificar este artículo este desarrollo se realizó por separado, para verlo acceder aquí.

Separar los componentes de nombres de personas

Extraer los componentes de nombres de personas no es una tarea sencilla, aquí se mostrará una manera muy básica de separar los nombres propios en sus dos apellidos y nombres.

Para este caso se parte de la premisa que los componentes del nombre están separados por espacios, e ingresados siempre en el mismo orden: apellido uno, apellido dos, y nombres.

La idea del proceso es extraer el primer apellido a partir de la primer posición del nombre completo hasta antes de la primer ocurrencia del espacio.

Para extraer el segundo apellido se parte del texto que se encuentra a partir del primer espacio hasta la ubicación del siguiente espacio.

Y por ultimo para extraer los nombres se parte desde la posición siguiente al segundo espacio (largo del primer apellido + largo del segundo apellido + 2 espacios + 1) hasta el final de la cadena de texto.

Este método tiene grandes limitantes tales como:

• No extrae apellidos o nombres compuestos (ej: DEL CAMPO).
• El orden de los componentes es fijo para todos los casos.
• Genera anomalías cuando no existen algunos de los apellidos o nombres.

Separar nombres con Microsoft Excel en español:

Partiendo de que el nombre completo se encuentra en la columna A, se dejará el primer apellido en la columna B, el segundo apellido en la columna C, y los nombres en la columna D.

Primer Apellido (B2)_

=EXTRAE(ESPACIOS(A2); 1; HALLAR(" "; ESPACIOS(A2)) - 1)

Segundo Apellido (C2):

=EXTRAE(EXTRAE(ESPACIOS(A2); LARGO(B2) + 2; LARGO(A2)); 1; HALLAR(" "; EXTRAE(ESPACIOS(A2); LARGO(B2) + 2; LARGO(A2))) - 1)

Nombres (D2):

=EXTRAE(ESPACIOS(A2); LARGO(B2) + LARGO(C2) + 3; LARGO(A2))

Separar nombres con Microsoft Excel en inglés, Google Docs, OpenOffice.org Calc, Numbres:

Se parten de las mismas premisas que en ejemplo anterior.

Primer Apellido (B2):

=MID(TRIM(A2); 1; SEARCH(" "; TRIM(A2)) - 1)

Segundo Apellido (C2):

=MID(MID(TRIM(A2); LEN(B2) + 2; LEN(A2)); 1; SEARCH(" "; MID(TRIM(A2); LEN(B2) + 2; LEN(A2))) - 1)

Nombres (D2):

=MID(TRIM(A2); LEN(B2) + LEN(C2) + 3; LEN(A2))

Separar nombres con Oracle:

Se realizará una consulta con una lógica similar a la de las hojas de cálculo, la diferencia más importante radica en que no se dispone del valor de la celda anterior para conocer su largo.

SELECT nombre_completo, substr(nombre_completo, 1, instr(nombre_completo, ' ')-1) apellido_uno, substr(substr(nombre_completo, instr(nombre_completo, ' ') + 1), 1, instr(substr(nombre_completo, instr(nombre_completo, ' ') + 1), ' ') - 1) apellido_dos, substr(nombre_completo, instr(nombre_completo, ' ', instr(nombre_completo, ' ') + 1) + 1) nombres FROM (select 'martinez gonzalez martin' nombre_completo from dual);

Esta sentencia es para prueba, cuando se disponga de una tabla cambiar el contenido del from por la tabla donde se encuentre el nombre a procesar, y modificar los nombres de campos.

Separar Nombres con Microsoft Access:

Se realizará una consulta similar a la planteada en Oracle.

SELECT nombre_completo, mid(nombre_completo, 1, instr(nombre_completo, ' ') -1) as apellido_uno, mid(mid(nombre_completo, instr(nombre_completo, ' ') + 1), 1, instr(mid(nombre_completo, instr(nombre_completo, ' ') + 1), ' ')-1) as apellido_dos, mid(nombre_completo, instr(instr(nombre_completo, ' ') + 1, nombre_completo, ' ') + 1) as nombres FROM tabla_nombres;

Tomando los datos de una tabla llamada tabla_nombres que tiene un campo nombre_completo con contiene la información a dividir.

Otras alternativas:

Vale la pena comentar que otra forma muy básica de hacer este proceso es utilizar la herramienta de Microsoft Excel de separar texto en columnas. Aparte de ser muy básica esta operación se debe de hacer por procesos y no definida en formulas como la que aquí se presenta.

Próximamente se publicarán maneras más completas de realizar estas operaciones. Soluciones con mayor inteligencia son realizadas con algoritmos más complejos basados en árboles de decisión y redes neuronales.

Aprovecho el tema para ofrecer mis servicios profesionales para la normalización de nombres propios en bases de datos a gran escala, utilizando algoritmos que interpretan los componentes sin importar el orden, formato y cantidad de componentes del mismo. Lo complementan operaciones adicionales como la identificación del sexo, e identificación nombre repetidos aun cuando estén definidos en formas diferentes (nombres completos vs iniciales, con faltas de ortografía, por sonido, etc.).

Por consultas realizarlas por este correo

Calcular primer y ultimo día hábil del mes

En este artículo se mostrará como calcular el primer y/o último día hábil del mes, en principio sin contemplar los días festivos.

Para hallar el primer día hábil del mes, la idea es la siguiente:

1. Calcular la fecha del primer día del mes.
2. Ver el día de la semana que "cae" primer día del mes.
3. Si se trata de un sábado hay que sumarle dos días, de ser un domingo se suma uno, y para el resto de los días es el propio primero.

Para hallar el último día hábil del mes, la idea es la siguiente:

1. Calcular la fecha del último día del mes.
2. Ver el día de la semana que "cae" el último día del mes.
3. Si se trata de un sábado hay que restarle un día, de ser un domingo se restan 2, y para el resto de los días el mismo fin de mes.

Calcular primer y último día laborable en Microsoft Excel en español:

Primer día laborable del mes:

=A1+SI(DIASEM(A1) = 1; 1; SI(DIASEM(A1) = 7; 2; 0))

Último día laborable del mes:

=FIN.MES(A1;0) - SI(DIASEM(FIN.MES(A1; 0)) = 1; 2; SI(DIASEM(FIN.MES(A1; 0)) = 7; 1; 0))

Partiendo del supuesto que en A1 se encuentra la fecha perteneciente al mes en cuestión. Si se cambia por HOY() se obtiene el dato respecto al día actual.

Variante al Cálculo en Microsoft Excel en español:

En Excel hay una alternativa en la que si se pueden tener en cuenta dos días festivos.

Calcular el primer día hábil considerando los festivos se identifica el último día calendario del mes anterior y a este se le suma 1 día laborable.

Otra fórmula para calcular el primer día hábil del mes en Microsoft Excel sin considerar festivos.

=DIA.LAB(FIN.MES(FECHA(AÑO(A1); MES(A1) - 1; 1); 0); 1)

Otra fórmula para calcular el primer día hábil del mes en Microsoft Excel considerando festivos.

=DIA.LAB(FIN.MES(FECHA(AÑO(A1);MES(A1)-1;1);0);1;RANGO_DIAS_FESTIVOS)

Donde RANGO_DIAS_FESTIVOS corresponde a un rango donde se encuentra la lista de días festivos.

Calcular el primer día hábil considerando los festivos se identifica el último día calendario del mes corriente, a este se le suma 1 día calendario, y se le resta un día laborable.

Otra fórmula para calcular el ultimo día hábil del mes en Microsoft Excel sin considerar festivos.

=DIA.LAB(FIN.MES(A1; 0) + 1; -1)

Otra fórmula para calcular el ultimo día hábil del mes en Microsoft Excel considerando festivos.

=DIA.LAB(FIN.MES(A1;0)+1;-1;RANGO_DIAS_FESTIVOS)

Calcular primer y último día laborable del mes en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:

Calcular primer día laborable del mes:

=A1 + IF(WEEKDAY(A1) = 1; 1; IF(WEEKDAY(A1) = 7; 2; 0))

Calcular ultimo día laborable del mes:

=(date(year(A1); month(A1) + 1; 1) - 1)- if(weekday((date(year(A1); month(A1) + 1; 1) - 1)) = 1; 2; if(weekday((date(year(A1); month(A1) + 1; 1) -1)) = 7; 1; 0))

Al no tener la función que identifica el último día del mes la formula queda un poco mas compleja.

Calcular primer y último día laborable del mes en Oracle:

Cálculo para el primer día laborable del mes:

select to_date('200902', 'yyyymm') + decode(to_char(to_date('200902', 'yyyymm'), 'd'), '1', 1, '7', 2, 0) fecha_fija, trunc(sysdate, 'mm') + decode(to_char(trunc(sysdate, 'mm'), 'd'), '1', 1, '7', 2, 0) respecto_a_hoy from dual;

Cálculo para el ultimo día laborable del mes:

select last_day(sysdate) - decode(to_char(last_day(sysdate), 'd'), '1', 2, '7', 1, 0) respecto_a_hoy, last_day(to_date('200803', 'yyyymm')) - decode(to_char(last_day(to_date('200803', 'yyyymm')), 'd'), '1', 2, '7', 1, 0) fecha_fija from dual;

Calcular primer y último día laborable del mes en MySQL:

Cálculo para el primer día laborable del mes:

select adddate(date(concat(extract(year_month from '2009-03-15'), '01')), case weekday(date(concat(extract(year_month from '2009-03-15'), '01'))) when 6 then 2 when 5 then 1 else 0 end) fecha_fija, adddate(date(concat(extract(year_month from sysdate()), '01')), case weekday(date(concat(extract(year_month from sysdate()), '01'))) when 6 then 2 when 5 then 1 else 0 end) respecto_a_hoy;

Cálculo para el ultimo día laborable del mes:

select ADDDATE(last_day(sysdate()), - case weekday(last_day(sysdate())) when 6 then 2 when 5 then 1 else 0 end) respecto_a_hoy, ADDDATE(last_day('2008-08-01'), - case weekday(last_day('2008-08-01')) when 6 then 2 when 5 then 1 else 0 end) fecha_fija;

Calcular primer y último día laborable del mes en Microsoft Access:

Cálculo para el primer día laborable del mes:

SELECT cdate("1/02/2009") + IIf(Weekday(cdate("1/02/2009")) = 1, 1, IIf(Weekday(cdate("1/02/2009")) = 7, 2, 0)) as primer_dia_fecha_fija, DateSerial (year(date()), month(date()), 1) + IIf(Weekday(DateSerial (year(date()), month(date()), 1)) = 1, 1, IIf(Weekday(DateSerial (year(date()), month(date()), 1)) = 7, 2, 0)) as resp_a_hoy FROM datos;

Consulta que toma datos de una supuesta tabla "datos".

Cálculo para el ultimo día laborable del mes:

SELECT (DateSerial (year(date()), month(date()) + 1, 1)-1) - IIf(Weekday( (DateSerial (year(date()), month(date()) + 1, 1)-1)) = 1, 2, IIf(Weekday( (DateSerial (year(date()), month(date()) + 1, 1)-1)) = 7, 1, 0)) as respecto_a_hoy FROM datos;

Consulta que toma datos de una supuesta tabla "datos". Al no disponer de una función que calcule el ultimo día del mes calendario, se calcula el primer día del mes siguiente y se le resta uno (funciona aun para diciembre: 12 + 1 = enero).

Contar cantidad de domingos entre dos fechas o cualquier otro dia de la semana

La formula para calcular la cantidad de un determinado día de la semana (lunes, martes, miércoles, etc.) entre de dos fechas es la siguiente:

Truncar(( Fecha_Final – Fecha_Inicial – DiaDeLaSemana(Fecha_Final-X) + 8) / 7)

Donde X es el número del día de la semana: 0 = Domingo, 1 = Lunes, …, 6 = Sábado.

Esta fórmula puede utilizarse para calcular la cantidad de días laborales entre dos fechas tomando en cuenta los sábados (cosa que las funciones tradicionales no contemplan).

Cálculo en Microsoft Excel en español:

Contar la cantidad de domingos:

=TRUNCAR((B3 - B2 - DIASEM(B3 - 0) + 8) / 7)

Contar la cantidad de lunes:

=TRUNCAR((B3 - B2 - DIASEM(B3 - 1) + 8) / 7)

En B3 se encuentra la fecha final y B2 la fecha inicial.

Cálculo en Google Docs, OpenOffice.org, Calc, Microsoft Excel en inglés:

Traduciendo la fórmula al inglés queda de la siguiente manera:

Contar la cantidad de domingos:

=TRUNC((B3 - B2 - WEEKDAY(B3 - 0) + 8) / 7)

Contar la cantidad de lunes:

=TRUNC((B3 - B2 - WEEKDAY(B3 - 1) + 8) / 7)

Aquí encontrarán un ejemplo al respecto realizado en Google Docs.

Cálculo en Oracle:

Calcular la cantidad de martes que hay entre dos fechas:

SELECT TRUNC((to_date('20090112', 'yyyymmdd') - to_date('20090102', 'yyyymmdd') - to_number(to_char(to_date('20090112', 'yyyymmdd') + 2, 'd'))+ 8 ) / 7) FROM DUAL;

Cantidad de martes entre el 2 de enero de 2009 y hoy:

SELECT TRUNC((TRUNC(SYSDATE) - to_date('20090102', 'yyyymmdd') - to_number(to_char(TRUNC(SYSDATE) + 2, 'd')) + 8 ) / 7) FROM DUAL;

Cálculo en MySQL:

Calcular la cantidad de martes que hay entre dos fechas:

select truncate((datediff(STR_TO_DATE('20090130', '%Y%m%d'), STR_TO_DATE('20090102', '%Y%m%d')) - Weekday(date_add(STR_TO_DATE('20090130', '%Y%m%d'), interval(-2 + 1)day)) + 7) / 7, 0) cantidad_martes;

Aquí los cálculos cambian debido a que Weekday retorna 1 para lunes hasta 6 domingo. Para variar el día de la semana se deberá sustituir el 2 que se encuentra en "interval (-2 + 1)" por el número correspondiente con mismo criterio que antes 0: domingo, 1: lunes, etc.

Cálculo en Microsoft Access:

Cantidad de sábados entre ambas fechas.

SELECT INT((cdate("12/01/2009") - cdate("2/01/2009") - Weekday(cdate("12/01/2009") + 6) + 8) / 7) FROM DATOS;

Cantidad de sábados entre el 2 de enero de 2009 y hoy

SELECT INT((cdate(NOW()) - cdate("2/01/2009") - Weekday(cdate(NOW()) + 6) + 8) / 7) FROM DATOS;

NOTA: Tomada de una supuesta tabla "datos".

Ordenar datos por criterios externos arbitrarios

A continuación se mostrará una forma sencilla de ordenar una serie de datos por un criterio totalmente arbitrario, y que éste se pueda modificar según las necesidades del momento.

La idea del proceso es la siguiente:

• Crear una tabla que contenga al menos una columna los datos que se deben de ordenar, y en una segunda valores numéricos en la donde se definirá el orden.
• Cruzar la información a ordenar con la nueva tabla de manera de agregar una columna adicional con la información del orden.
• Ordenar los datos por la nueva columna.
• En caso que en el futuro se requiera modificar el orden, simplemente se cambia la numeración de la tabla donde está definido.

Solución para hojas de cálculo:

A modo de ejemplo se mostrará una lista de productos que cada uno de ellos tiene una categoría, se ordenarán los productos según un orden cualquiera que se le asignará a cada categoría. : El mismo está disponible en la siguiente dirección de Google Docs.
Para que se puedan ver las fórmulas ha quedado de libre edición, por lo que si ves algo extraño accede a la versión original (Archivo / Historial de Revisiones).
Para verlo en Microsoft Excel (español) se debe de exportar el documento como xls (Archivo / Exportar).

Los pasos son los siguientes:

1. Crear la lista donde se definirá el orden a aplicar. En el ejemplo se trata de la lista que se encuentra en la hoja Categorías. Definir el orden.
2. Crear una nueva columna en la lista de datos originales para posteriormente cruzar la información (columna Orden Externo).
3. Cruzar la información entre las dos tablas de manera de obtener el valor del orden de la segunda hoja. Esto se realizará utilizando la función BUSCARV (VLOOKUP en ingles):
   

   La formula para la busqueda es la siguiente:
   

   Versiones en español:
   

   =BUSCARV($C2; Categorías!$B$2:$D$9; 3; FALSO)
   

   Versiones en inglés:
   

   =VLOOKUP($C2; 'Categorías'!$B$2:$D$9; 3, 0; false)
   

   Siendo C la columna donde se encuentra la clave de búsqueda. En las columnas B y D de la hoja Categorias estarían la clave a buscar y dato resultante respectivamente.
   

   

4. Ordenar la tabla por la nueva columna calculada, adicional a este orden se le puede incluir otros. Para el caso del ejemplo el orden está definido por la columna Orden Externo y posteriormente Nombre Producto.

Solución para base de datos (SQL):

A nivel de base de datos la solución es más fácil, una vez creada la tabla (supongamos con los mismos nombres que la planilla de cálculo mostrada).
Simplemente se debe de hacer un Join entre ambas tablas y ordenar el resultado por el dato del orden.

SELECT Productos.Codigo, Productos.Nombre_Producto, Productos.Categoria, Categorias.Orden FROM Productos, Categorias WHERE Productos.Categoria = Categorias.Categoria ORDER BY Categorias.Orden, Productos.Nombre_Producto

Identificar siguiente día de la semana, siguiente lunes, siguiente martes, .. siguiente jueves, …, etc.

Hay veces que se requiere poder identificar la fecha correspondiente al, por ejemplo, siguiente jueves de la semana. Puede que nos interese compara la fecha de un pago con la del cierre de una remesa, o lo que sea.

Para el cálculo, la idea es la siguiente:

1. Identificar el día de la semana que "estamos parados" (ya sea la fecha actual o determinada fecha).
2. Sabiendo que una de las numeraciones para identificar los días de las semanas es 1: Domingo, 2: Lunes, 3: Martes, ..., 6:Viernes, 7: Sábado.
3. Contar la cantidad de días que distan, por ejemplo, del jueves de la semana en curso.
4. El valor resultante se lo sumamos a la fecha en cuestión. Hasta aquí tenemos la fecha correspondiente al jueves de la semana en curso.
5. Como se requiere el siguiente jueves, debemos ver si ya nos pasamos de ese día para ir a la semana siguiente sumando 7 días.

En el presente ejemplo está realizado para hallar el próximo jueves, si se requiere otro día se debe de cambiar las dos ocurrencias del número 5 correspondiente al jueves por el número correspondiente al día deseado según información del punto 2.

Cálculo en Microsoft Excel en español:

=A1 + 5 - DIASEM(A1; 1) + SI(DIASEM(A1; 1) > 5; 7; 0)

Este ejemplo se calcula según una fecha que se encuentra en la celda A1. Si se desea calcular sobre el día actual se cambia la celda por la función HOY.

=HOY() + 5 - DIASEM(HOY(); 1) + SI(DIASEM(HOY(); 1) > 5; 7; 0)

Cálculo en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:

=A1 + 5 - WEEKDAY(A1; 1) + IF(WEEKDAY(A1; 1) > 5; 7; 0)

Este ejemplo se calcula según una fecha que se encuentra en la celda A1. Si se desea calcular sobre el día actual se cambia la celda por la función TODAY.

=TODAY() + 5 - WEEKDAY(TODAY(); 1) + IF(WEEKDAY(TODAY(); 1) > 5; 7; 0)

Cálculo en Oracle:

select to_date('20081113', 'yyyymmdd') + 5 - to_char(to_date('20081113', 'yyyymmdd'), 'd') + case when to_char(to_date('20081113', 'yyyymmdd'), 'd') > '5' then 7 else 0 end from dual;

Este ejemplo se calcula según una fecha fija. Si se desea calcular sobre el día actual se cambia la celda por la función SYSDATE.

select trunc(sysdate) + 5 - to_char(sysdate, 'd') + case when to_char(sysdate, 'd') > '5' then 7 else 0 end from dual;

Tener en cuenta que muchos de los lenguajes, ej C/C++, de programación no tienen implementada la función de sumar y restar días, en estos casos hay que desarrollarla.

Comparar o Restar horas de diferentes fechas

En varias ocasiones he visto problemas al realizar operaciones con fechas y horas, más precisamente cuando las horas son de diferentes fechas.

Por ejemplo, al restar dos horas cuando las fechas son diferentes, si intentas comparar las fechas y luego las horas restando unas y otras lo puedes hacer pero lleva un poco de lógica algo compleja.

La solución es extremadamente simple: la fecha y hora se deben de sumar generando un único dato del tipo fecha hora.

Esto se debe a que la mayoría de los sistemas para el almacenar un dato que contiene una fecha o una hora utilizan un mismo tipo de dato que contiene la fecha y hora integrada, y de manera visual por formatos se diferencian uno de otro.

Facilitará operaciones tales como:

- Calcular el tiempo transcurrido entre dos horas de igual o distintas fechas.

- Comparar dos horas de igual o distintas fechas.

- Obtener la fecha y hora máxima de un rango.

- Obtener la fecha y hora mínima de un rango.

- Obtener la fecha y hora media de un rango.

Cálculo en Microsoft Excel, Google Docs, OpenOffice.org Calc, Base de Datos:

Diferencia entre fechas (tiempo transcurrido desde la Hora Inicio de la Fecha Inicio hasta la Hora Fin de la Fecha Fin).

=(FechaFin+ HoraFin) – (FechaInicio + HoraInicio)

Comparar fechas, por ejemplo saber si la Hora de Fin es mayor a la Hora de Inicio.

=(FechaFin+ HoraFin) >= (FechaInicio + HoraInicio)

En programa que tanto las fechas como las horas se almacenan en cadenas de caracteres, a la fecha se le debe de concatenar la hora para poder compararlas.

Generar reportes por rangos variables

Una operación muy común al momento de recuperar datos es clasificar el resultado por rangos de valores dependiendo de un determinado criterio, ya sea para posteriormente agruparla o simplemente para saber en que categorías se encuentra.

Dentro de la Minería de Datos (datamining), podemos decir que estas operaciones están incluidas en las técnicas de Limpieza y Transformación de datos.

El proceso para identificar y agrupar los rangos es el siguiente:

1. Identificar el rango al que pertenece cada dato.

2. Agrupar por los criterios necesarios más la información del rango (paso opcional).

3. Realizar los cálculos de agregación de información necesarios (Suma, cuentas, promedios, máximos, mínimos, etc).

La forma "tradicional" de identificar el rango es hacer tantas condiciones como rangos existan, lo que lleva a un reporte complicado que depende de la cantidad de rangos, y ni hablar cuando hay que hacer cambios.

Como alternativa podemos realizar dicha operación utilizando una tabla auxiliar (lista) donde se definen los rangos, y luego mediante una misma consulta preguntar a que rango de dicha tabla pertenece cada valor.

Para cada rango se debe de realizar la siguiente pregunta:

valor_a_buscar "mayor o igual" rango_minimo Y valor_a_buscar "menor" rango_minimo

Cuando el resultado es verdadero se debe a que lo encontramos. Es muy importante no dejar huecos entre los rangos ni superponerlos para evitar perder información ni duplicarla respectivamente.

Solución para hojas de cálculo:

1. Se crea una nueva hoja, por ejemplo llamada RANGOS, donde tendremos tres columnas: A tendrá el rango mínimo, B el rango máximo, y C el nombre del rango.

2. Para buscar los rangos se debe de utilizar la BUSCARV (VLOOKUP en ingles), utilizando la característica del cuarto parámetro la cual habilita la búsqueda en rangos. Buscará el dato en la lista, de no encontrarlo nos dará el dato correspondiente al menor valor superior al que estamos buscando, aquí es muy importante tener ordenada la lista ya que de lo contrario no nos funcionará.

En la hoja donde tenemos nuestra información, se crea una nueva columna llamada Rango donde se ingresará la siguiente formula:

2.a. Formula en Microsoft Excel en Español:

=BUSCARV(B2;RANGOS!$A$2:$C$6;3;VERDADERO)

2.b. Fórmula en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:

=VLOOKUP(B2; RANGOS.$A$2:$C$6; 3; 1)

3. En caso de necesitar agrupar la información, una vez identificado el rango, se crea una tabla dinámica que incluya esta nueva columna y se realiza el reporte. Las operaciones de agregación son provistas por la tabla dinámica (en caso de utilizar Microsoft Excel).

Hice un ejemplo con una lista de países agrupando según la información de su población y superficie, aquí queda disponible un documento de Google Docs

Para que se puedan ver las fórmulas ha quedado de libre edición, por lo que si ven algo extraño accede a la versión original (Archivo / Historial de Revisiones).

Solución para manejadores de base de datos:

1. Se crea la tabla para que contenga los rangos como mínimo con los campos que de la tabla de rangos.

2. Definir la información de los rangos ingresando los datos en la nueva tabla.

3. Al reporte (consulta) que se tenga simplemente se le debe de agregar la tabla de rangos y unirla (join) mediante:

and (valor >= rango_minimo and valor < rango_maximo)

O como un colaborador mencionó en sus comentarios:

and (valor between rango_minimo and (rango_maximo-1))

Ejemplo de reporte detallado:

select td.clave_dato, td.valor_dato, tr.nombre_rango from tabla_datos td, tabla_rangos tr where td.valor_dato >= tr.rango_minimo and td.valor_dato < tr.rango_maximo

4. En caso de requerir las agrupaciones, se deben de agrupar los datos y utilizar las operaciones de agregación necesarias.

select tr.nombre_rango, count(*) as cuenta_dato, sum(otro_valor) as suma_dato from tabla_datos td, tabla_rangos tr where td.valor_dato >= tr.rango_minimo and td.valor_dato < tr.rango_maximo group by tr.nombre_rango

Como siempre si desean mayor información o aclarar algún tema no duden en comunicarse.

Rellenar números con ceros a la izquierda

Cuando se requiere, por ejemplo, rellenar con ceros a la izquierda de un número es fácil hacerlo en sistemas que tienen una función definida, como el LPAD de Oracle, y no tan fácil en planillas de cálculo y muchos lenguajes de programación ya que esta operación no está definida. Hacerlo por suerte la cosa no es tan complicado.

El proceso para rellenar un número con ceros u otro carácter a la izquierda es el siguiente:

Calcular la cantidad de caracteres a insertar de acuerdo al largo definido y el largo que posea el dato al que se le desea dar formato.

Crear una cadena de caracteres repitiendo el carácter de relleno (en nuestro caso 0) tantas veces como el resultado del punto anterior.

Unir la cadena de caracteres creada con el dato original, en este caso los caracteres de relleno van a la izquierda. En el caso de rellenar por la derecha, solo se debe de cambiar el orden en el cual se concatenan las dos cadenas.

A nivel de fórmulas queda de la siguiente manera:

Rellenar con ceros u otro carácter a la izquierda en Microsoft Excel en español:

=REPETIR("0"; 15 - LARGO(A2)) & A2

que es igual a:

=CONCATENAR(REPETIR("0"; 15 - LARGO(A2)); A2)

Para estos casos se rellena hasta con 15 ceros a la izquierda del dato que se encuentra en A2.

Para rellenar por la derecha se invierten los datos a concatenar:

=A2 & REPETIR("0"; 15 - LARGO(A2))

Rellenar con ceros u otro carácter a la izquierda en en Google Docs, OpenOffice.org, Calc, Microsoft Excel en inglés:

=REPT("0"; 15 - LEN(A2)) & A2

que es igual a:

=CONCATENATE(REPT("0"; 15 - LEN(A2)); A2)

Rellenar con ceros u otro carácter a la izquierda o derecha en Oracle:

SELECT LPAD(123456,15,'0') FROM DUAL;

Utilizando la operación ya implementada por Oracle, se rellena con ceros a la izquierda del número 123456. La función para rellenar a la derecha es RPAD.

Rellenar con ceros u otro caracter a la izquierda o derecha en MySQL:

SELECT LPAD(123456,15,'0') relleno_izquierda, RPAD(123456,15,'0') relleno_derecha;

Al igual que Oralcle MySQL dispone de las funciones LPAD y RPAD.

Rellenar con ceros u otro carácter a la izquierda o derecha en Microsoft Access:

SELECT RIGHT('000000000000000' & dato_numerico, 15) as relleno_izquierda, LEFT(dato_numerico & '000000000000000', 15) as relleno_derecha FROM datos;

Ejemplo de rellenar con hasta 15 ceros por la izquierda y la derecha a un dato_numerico perteneciente a la tabla datos.

Para el caso de Microsoft Access se propone una variación a los casos anteriores. Para rellenar por la izquierda se le concatenan muchos ceros a la izquierda, y se toman se extrae los N caracteres que se desean por la derecha de la cadena resultante. Para rellenar por la derecha se utilizan las operaciones inversas.

Calcular primer y último día de la semana

Una necesidad frecuente es identificar el primer y/o ultimo día hábil de la semana (en curso, o especificada por una fecha), esto se resume a identificar la fecha que corresponde a determinado día de la semana.

El proceso para hallar el día es:

1. Identificar el día de la semana que "estamos parados" (ya sea la fecha actual o determinada fecha).
2. Sabiendo que una de las numeraciones para identificar los días de las semanas es 1: Domingo, 2: Lunes, 3: Martes, ..., 6:Viernes, 7: Sábado.
3. Se quitan los días que han transcurrido hasta la fecha en cuestión, así obtenemos la fecha correspondiente al sábado anterior.
4. Sumamos el número del día de la semana que queremos calcular, por ejemplo 2 para el lunes.

A nivel de fórmulas queda de la siguiente manera:

Cálculo en Microsoft Excel en español:

Lunes:
=A1 + 2 - DIASEM(A1; 1)

Viernes:
=A1 + 6 - DIASEM(A1; 1)

NOTA: Estos ejemplos se calculan según una fecha que se encuentra en la celda A1.

Lunes:
=HOY() + 2 - DIASEM(HOY(); 1)

Viernes:
=HOY() + 6 - DIASEM(HOY(); 1)

Cálculo en Google Docs, OpenOffice.org, Calc, Microsoft Excel en inglés:

Lunes:
=A1 + 2 - WEEKDAY(A1; 1)

Viernes:
=A1 + 6 - WEEKDAY(A1; 1)

NOTA: Estos ejemplos se calculan según una fecha que se encuentra en la celda A1.

Lunes:
=TODAY() + 2 - WEEKDAY(TODAY(); 1)

Viernes:
=TODAY() + 6 - WEEKDAY(TODAY(); 1)

NOTA: Estos ejemplos se calculan según una fecha actual (la que tenga la máquina).

Cálculo en Oracle:

Identificar el primer y último día de la semana según fecha actual:

select to_date('20081113', 'yyyymmdd') - to_char( to_date('20081113', 'yyyymmdd'), 'd') lunes, to_date('20081113', 'yyyymmdd') - to_char(to_date('20081113', 'yyyymmdd'), 'd') + 6 viernes from dual;

Cálculo en Microsoft Access:

SELECT cdate("2/12/2008") - Weekday(cdate("2/12/2008")) + 2 as segun_fecha_fija, truncate(now, 'DD') - Weekday(cdate(now())) + 2 as segun_fecha_hoy FROM datos;

NOTA: Tomada de una supuesta tabla "datos".

Estas funciones se pueden utilizar para calcular cualquier día de la semana

Si requieres mas información al respecto hazlo saber, y como siempre si crees conveniente que se deba desarrollar sobre un tema en particular hazlo saber que se tomará en cuenta.