none
Obtener matrículas consecutivas RRS feed

  • Pregunta

  • Buen día desde hace un par de días ando intentando obtener en mi base de datos a todos los alumnos que han estudiado 11 meses consecutivos, pero aún no lo he podido lograr. Verán en las matrículas guardo una fecha de matrícula y un id de apertura, este id de apertura está amarrado a un horario por local y finalmente el horario está amarrado a una programación anual. Mi idea es usar la fecha de fin de horario de la programación anual, ya que tengo casos en los que el alumno de matricula un mismo mes y no me ayudaría la fecha de matrícula. Me gustaría algún ejemplo sencillo de como obtener registros de meses consecutivos. Cualquier ejemplo que me puedan brindar me ayudaría mucho. Gracias
    miércoles, 12 de abril de 2017 15:49

Respuestas

  • ...Deseo obtener en mi base de datos a todos los alumnos que han estudiado 11 meses consecutivos, pero aún no lo he podido lograr. 

    Es una pregunta muy amplía para otorgar respuestas puntuales, quizá requieras comparar contra una tabla que almacene el calendario de matriculas o quizá baste con encontrar la diferencia entre los meses de matricula.

    Por ejemplo, de la lista que se presenta a continuación se busca a todos los alumnos que como mínimo se hayan matriculado 4 veces en meses consecutivos, según los datos de prueba sólo el alumno 'A' cumple con la condición:

    DECLARE @Matricula table (Alumno varchar(100), Fecha date)
    INSERT INTO @Matricula VALUES
        ('A', '20170101'), ('A', '20170201'), ('A', '20170301'), ('A', '20170401'), ('A', '20170501'),
        ('B', '20170101'), ('B', '20170401'), ('B', '20170501'),
        ('C', '20170101'), ('C', '20170201'), ('C', '20170601'), ('C', '20170901'), ('C', '20171101');
    
    WITH T AS
    (
        SELECT 
    	   m.Alumno, m.Fecha,
    	   DATEDIFF(MONTH, m.Fecha, LAG(m.Fecha, 1, DATEADD(MONTH, 1, m.Fecha)) 
    		  OVER(PARTITION BY m.Alumno ORDER BY m.Fecha DESC)) AS [Diff]
        FROM
    	   @Matricula m
    )
    SELECT t1.Alumno FROM T t1 
    GROUP BY t1.Alumno HAVING (COUNT(*) >= 4) AND (COUNT(*) = SUM(t1.Diff));
    GO



    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Propuesto como respuesta Joyce_ACModerator miércoles, 12 de abril de 2017 16:38
    • Marcado como respuesta Carlos Sk miércoles, 12 de abril de 2017 21:15
    miércoles, 12 de abril de 2017 16:12
  • Hola Carlos,

    Ese problema se conoce como "Encontrar Islas (finding islands)" y puedes leer mas sobre este en el ultimo libro de Itzik Ben-Gan (T-SQL Querying).

    Una forma de resolver este problema es enumerando las filas y restar ese valor a la fecha (en este caso como meses). Las filas consecutivas caeran en el mismo mes/año el cual serviria como identificador de grupo.

    El resto seria agrupar por alumno y el identificador mencionado para contar filas y filtrar usando la clausula HAVING.

    Esta solucion trabaja desde la version 2005.

    Ejemplo:

    DECLARE @Matricula table (Alumno varchar(100), Fecha date)
    INSERT INTO @Matricula VALUES
        ('A', '20170101'), ('A', '20170201'), ('A', '20170301'), ('A', '20170401'), ('A', '20170501'),
        ('B', '20170101'), ('B', '20170401'), ('B', '20170501'),
        ('C', '20170101'), ('C', '20170201'), ('C', '20170601'), ('C', '20170901'), ('C', '20171101');
    
    -- mostrar la idea
    WITH R AS (
    SELECT
    	Alumno,
        Fecha,
    	CONVERT(char(6), DATEADD(MONTH, - ROW_NUMBER() OVER(PARTITION BY Alumno ORDER BY Fecha), Fecha), 112) AS grp_helper
    FROM
    	@Matricula
    )
    SELECT
    	R.Alumno,
    	R.grp_helper,
    	R.Fecha
    FROM
    	R
    ORDER BY
    	R.Alumno,
    	R.grp_helper,
    	R.Fecha;
    
    -- solucion
    WITH R AS (
    SELECT
    	Alumno,
        Fecha,
    	CONVERT(char(6), DATEADD(MONTH, - ROW_NUMBER() OVER(PARTITION BY Alumno ORDER BY Fecha), Fecha), 112) AS grp_helper
    FROM
    	@Matricula
    )
    SELECT
    	R.Alumno
    FROM
    	R
    GROUP BY
    	R.Alumno,
    	R.grp_helper
    HAVING
    	COUNT(*) >= 4;
    GO


    Gracias a Willams por proveer data de ejemplo, que es lo que esperamos se adjunte cuando se hace una pregunta de este tipo.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas







    • Editado HunchbackMVP miércoles, 12 de abril de 2017 20:23
    • Marcado como respuesta Carlos Sk miércoles, 12 de abril de 2017 21:14
    miércoles, 12 de abril de 2017 19:09

Todas las respuestas

  • ...Deseo obtener en mi base de datos a todos los alumnos que han estudiado 11 meses consecutivos, pero aún no lo he podido lograr. 

    Es una pregunta muy amplía para otorgar respuestas puntuales, quizá requieras comparar contra una tabla que almacene el calendario de matriculas o quizá baste con encontrar la diferencia entre los meses de matricula.

    Por ejemplo, de la lista que se presenta a continuación se busca a todos los alumnos que como mínimo se hayan matriculado 4 veces en meses consecutivos, según los datos de prueba sólo el alumno 'A' cumple con la condición:

    DECLARE @Matricula table (Alumno varchar(100), Fecha date)
    INSERT INTO @Matricula VALUES
        ('A', '20170101'), ('A', '20170201'), ('A', '20170301'), ('A', '20170401'), ('A', '20170501'),
        ('B', '20170101'), ('B', '20170401'), ('B', '20170501'),
        ('C', '20170101'), ('C', '20170201'), ('C', '20170601'), ('C', '20170901'), ('C', '20171101');
    
    WITH T AS
    (
        SELECT 
    	   m.Alumno, m.Fecha,
    	   DATEDIFF(MONTH, m.Fecha, LAG(m.Fecha, 1, DATEADD(MONTH, 1, m.Fecha)) 
    		  OVER(PARTITION BY m.Alumno ORDER BY m.Fecha DESC)) AS [Diff]
        FROM
    	   @Matricula m
    )
    SELECT t1.Alumno FROM T t1 
    GROUP BY t1.Alumno HAVING (COUNT(*) >= 4) AND (COUNT(*) = SUM(t1.Diff));
    GO



    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    • Propuesto como respuesta Joyce_ACModerator miércoles, 12 de abril de 2017 16:38
    • Marcado como respuesta Carlos Sk miércoles, 12 de abril de 2017 21:15
    miércoles, 12 de abril de 2017 16:12
  • Hola Willams,

    Una consulta lo de LAG es para SQL Server 2012? Tengo la versión SQL Server 2008 R2

    Saludos

    miércoles, 12 de abril de 2017 16:38
  • Hola Carlos,

    Ese problema se conoce como "Encontrar Islas (finding islands)" y puedes leer mas sobre este en el ultimo libro de Itzik Ben-Gan (T-SQL Querying).

    Una forma de resolver este problema es enumerando las filas y restar ese valor a la fecha (en este caso como meses). Las filas consecutivas caeran en el mismo mes/año el cual serviria como identificador de grupo.

    El resto seria agrupar por alumno y el identificador mencionado para contar filas y filtrar usando la clausula HAVING.

    Esta solucion trabaja desde la version 2005.

    Ejemplo:

    DECLARE @Matricula table (Alumno varchar(100), Fecha date)
    INSERT INTO @Matricula VALUES
        ('A', '20170101'), ('A', '20170201'), ('A', '20170301'), ('A', '20170401'), ('A', '20170501'),
        ('B', '20170101'), ('B', '20170401'), ('B', '20170501'),
        ('C', '20170101'), ('C', '20170201'), ('C', '20170601'), ('C', '20170901'), ('C', '20171101');
    
    -- mostrar la idea
    WITH R AS (
    SELECT
    	Alumno,
        Fecha,
    	CONVERT(char(6), DATEADD(MONTH, - ROW_NUMBER() OVER(PARTITION BY Alumno ORDER BY Fecha), Fecha), 112) AS grp_helper
    FROM
    	@Matricula
    )
    SELECT
    	R.Alumno,
    	R.grp_helper,
    	R.Fecha
    FROM
    	R
    ORDER BY
    	R.Alumno,
    	R.grp_helper,
    	R.Fecha;
    
    -- solucion
    WITH R AS (
    SELECT
    	Alumno,
        Fecha,
    	CONVERT(char(6), DATEADD(MONTH, - ROW_NUMBER() OVER(PARTITION BY Alumno ORDER BY Fecha), Fecha), 112) AS grp_helper
    FROM
    	@Matricula
    )
    SELECT
    	R.Alumno
    FROM
    	R
    GROUP BY
    	R.Alumno,
    	R.grp_helper
    HAVING
    	COUNT(*) >= 4;
    GO


    Gracias a Willams por proveer data de ejemplo, que es lo que esperamos se adjunte cuando se hace una pregunta de este tipo.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas







    • Editado HunchbackMVP miércoles, 12 de abril de 2017 20:23
    • Marcado como respuesta Carlos Sk miércoles, 12 de abril de 2017 21:14
    miércoles, 12 de abril de 2017 19:09
  • Muchas gracias Hunchback igualmente probaré en la versión 2012 lo propuesto por Willams.
    miércoles, 12 de abril de 2017 21:15