Welcome to Sign in | Join | Help

Ultimele 2 randuri in coloane

  •  06-26-2012, 10:51 AM

    Ultimele 2 randuri in coloane

    Salut
    Incerc sa extrag ultimele 2 randuri dintr-un tabel grupate pe un camp iar din cele 2 randuri sa iau o coloana
    Am reusit ceva de genul

    WITH Temp (RANK, CNP, Data, Suma)
    AS
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY CNP ORDER BY f.pkey DESC) AS 'Rank',
               f.cnp,
               f.Data,
               f.Suma
        FROM   Facturi f
    )
    SELECT f1.cnp,
           f1.Data,
           f1.Suma AS actual,
           f2.Suma AS penultimul
    FROM   Temp f1,
           Temp f2
    WHERE  f1.Rank = f2.Rank - 1
           AND f1.CNP = f2.cnp
           AND f1.rank = 1
    UNION ALL
    SELECT f1.cnp,
           MIN(f1.Data) AS Data,
           SUM(f1.Suma)  AS actual,
           0 AS penultimul
    FROM   Temp f1
    GROUP BY
           cnp
    HAVING COUNT(f1.CNP) = 1

    dar am impresia ca este cam complicat, mai ales daca va trebui sa folosesc mai multe coloane.
    Aveti vreo idee mai simpla ?

     

    Sorin Sandu
View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems