none
Consulta SQL RRS feed

  • Pregunta

  • Hola Buenos dias.. Tengo la siguiente consulta, que viene de una funcion previamente creada..

    SELECT [Fecha Orig.], [Fecha Pago], [Fecha Ant.], [Dias],
    Convert(decimal(10,2),IIF(ROW_NUMBER() OVER (ORDER BY [idCuentaPago])=1,[Deuda Inicial],
    LAG(Saldo) OVER (ORDER BY [idCuentaPago]))) 'Deuda Inicial',
     Convert(decimal(10,2),IIF(ROW_NUMBER() OVER (ORDER BY [idCuentaPago])=1,[Deuda Inicial] + [dbo].[prueba].[Interes] - [Pago],
    LAG(Saldo) OVER (ORDER BY [idCuentaPago]) + (LAG(Saldo) OVER (ORDER BY [idCuentaPago])*(([Dias]  * e.InteresDiario) /100) ) - [Pago])) 'Saldo',

     Convert(decimal(10,2),IIF(ROW_NUMBER() OVER (ORDER BY [Fecha Pago])=1,[dbo].[prueba].[Interes],
    (LAG(Saldo) OVER (ORDER BY [idCuentaPago])*(([Dias]  * e.InteresDiario) /100) ))) 'Interes',
     [Pago], ROW_NUMBER() OVER (ORDER BY [idCuentaPago]) 'Orden', [idCuentaPago], [idCuentaDetalle]

    FROM prueba(23)

    INNER JOIN empresa e ON e.idEmpresa=e.idEmpresa

    Y me da los siguientes resultados:

    Fecha Orig.    Fecha Pago    Fecha Ant.    Dias    Deuda Inicial    Saldo    Interes    Pago    Orden
    2019-12-01 08:34:00    2020-01-11 18:55:00    NULL    41    2000.00    1718.00    328.00    610.00    1
    2019-12-01 08:34:00    2020-02-13 20:37:00    2020-01-11 18:55:00    33    0.00    -500.00    0.00    500.00    2
    2019-12-01 08:34:00    2020-07-17 11:56:00    2020-02-13 20:37:00    155    0.00    -200.00    0.00    200.00    3
    2019-12-01 08:34:00    2020-07-20 19:21:00    2020-07-17 11:56:00    3    0.00    -200.00    0.00    200.00    4

    Pero resulta que el LAG me la hace sobre la consulta de la funcion prueba y no sobre el select actual.

    Alguien sabe como puedo hacer para que el LAG me haga sobre el select que copie arriba?

    Saludos!

    martes, 21 de julio de 2020 13:23

Respuestas

  • Pero resulta que el LAG me la hace sobre la consulta de la funcion prueba y no sobre el select actual.

    No entiendo. ¿Como que "sobre el select actual"? Solo existe un único select en la query que has escrito, y el LAG necesariamente tiene que operar sobre ese Select. No hay ninguna otra cosa sobre la que pueda operar. La función no tiene nada que ver.

    Resumamos: la operativa es como sigue: Tienes una select así:

    select columnas from loquesea

    Ese "loquesea" puede ser cualquier cosa, tal como una tabla, una vista, una subquery, una CTE... o una función de tipo tabla. Pero a los efectos del LAG es irrelevante qué sea ese "loquesea". Lo importante es que devuelve registros que tienen columnas. El LAG va a operar sobre esos registros y esas columnas, con total independencia de qué sea "loquesea"; no tiene nada que ver el hecho de que se trate de una función, y el contenido interno de la función es invisible para el LAG.

    Sobre esas columnas que ha devuelto la función (y que constituyen el contenido de la Select actual), el LAG toma la columna que le digas y ordena por ella. Por ejemplo, si pones OVER (ORDER BY [Fecha Pago]), ordena TODOS los registros por el campo [Fecha Pago], y entonces toma el dato que le pides del registro anterior, donde "anterior" quiere decir el anterior según el orden por ese campo que has indicado en el order by. Todo esto lo hace sobre el select anterior, que además es el único select que existe y además coincide con el resultado de la función. Las tres cosas son la misma.

    • Marcado como respuesta Mauricio Hamak martes, 21 de julio de 2020 19:00
    martes, 21 de julio de 2020 18:49
  • No. O has copiado mal la consulta, o estás equivocado sobre lo que hace.

    Quitando la "morralla", la consulta es básicamente así:

    Select [varias cosas] from [LaFuncion] inner join [UnaTabla]

    Lo primero de todo se ejecuta la función, que devuelve uno o más registros y a los efectos de la select es como si esos registros fueran una tabla. Después a eso le aplica un join con la tabla, y el resultado es otro conjunto de registros. Entonces nos quedamos con esos registros y a partir de aquí es como si la función y la tabla no existiesen. Solo importan esos registros que han salido del join.

    Y ahora empezamos a hablar del LAG, que solo se aplica sobre esos registros resultantes. La función aquí ya no existe, no tiene nada que ver con la operativa del LAG.

    Antes de pensar en el LAG, lo primero examina esos registros que te salen del JOIN a ver qué es lo que tienen. Lo que no esté ahí es invisible para el LAG. Si esos registros todos contienen un cierto valor de Saldo, sea de donde sea que haya salido (la función o la tabla), el LAG solo ve ese valor. No puede sacar ninguna otra cosa de ningún otro sitio. Si querías sacar algo diferente, entonces tienes que cambiar la parte que hay detrás del from (la función, el join y la tabla). No puedes sacar ninguna otra cosa manipulando únicamente las columnas de la select, incluidos los LAG.

    • Marcado como respuesta Mauricio Hamak martes, 21 de julio de 2020 19:19
    martes, 21 de julio de 2020 19:17

Todas las respuestas

  • Pero resulta que el LAG me la hace sobre la consulta de la funcion prueba y no sobre el select actual.

    No entiendo. ¿Como que "sobre el select actual"? Solo existe un único select en la query que has escrito, y el LAG necesariamente tiene que operar sobre ese Select. No hay ninguna otra cosa sobre la que pueda operar. La función no tiene nada que ver.

    Resumamos: la operativa es como sigue: Tienes una select así:

    select columnas from loquesea

    Ese "loquesea" puede ser cualquier cosa, tal como una tabla, una vista, una subquery, una CTE... o una función de tipo tabla. Pero a los efectos del LAG es irrelevante qué sea ese "loquesea". Lo importante es que devuelve registros que tienen columnas. El LAG va a operar sobre esos registros y esas columnas, con total independencia de qué sea "loquesea"; no tiene nada que ver el hecho de que se trate de una función, y el contenido interno de la función es invisible para el LAG.

    Sobre esas columnas que ha devuelto la función (y que constituyen el contenido de la Select actual), el LAG toma la columna que le digas y ordena por ella. Por ejemplo, si pones OVER (ORDER BY [Fecha Pago]), ordena TODOS los registros por el campo [Fecha Pago], y entonces toma el dato que le pides del registro anterior, donde "anterior" quiere decir el anterior según el orden por ese campo que has indicado en el order by. Todo esto lo hace sobre el select anterior, que además es el único select que existe y además coincide con el resultado de la función. Las tres cosas son la misma.

    • Marcado como respuesta Mauricio Hamak martes, 21 de julio de 2020 19:00
    martes, 21 de julio de 2020 18:49
  • Hola Alberto! Yo de la select de arriba la trae de una funcion llamada "FROM prueba(23)", que a su vez tiene una columna [Deuda Inicial], yo de esa funcion solo necesito la primer fila y despues voy trabajando en la select de arriba a partir de ese dato! Lo que me sucede es que todos los datos de la columna "saldo" me trae de la funcion "FROM prueba(23)" y yo necesito que el LAG me la haga del select que esta arriba no de la funciona.

    Muchas gracias!!!

    martes, 21 de julio de 2020 19:01
  • No. O has copiado mal la consulta, o estás equivocado sobre lo que hace.

    Quitando la "morralla", la consulta es básicamente así:

    Select [varias cosas] from [LaFuncion] inner join [UnaTabla]

    Lo primero de todo se ejecuta la función, que devuelve uno o más registros y a los efectos de la select es como si esos registros fueran una tabla. Después a eso le aplica un join con la tabla, y el resultado es otro conjunto de registros. Entonces nos quedamos con esos registros y a partir de aquí es como si la función y la tabla no existiesen. Solo importan esos registros que han salido del join.

    Y ahora empezamos a hablar del LAG, que solo se aplica sobre esos registros resultantes. La función aquí ya no existe, no tiene nada que ver con la operativa del LAG.

    Antes de pensar en el LAG, lo primero examina esos registros que te salen del JOIN a ver qué es lo que tienen. Lo que no esté ahí es invisible para el LAG. Si esos registros todos contienen un cierto valor de Saldo, sea de donde sea que haya salido (la función o la tabla), el LAG solo ve ese valor. No puede sacar ninguna otra cosa de ningún otro sitio. Si querías sacar algo diferente, entonces tienes que cambiar la parte que hay detrás del from (la función, el join y la tabla). No puedes sacar ninguna otra cosa manipulando únicamente las columnas de la select, incluidos los LAG.

    • Marcado como respuesta Mauricio Hamak martes, 21 de julio de 2020 19:19
    martes, 21 de julio de 2020 19:17
  • Claro si, tengo esta consulta:

    WITH CTE AS (
        SELECT  ROW_NUMBER() OVER( ORDER BY ps.FECHA) as rn, *
        from cuentas_pagos as ps
        WHERE ps.idCuentaDetalle=23
    )

    (SELECT  d.Fecha 'Fecha Orig.', a.Fecha 'Fecha Pago', c.Fecha 'Fecha Ant.',
     IIF(ROW_NUMBER() OVER (ORDER BY a.idCuentadetalle)=1,
     DATEDIFF(day,d.Fecha, a.Fecha),
     DATEDIFF(day,c.Fecha, a.Fecha))
     'Dias',
      CONVERT(decimal(10,2),
      IIF(ROW_NUMBER() OVER (ORDER BY a.idCuentaPago)=1,
     d.Importe,
     IIF(ROW_NUMBER() OVER (ORDER BY a.idCuentadetalle)=2,
     LAG(d.Importe+ d.Importe *((DATEDIFF(day, d.Fecha,a.Fecha)  * e.InteresDiario) /100) -a.Importe) OVER (ORDER BY a.idCuentaPago)
     ,
      LAG(d.Importe+ d.Importe *((DATEDIFF(day, c.Fecha,a.Fecha)  * e.InteresDiario) /100) -a.Importe) OVER (ORDER BY a.idCuentaPago)

     

     ))) 'Deuda Inicial',
      CONVERT(decimal(10,2),IIF(ROW_NUMBER() OVER (ORDER BY a.idCuentaPago)=1,
    d.Importe+ d.Importe *((DATEDIFF(day, d.Fecha,a.Fecha)  * e.InteresDiario) /100) -a.Importe ,
    IIF(ROW_NUMBER() OVER (ORDER BY a.idCuentadetalle)=2,
     LAG(d.Importe+ d.Importe *((DATEDIFF(day, d.Fecha,a.Fecha)  * e.InteresDiario) /100) -a.Importe) OVER (ORDER BY a.idCuentaPago)
     + LAG(d.Importe+ d.Importe *((DATEDIFF(day, d.Fecha,a.Fecha)  * e.InteresDiario) /100) -a.Importe) OVER (ORDER BY a.idCuentaPago) *
     ((DATEDIFF(day, c.Fecha,a.Fecha)  * e.InteresDiario) /100)  - a.Importe
    ,2
    ))) 'Saldo',
    a.Importe

    FROM CTE a
    LEFT JOIN CTE b ON a.rn = b.rn -1
    LEFT JOIN CTE c ON a.rn = c.rn +1
    INNER JOIN cuentas_detalle d ON d.idCuentaDetalle=a.idCuentaDetalle
    INNER JOIN empresa e ON e.idEmpresa=e.idEmpresa
    INNER JOIN cuentas f ON f.idCuenta=a.idCuenta
    )


    y me da el siguiente resultado:

    Fecha Orig.    Fecha Pago    Fecha Ant.    Dias    Deuda Inicial    Saldo    Importe
    2019-12-01 08:34:00    2020-01-11 18:55:00    NULL    41    2000.00    1718.00    610.00
    2019-12-01 08:34:00    2020-02-13 20:37:00    2020-01-11 18:55:00    33    1718.00    1444.78    500.00
    2019-12-01 08:34:00    2020-07-17 11:56:00    2020-02-13 20:37:00    155    1764.00    2.00    200.00
    2019-12-01 08:34:00    2020-07-20 19:21:00    2020-07-17 11:56:00    3    3040.00    2.00    200.00

    La unica columna que me interesa el LAG de la fila anterior es saldo solamente, pero no locro hacerlo funcionar...

    martes, 21 de julio de 2020 19:30
  • O si no algo asi con una funcion adentro de un select:


    SELECT [Fecha Orig.], [Fecha Pago], [Fecha Ant.], [Dias],

    IIF(ROW_NUMBER() OVER (ORDER BY [Fecha Pago])=1,[Deuda Inicial],LAG(Saldo) OVER (ORDER BY idCuentaPago)) 'Deuda Inicial',

    [dbo].[prueba].[Interes],[Pago],
     Convert(decimal(10,2),
     IIF(ROW_NUMBER() OVER (ORDER BY [idCuentaPago])=1, [Deuda Inicial] + [dbo].[prueba].[Interes] - [Pago],
     [Deuda Inicial ] + [dbo].[prueba].[Interes] - [Pago]
     ))
    AS Saldo
     
    FROM prueba(23)
    INNER JOIN empresa e ON e.idEmpresa=e.idEmpresa

    Pero me da error por que dice que Saldo no existe cuando quiero hacer un LAG sobre "Saldo".

    Saludos!

    martes, 21 de julio de 2020 19:43
  • Pero me da error por que dice que Saldo no existe cuando quiero hacer un LAG sobre "Saldo".

    No, lo que quieres hacer no está permitido. Fíjate que simplificando es esto:

    Select CiertaFuncion(Alias), CiertoCalculo as Alias from ...

    En resumen, un campo calculado tiene un alias, que en tu caso se lama "Saldo", y después intentas usar ese alias en otra columna, aplicando una función sobre ese alias. Esto no está permitido con ninguna función ni con ningún cálculo, SQL no permite que un alias de columna se utilice en otra columna. Esto no se permite no solo con el LAG sino tampoco con ninguna otra función; no es un problema específico del LAG.

    Resumiendo: para poder usar el LAG, aquello sobre lo que haces el lag tiene que estar definido detrás del FROM en la consulta, no puede ser un cálculo metido en la lista de columnas de la SELECT.

    Lo mismo sucedería con cualquier otra función, no es una limitación específica del LAG.

    martes, 21 de julio de 2020 20:04