Lanzamiendo online de SQL2012

El 7 de marzo es el lanzamiento online del SQL2012! les dejo un link con la agenda para que puedan ir palpitandolo:

http://www.sqlserverlaunch.com/LATAM/agenda

LAG y LEAD, ver el registro anterior o siguiente en SQL 2012

El SQL 2012 sigue teniendo novedades interesantes para mostrar, así que continuaré con la introducción a las nuevas funciones TSQL. Hoy es el turno de LAG y LEAD.
Una cosa que siempre me molestó es cuando un cliente decía "pero esto es fácil, en el excel lo hago" y quizá en SQL no era tan simple de expresar. Un caso común de esto es cuando una "celda" se calculaba utilizando valores de su registro pero también del anterior.
La forma habitual de hacer esto en sql es con un self join, y ahí empezar a remarla. En la versión 2012 contamos con una forma mucho mas cómoda y es con las funciones LAG y LEAD.
La función LAG nos permite consultar el registro inmediatamente anterior al que estamos procesando, y si lo deseamos tambien dos anteriores o el número deseado. Veamos con un ejemplo que es mas claro.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 18/01/12
-- Description: Hacer un cálculo en base al registro anterior en SQL 2012: LAG y LEAD
-- =============================================

-- Primero creamos una tabla con valores de ejemplo para poder probar la funcion
CREATE TABLE #PruebaLAG_LEAD(
id INT PRIMARY KEY, dia DATE, ventas INT
)

INSERT INTO #PruebaLAG_LEAD VALUES
(1, '20120101', 10),
(2, '20120102', 11),
(3, '20120103', 13),
(4, '20120104', 8),
(5, '20120105', 10),
(6, '20120106', 15),
(7, '20120107', 10)

/*
Dada la siguiente tabla deseamos saber la diferencia de ventas de un día con el anterior, así que con los valores de ejemplo obtendriamos:
(1, '20120101', NULL),
(2, '20120102', 1),
(3, '20120103', 2),
(4, '20120104', -5),
(5, '20120105', 2),
(6, '20120106', 5),
(7, '20120107', -5)
*/
-- En la forma tradicional esto lo podríamos hacer así:
SELECT t1.id, t1.dia, t1.ventas, t2.ventas ventaAnterior, t1.ventas - t2.ventas ventaDiferencia
  FROM #PruebaLAG_LEAD t1
 LEFT JOIN #PruebaLAG_LEAD t2 ON t1.id -1 = t2.id

-- Es importante resaltar el null del primer registro, causado por el LEFT JOIN. Esto se debe a que en mi primer registro no tiene sentido comparar con el valor anterior, se va fuera de la lógica de negocio.

-- Ahora veamos como podríamos escribir esto mismo en SQL 2012

SELECT *, LAG(ventas, 1, null)OVER(ORDER BY dia) as ventaAnterior, 
    ventas - LAG(ventas, 1)OVER(ORDER BY dia) as ventaDiferencia
  FROM #PruebaLAG_LEAD

DROP TABLE #PruebaLAG_LEAD

Como ven es muy comodo de utilizar y viene en diferentes sabores. Los tres parámetros que toma (los últimos dos opcionales) son:
LAG(campo, cantidad de registros anteriores a buscar, valor default si es nulo)

En caso de que no se indique cuantos registros atras mirar se asume uno. Y en caso de no indicar un valor default es null (tal como en el ejemplo que utilice una vez null de forma explícita y otra implícita).

Lo único que no vimos es que pasaría si quisiera ignorar el registro inicial, para no tener ese valor nulo. En las versiones anteriores me bastaría con cambiar el LEFT JOIN por un INNER JOIN, pero ahora debo poner un poco mas de mano y utilizar una CTE.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 18/01/12
-- Description: Hacer un cálculo en base al registro anterior en SQL 2012: LAG y LEAD
-- =============================================

-- Primero creamos una tabla con valores de ejemplo para poder probar la funcion
CREATE TABLE #PruebaLAG_LEAD(
id INT PRIMARY KEY, dia DATE, ventas INT
)

INSERT INTO #PruebaLAG_LEAD VALUES
(1, '20120101', 10),
(2, '20120102', 11),
(3, '20120103', 13),
(4, '20120104', 8),
(5, '20120105', 10),
(6, '20120106', 15),
(7, '20120107', 10)

-- Como ignorar el registro inicial para que no quede el NULL

;WITH cte AS(
SELECT ROW_NUMBER()OVER(ORDER BY dia) rn, *, LAG(ventas, 1, null)OVER(ORDER BY dia) as ventaAnterior, 
    ventas - LAG(ventas, 1)OVER(ORDER BY dia) as ventaDiferencia
  FROM #PruebaLAG_LEAD
)
SELECT * 
  FROM cte
WHERE rn>1

DROP TABLE #PruebaLAG_LEAD

La función LEAD es equivalente pero para el siguiente registro en lugar del anterior así que no entraremos en mas detalle.

Pro
Hace que sea mucho mas simple el tipo de cálculos de valores precedentes
Es mas declarativo y eficiente que la vieja forma
Es muy flexible, puede ser variable el segundo campo, pudiendo utilizar algo rebuscado como comparar todo contra el principio de mes
Contra
Es incomodo si no se quiere incluir el registro inicial

Mas info de LAG: http://msdn.microsoft.com/es-us/library/hh231256(v=sql.110).aspx
Mas info de LEAD: http://msdn.microsoft.com/es-us/library/hh213125(v=sql.110).aspx

Saber el fin de mes en SQL 2012: EOMONTH

Buenas a todos!!! En este articulo continuaremos contando las novedades del TSQL de SQL 2012.
En esta oportunidad quiero presentarles la función EOMONTH. Esta función se puede utilizar de dos maneras, con un solo parámetro o con dos.
Cuando la utilizamos con un solo parámetro, éste debe ser de tipo fecha y la función lo que retorna es el último día del mes correspondiente al parámetro. Esto en versiones anteriores de SQL se podía hacer de varias formas, pero de todas era bastante engorroso.
En el ejemplo veremos alguna de esas alternativas.
La segunda opción, muy práctica por cierto, es pasandole la fecha y otro parámetro que indica cuantos meses sumar o restar. Esto lo que hará es retornar el fin de mes siguiente. Veamos como funcionan.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 18/01/12
-- Description: Saber el fin de mes en SQL 2012
-- =============================================

-- Primero declaramos una variable de tipo fecha para la prueba. Puede ser datetime sin inconveniente.
DECLARE @date DATE;
SET @date = GETDATE();

-- Veamos una de las opciones de como calcular el principio de mes y fin de mes con la sintaxis permitida en versiones anteriores de SQL.
-- Repito, esta es una forma de hacerlo pero hay varias
SELECT @date AS FechaOriginal,
    DATEADD(DAY,-1 * (DATEPART(DAY, @date))+1, @date) AS PrincipioDeMes,
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY,-1 * (DATEPART(DAY, @date))+1, @date))) AS FinDeMes
        

-- Ahora con la función EOMONTH podemos expresarlo de forma directa
SELECT EOMONTH ( @date ) as FinDeMes,
       EOMONTH ( @date, 1 ) as FinSiguienteMes,
       EOMONTH ( @date, -1 ) as FinMesAnterior;

-- Y si en cambio no queremos el fin de mes sino el comienzo, podemos hacer lo siguiente
-- -> Calcular el fin del mes anterior y sumarle un dia
SELECT DATEADD(DAY, 1, EOMONTH ( @date, -1 )) as PrincipioDeMes;

Pro

• Permite escribir de forma muy declarativa y simple el fin de mes, algo que es muy utilizado
• La sintaxis es muy intuitiva

Contra

• No entiendo porque no se aplica lo mismo para principio de mes, esto hacer perder ortogonalidad. Podria ser otra o un parámetro que indique si se desea el fin o el comienzo de mes

Para mas información: http://msdn.microsoft.com/en-us/library/hh213020%28v=sql.110%29.aspx