Aiuto!!! Sviluppo Query SQL

USE tempdb;

CREATE TABLE dbo.Dati(
id int NOT NULL,
codice char(4) NOT NULL,
data smalldatetime NOT NULL,
valore int NOT NULL
);

INSERT dbo.Dati VALUES(1, '0003', '20070120 13:40', -100);
INSERT dbo.Dati VALUES(1, '0003', '20070120 12:40', 70);
INSERT dbo.Dati VALUES(1, '0003', '20070120 11:40', 75);
INSERT dbo.Dati VALUES(2, '0005', '20070120 13:40', 10);
INSERT dbo.Dati VALUES(2, '0005', '20070120 12:40', -100);
INSERT dbo.Dati VALUES(2, '0005', '20070120 11:40', 54);
INSERT dbo.Dati VALUES(3, '0007', '20070120 13:40', -100);
INSERT dbo.Dati VALUES(3, '0007', '20070120 12:40', 154);
INSERT dbo.Dati VALUES(3, '0007', '20070120 11:40', 57);
INSERT dbo.Dati VALUES(4, '0010', '20070120 13:40', 54);
INSERT dbo.Dati VALUES(4, '0010', '20070120 12:40', 4545);
INSERT dbo.Dati VALUES(4, '0010', '20070120 11:40', 54);
INSERT dbo.Dati VALUES(4, '0010', '20070120 10:40', 54);
INSERT dbo.Dati VALUES(5, '0011', '20070120 13:40', 2);
INSERT dbo.Dati VALUES(6, '0012', '20070120 13:40', -100);
INSERT dbo.Dati VALUES(6, '0012', '20070120 12:40', 59);
INSERT dbo.Dati VALUES(7, '0013', '20070120 13:40', 54);
INSERT dbo.Dati VALUES(7, '0013', '20070120 12:40', 40);

/* Soluzione per SQL Server 2005 basata su una CTE */
WITH CTE_GetKey AS
(
    SELECT
          id
        , codice
        , MAX(data) AS data
    FROM dbo.Dati
    WHERE valore <> -100
    GROUP BY id, codice
)
SELECT D.*
FROM dbo.Dati AS D
JOIN CTE_GetKey AS C
ON D.id = C.id
AND D.codice = C.codice
AND D.data = C.data;

/* Soluzione generica basata su una tabella derivata */
SELECT D.*
FROM dbo.Dati AS D
JOIN (
    SELECT
          id
        , codice
        , MAX(data) AS data
    FROM dbo.Dati
    WHERE valore <> -100
    GROUP BY id, codice
) AS C
ON D.id = C.id
AND D.codice = C.codice
AND D.data = C.data;

/* Output:

id          codice data                    valore
----------- ------ ----------------------- -----------
1           0003   2007-01-20 12:40:00     70
2           0005   2007-01-20 13:40:00     10
3           0007   2007-01-20 12:40:00     154
4           0010   2007-01-20 13:40:00     54
5           0011   2007-01-20 13:40:00     2
6           0012   2007-01-20 12:40:00     59
7           0013   2007-01-20 13:40:00     54

(7 row(s) affected)

*/

DROP TABLE dbo.Dati;

SELECT D.*
FROM Dati AS D
JOIN (
    SELECT
          id
        , codice
        , MAX(data) AS dataUltima
    FROM Dati
    WHERE valore <> -100
    GROUP BY id, codice
) AS C
ON D.id = C.id
AND D.codice = C.codice
AND D.data = C.dataUltima;