none
Consultar información por meses separados SQL Server RRS feed

  • Pregunta

  • Hola buen día, estoy tratando de buscar una información por meses hasta el momento lo realizo con la funcion SplitString pero es muy tardado, el parámetro @fecha lo mando de esta forma (012017,032017,052017) para buscar (enero, marzo, mayo), en otra pregunta que realice me comentaron que con una tabla temporal pero aun no logro realizarlo, podrian apoyarme con algun ejemplo u otra sugerencia gracias..

    ALTER FUNCTION [dbo].[SplitString]
    (    
          @Input NVARCHAR(MAX),
          @Character CHAR(1)
    )
    RETURNS @Output TABLE (
          Item NVARCHAR(1000)
    )
    AS
    BEGIN
          DECLARE @StartIndex INT, @EndIndex INT
     
          SET @StartIndex = 1
          IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
          BEGIN
                SET @Input = @Input + @Character
          END
     
          WHILE CHARINDEX(@Character, @Input) > 0
          BEGIN
                SET @EndIndex = CHARINDEX(@Character, @Input)
               
                INSERT INTO @Output(Item)
                SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
               
                SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
          END
     
          RETURN
    END

    SELECT a.sFolio, C.sNombre, C.sClave, C.sEstatus, sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)) as NPInicio, (count(*)- (COUNT(case when a.sRegimen like'B1%' then 1 else null end) + COUNT(case when a.sRegimen like'B%' then 1 else null end)))/COUNT(DISTINCT B.nMes) as EEE, (COUNT(case when a.sRegimen like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end))

    /COUNT(DISTINCT B.nMes) as NPEET, COUNT(*)/COUNT(DISTINCT B.nMes) as NPEEECONRYT, (sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)))+ COUNT(*)/COUNT(DISTINCT B.nMes)- COUNT(case when b.nMes=convert(varchar, month(a.dfechapago))

    and b.nAnio=convert(varchar, year(a.dfechapago)) then 1 else null end) - (COUNT(case when a.sRegimen like'B1%' then 1 else null end)+

    COUNT(case when a.sRegimen like'B%' then 1 else null end))/COUNT(DISTINCT B.nMes) AS FACTURA from Reporte.dbo.RegEEEP a JOIN Reporte.dbo.RegEduc b ON a.sFolio= b.sFolio AND convert(varchar, month(a.dfechapago))

    +convert(varchar, year(a.dfechapago))IN (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ','))

    and (convert(varchar, nMes)+convert(varchar,nAnio) IN (SELECT CAST(Item AS integer) FROM dbo.SplitString(@Fechas, ',')) ) LEFT JOIN Reporte.dbo.Clientes c ON A.sFolio = c.sFolio where ((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null) or

    (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre LIKE '%' + @Buscar+ '%')) group by a.sFolio, c.sNombre, c.sEstatus, c.sClave




    • Editado D. Velazquez miércoles, 23 de agosto de 2017 15:26
    miércoles, 23 de agosto de 2017 15:24

Respuestas

  • Lo primero que te recomiendo es cambiar la forma como separas una cadena de caracteres en partes según un delimitador, lo segundo es que recuperes una sola vez las partes y no por cada vez que lo requieras, por ejemplo:

    - Función con valores de tabla

    CREATE FUNCTION [dbo].[fnSplit]
    (
    	 @Texto nvarchar(4000),
    	 @Delimitador nvarchar(10)
    )
    RETURNS @T table (id int IDENTITY(1, 1), Palabra nvarchar(4000))
    AS
    BEGIN
        DECLARE @xml xml
        SET @xml = N'<root><r>' + REPLACE(@Texto, @Delimitador,'</r><r>') + '</r></root>'
    
        INSERT INTO @T(Palabra)
        SELECT
    	   v.value('.','varchar(max)')
        FROM 
    	   @xml.nodes('//root/r') AS Valor(v)
        
        RETURN
    END
    GO

    - Consulta sql

    DECLARE @Fechas varchar(100) = '012017,032017,052017';
    WITH Fechas AS
    (
        SELECT Palabra AS Fecha FROM dbo.fnSplit(@Fechas, ',')
    )
    SELECT 
        --<Tu lista de selección>
    FROM 
        Reporte.dbo.RegEEEP a
        INNER JOIN Reporte.dbo.RegEduc b ON a.sFolio = b.sFolio 
    	   AND CONCAT(MONTH(a.dfechapago), YEAR(a.dfechapago)) IN (SELECT Fecha FROM Fechas) 
    	   AND CONCAT(nMes, nAnio) IN (SELECT Fecha FROM Fechas)	   
        LEFT JOIN Reporte.dbo.Clientes c ON a.sFolio = c.sFolio 
    WHERE
        --<Tus expresiones de filtro>



    Nuestra profesión exige tener pasión por resolver problemas de una manera óptima y eficiente.
    • Marcado como respuesta D. Velazquez miércoles, 23 de agosto de 2017 20:18
    miércoles, 23 de agosto de 2017 16:14
  • Que es lo que no puedes realizar?

    El problema no esta en como desmembras la lista (es una lista corta), sino en que no hay estadisticas de cuantos elementos hay en ella que el optimizador pueda usar (funcion tipo tabla multilineas).

    Tambien te comente que manipular una columna en un filtro o union no es buena practica pues tambien evita que el optimizador pueda usar estadisticas de indices existentes.

    Usando tu funcion podemos hacer lo sgte:

    DECLARE @T table (
    anio smallint NOT NULL,
    mes tinyint NOT NULL,
    bofm date NOT NULL,
    eofm date NOT NULL,
    PRIMARY KEY (anio, mes),
    UNIQUE (bofm, eofm),
    UNIQUE (eofm, bofm)
    );
    
    INSERT INTO @T (anio, mes, bofm, eofm)
    SELECT
        RIGHT(Item, 4) AS anio,
        LEFT(Item, 2) AS mes,
        CAST(RIGHT(Item, 4) + LEFT(Item, 2) + '01' AS date) AS bofm,
        DATEADD(MONTH, DATEDIFF(MONTH, '18990101', CAST(RIGHT(Item, 4) + LEFT(Item, 2) + '01' AS date)), '18990101') AS eofm
    FROM
        dbo.SplitString(N'012017,032017,052017', ',') AS T;
    
    SELECT * FROM @T;
    GO

    Una vez que tengas esta variable tipo tabla entonces puedes usar:

    SELECT
    	a.sFolio, C.sNombre, C.sClave, C.sEstatus,  sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)) as NPInicio, 
    	(count(*)- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end) + COUNT(case when a.sRegimen like'B%' then 1 else null end)))/COUNT(DISTINCT B.nMes) as EEE,
    	
    	(COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end))
    	/COUNT(DISTINCT B.nMes) as NPEET, 
    	COUNT(*)/COUNT(DISTINCT B.nMes) as NPEEECONRYT,
    	(sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)))+ COUNT(*)/COUNT(DISTINCT B.nMes)-
    	COUNT(case when b.nMes=convert(varchar, month(a.dfechapago))
    	and b.nAnio=convert(varchar, year(a.dfechapago)) then 1 else null end)
    	- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ 
    	COUNT(case when a.sRegimen like'B%' then 1 else null end))/COUNT(DISTINCT B.nMes) AS FACTURA
    FROM
    	Reporte.dbo.RegEEEP a
    	JOIN 
    	Reporte.dbo.RegEduc b 
    	ON a.sFolio= b.sFolio 
    	AND EXISTS (SELECT * FROM @T AS T WHERE CAST(a.dfechapago AS date) BETWEEN T.bofm AND T.eofm) 
    	AND EXISTS (SELECT * FROM @T AS T WHERE T.anio = b.nAnio AND T.mes = b.nMes)
    	LEFT JOIN
    	Reporte.dbo.Clientes c ON A.sFolio = c.sFolio 
    WHERE
    	((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null)
    	OR (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    GROUP BY
    	a.sFolio, c.sNombre, c.sEstatus, c.sClave
    GO

    Asegurate de que el tipo de dato de las columnas anio y mes en la variable tipo tabla sean compatible con las columnas nAnio y nMes en la tabla Reporte.dbo.RegEduc.

    A proposito, el filtro que tienes en la clausula WHERE sobre la tabla Reporte.dbo.Clientes cambia por completo lo que tratas de hacer (LEFT JOIN) cambiando el LEFT JOIN a un INNER JOIN.  El filtro sobre dicha tabla debera formar parte de la condicion de union.

    SELECT
    	a.sFolio, C.sNombre, C.sClave, C.sEstatus,  sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)) as NPInicio, 
    	(count(*)- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end) + COUNT(case when a.sRegimen like'B%' then 1 else null end)))/COUNT(DISTINCT B.nMes) as EEE,
    	
    	(COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end))
    	/COUNT(DISTINCT B.nMes) as NPEET, 
    	COUNT(*)/COUNT(DISTINCT B.nMes) as NPEEECONRYT,
    	(sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)))+ COUNT(*)/COUNT(DISTINCT B.nMes)-
    	COUNT(case when b.nMes=convert(varchar, month(a.dfechapago))
    	and b.nAnio=convert(varchar, year(a.dfechapago)) then 1 else null end)
    	- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ 
    	COUNT(case when a.sRegimen like'B%' then 1 else null end))/COUNT(DISTINCT B.nMes) AS FACTURA
    FROM
    	Reporte.dbo.RegEEEP a
    	JOIN 
    	Reporte.dbo.RegEduc b 
    	ON a.sFolio= b.sFolio 
    	LEFT JOIN
    	Reporte.dbo.Clientes c 
    	ON A.sFolio = c.sFolio
    	AND (
    	((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null)
    	OR (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    	)
    WHERE
    	EXISTS (SELECT * FROM @T AS T WHERE CAST(a.dfechapago AS date) BETWEEN T.bofm AND T.eofm) 
    	AND EXISTS (SELECT * FROM @T AS T WHERE T.anio = b.nAnio AND T.mes = b.nMes)
    GROUP BY
    	a.sFolio, c.sNombre, c.sEstatus, c.sClave
    GO


    AMB

    Some guidelines for posting questions...

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

    • Editado HunchbackMVP miércoles, 23 de agosto de 2017 18:18
    • Marcado como respuesta D. Velazquez miércoles, 23 de agosto de 2017 20:18
    miércoles, 23 de agosto de 2017 17:49

Todas las respuestas

  • Lo primero que te recomiendo es cambiar la forma como separas una cadena de caracteres en partes según un delimitador, lo segundo es que recuperes una sola vez las partes y no por cada vez que lo requieras, por ejemplo:

    - Función con valores de tabla

    CREATE FUNCTION [dbo].[fnSplit]
    (
    	 @Texto nvarchar(4000),
    	 @Delimitador nvarchar(10)
    )
    RETURNS @T table (id int IDENTITY(1, 1), Palabra nvarchar(4000))
    AS
    BEGIN
        DECLARE @xml xml
        SET @xml = N'<root><r>' + REPLACE(@Texto, @Delimitador,'</r><r>') + '</r></root>'
    
        INSERT INTO @T(Palabra)
        SELECT
    	   v.value('.','varchar(max)')
        FROM 
    	   @xml.nodes('//root/r') AS Valor(v)
        
        RETURN
    END
    GO

    - Consulta sql

    DECLARE @Fechas varchar(100) = '012017,032017,052017';
    WITH Fechas AS
    (
        SELECT Palabra AS Fecha FROM dbo.fnSplit(@Fechas, ',')
    )
    SELECT 
        --<Tu lista de selección>
    FROM 
        Reporte.dbo.RegEEEP a
        INNER JOIN Reporte.dbo.RegEduc b ON a.sFolio = b.sFolio 
    	   AND CONCAT(MONTH(a.dfechapago), YEAR(a.dfechapago)) IN (SELECT Fecha FROM Fechas) 
    	   AND CONCAT(nMes, nAnio) IN (SELECT Fecha FROM Fechas)	   
        LEFT JOIN Reporte.dbo.Clientes c ON a.sFolio = c.sFolio 
    WHERE
        --<Tus expresiones de filtro>



    Nuestra profesión exige tener pasión por resolver problemas de una manera óptima y eficiente.
    • Marcado como respuesta D. Velazquez miércoles, 23 de agosto de 2017 20:18
    miércoles, 23 de agosto de 2017 16:14
  • Que es lo que no puedes realizar?

    El problema no esta en como desmembras la lista (es una lista corta), sino en que no hay estadisticas de cuantos elementos hay en ella que el optimizador pueda usar (funcion tipo tabla multilineas).

    Tambien te comente que manipular una columna en un filtro o union no es buena practica pues tambien evita que el optimizador pueda usar estadisticas de indices existentes.

    Usando tu funcion podemos hacer lo sgte:

    DECLARE @T table (
    anio smallint NOT NULL,
    mes tinyint NOT NULL,
    bofm date NOT NULL,
    eofm date NOT NULL,
    PRIMARY KEY (anio, mes),
    UNIQUE (bofm, eofm),
    UNIQUE (eofm, bofm)
    );
    
    INSERT INTO @T (anio, mes, bofm, eofm)
    SELECT
        RIGHT(Item, 4) AS anio,
        LEFT(Item, 2) AS mes,
        CAST(RIGHT(Item, 4) + LEFT(Item, 2) + '01' AS date) AS bofm,
        DATEADD(MONTH, DATEDIFF(MONTH, '18990101', CAST(RIGHT(Item, 4) + LEFT(Item, 2) + '01' AS date)), '18990101') AS eofm
    FROM
        dbo.SplitString(N'012017,032017,052017', ',') AS T;
    
    SELECT * FROM @T;
    GO

    Una vez que tengas esta variable tipo tabla entonces puedes usar:

    SELECT
    	a.sFolio, C.sNombre, C.sClave, C.sEstatus,  sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)) as NPInicio, 
    	(count(*)- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end) + COUNT(case when a.sRegimen like'B%' then 1 else null end)))/COUNT(DISTINCT B.nMes) as EEE,
    	
    	(COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end))
    	/COUNT(DISTINCT B.nMes) as NPEET, 
    	COUNT(*)/COUNT(DISTINCT B.nMes) as NPEEECONRYT,
    	(sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)))+ COUNT(*)/COUNT(DISTINCT B.nMes)-
    	COUNT(case when b.nMes=convert(varchar, month(a.dfechapago))
    	and b.nAnio=convert(varchar, year(a.dfechapago)) then 1 else null end)
    	- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ 
    	COUNT(case when a.sRegimen like'B%' then 1 else null end))/COUNT(DISTINCT B.nMes) AS FACTURA
    FROM
    	Reporte.dbo.RegEEEP a
    	JOIN 
    	Reporte.dbo.RegEduc b 
    	ON a.sFolio= b.sFolio 
    	AND EXISTS (SELECT * FROM @T AS T WHERE CAST(a.dfechapago AS date) BETWEEN T.bofm AND T.eofm) 
    	AND EXISTS (SELECT * FROM @T AS T WHERE T.anio = b.nAnio AND T.mes = b.nMes)
    	LEFT JOIN
    	Reporte.dbo.Clientes c ON A.sFolio = c.sFolio 
    WHERE
    	((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null)
    	OR (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    GROUP BY
    	a.sFolio, c.sNombre, c.sEstatus, c.sClave
    GO

    Asegurate de que el tipo de dato de las columnas anio y mes en la variable tipo tabla sean compatible con las columnas nAnio y nMes en la tabla Reporte.dbo.RegEduc.

    A proposito, el filtro que tienes en la clausula WHERE sobre la tabla Reporte.dbo.Clientes cambia por completo lo que tratas de hacer (LEFT JOIN) cambiando el LEFT JOIN a un INNER JOIN.  El filtro sobre dicha tabla debera formar parte de la condicion de union.

    SELECT
    	a.sFolio, C.sNombre, C.sClave, C.sEstatus,  sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)) as NPInicio, 
    	(count(*)- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end) + COUNT(case when a.sRegimen like'B%' then 1 else null end)))/COUNT(DISTINCT B.nMes) as EEE,
    	
    	(COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ COUNT(case when a.sRegimen like'B%' then 1 else null end))
    	/COUNT(DISTINCT B.nMes) as NPEET, 
    	COUNT(*)/COUNT(DISTINCT B.nMes) as NPEEECONRYT,
    	(sum(b.aNPInicio)/ (count(a.sFolio)/count(distinct b.nMes)))+ COUNT(*)/COUNT(DISTINCT B.nMes)-
    	COUNT(case when b.nMes=convert(varchar, month(a.dfechapago))
    	and b.nAnio=convert(varchar, year(a.dfechapago)) then 1 else null end)
    	- (COUNT(case when a.sRegimen  like'B1%' then 1 else null end)+ 
    	COUNT(case when a.sRegimen like'B%' then 1 else null end))/COUNT(DISTINCT B.nMes) AS FACTURA
    FROM
    	Reporte.dbo.RegEEEP a
    	JOIN 
    	Reporte.dbo.RegEduc b 
    	ON a.sFolio= b.sFolio 
    	LEFT JOIN
    	Reporte.dbo.Clientes c 
    	ON A.sFolio = c.sFolio
    	AND (
    	((@Estatus is null) or (c.sEstatus = @Estatus)) and ((@Buscar is null)
    	OR (C.sFolio LIKE '%' + @Buscar+ '%' OR c.sNombre  LIKE '%' + @Buscar+ '%'))
    	)
    WHERE
    	EXISTS (SELECT * FROM @T AS T WHERE CAST(a.dfechapago AS date) BETWEEN T.bofm AND T.eofm) 
    	AND EXISTS (SELECT * FROM @T AS T WHERE T.anio = b.nAnio AND T.mes = b.nMes)
    GROUP BY
    	a.sFolio, c.sNombre, c.sEstatus, c.sClave
    GO


    AMB

    Some guidelines for posting questions...

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

    • Editado HunchbackMVP miércoles, 23 de agosto de 2017 18:18
    • Marcado como respuesta D. Velazquez miércoles, 23 de agosto de 2017 20:18
    miércoles, 23 de agosto de 2017 17:49
  • > Lo primero que te recomiendo es cambiar la forma como separas una cadena de caracteres
    > en partes según un delimitador

    La funcion que recomiendas todavia es una funcion tipo tabla multi sentencias.  Preferible si se usa una funcion tipo tabla en linea.  Ademas, cuando se usa xml debera tenerse en cuenta que la lista puede contener caracteres no validos para un documento xml.

    SELECT
        *
    FROM
        [dbo].[fnSplit](N'A&B,LLC', ',') AS T;
    GO
    
    /*
    
    Msg 9411, Level 16, State 1, Line 21
    XML parsing: line 1, character 13, semicolon expected
    
    */

    Aca dejo una version tipo tabla en linea y que toma en cuenta esos caracteres especiales.  Aun asi el optimizador usa un estimado fijo.

    CREATE FUNCTION [dbo].[SplitString] (    
    @Input NVARCHAR(MAX),
    @Character NCHAR(1)
    )
    RETURNS table
    AS
    RETURN (
    SELECT
        ROW_NUMBER() OVER(ORDER BY T3.col1) AS Item_pos,
        T4.col1 AS Item
    FROM
        (SELECT (SELECT @Input AS [*] FOR XML PATH('')) AS col1) T0
        CROSS APPLY
        (SELECT '<x>'+REPLACE(T0.col1,',','</x><x>')+'</x>' AS col1) T1
        CROSS APPLY
        (SELECT CAST(T1.col1 AS xml) AS col1) T2
        CROSS APPLY
        T2.col1.nodes('x') T3(col1)
        CROSS APPLY
        (SELECT T3.col1.value('text()[1]','nvarchar(4000)') AS col1) T4
    );
    GO
    SELECT
        *
    FROM
        [dbo].[SplitString](N'A&B,LLC', N',') AS T;
    GO
    
    /*
    
    Item_pos	Item
    1	        A&B
    2	        LLC
    
    */

    > lo segundo es que recuperes una sola vez las partes y no por cada vez que lo requieras

    Si usamos una CTE entonces la funcion sera llamada tantas veces como referencies la CTE.  La forma de evadir eso seria materializando el resultado de la llamada a la funcion usando una tabla o una variable tipo tabla segun la cardinalidad.


    AMB

    Some guidelines for posting questions...

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


    • Editado HunchbackMVP miércoles, 23 de agosto de 2017 20:04
    miércoles, 23 de agosto de 2017 20:03
  • Gracias a ambos por sus respuestas, pude mejorar el tiempo de respuesta de mi procedimiento almacenado con ambas formas, agradezco su tiempo y la explicación que me brindaron.
    miércoles, 23 de agosto de 2017 20:24