Restituire l'ultimo prezzo di un articolo

USE tempdb;

CREATE TABLE dbo.M_Testata_Acquisti(
RELDoc int NOT NULL,
Data datetime NULL,
CONSTRAINT PK_TestataMag PRIMARY KEY(RELDoc)
);

CREATE TABLE dbo.M_Dettagli_Acquisti(
RELDettagli_Acquisti int IDENTITY NOT NULL,
RELArticolo int NULL,
RELDoc int NULL,
Prezzo money NULL,
CONSTRAINT PK_DettagliAcquisti PRIMARY KEY(RELDettagli_Acquisti),
CONSTRAINT FK_Dettagli_Testata FOREIGN KEY(RELDoc)
REFERENCES dbo.M_Testata_Acquisti(RELDoc)
);

INSERT dbo.M_Testata_Acquisti VALUES(1, '20070703');
INSERT dbo.M_Testata_Acquisti VALUES(2, '20070703');
INSERT dbo.M_Testata_Acquisti VALUES(3, '20070704');

INSERT dbo.M_Dettagli_Acquisti VALUES(3, 1, 2);
INSERT dbo.M_Dettagli_Acquisti VALUES(4, 1, 20);
INSERT dbo.M_Dettagli_Acquisti VALUES(3, 3, 2);
INSERT dbo.M_Dettagli_Acquisti VALUES(3, 3, 10);

WITH CTE_GetUltimiAcquisti AS
(
    SELECT RELArticolo, MAX(RELDettagli_Acquisti) AS RELDettagli_Acquisti
    FROM dbo.M_Dettagli_Acquisti
    GROUP BY RELArticolo
),
CTE_GetUltimiPrezzi AS
(
    SELECT UA.RELArticolo, D.RELDoc, D.Prezzo
    FROM CTE_GetUltimiAcquisti AS UA
    JOIN dbo.M_Dettagli_Acquisti AS D
    ON UA.RELDettagli_Acquisti = D.RELDettagli_Acquisti
)
SELECT T.Data, UP.RELArticolo, UP.Prezzo
FROM dbo.M_Testata_Acquisti AS T
JOIN CTE_GetUltimiPrezzi AS UP
ON T.RELDoc = UP.RELDoc;

/* Output:

Data                    RELArticolo Prezzo
----------------------- ----------- ---------------------
2007-07-04 00:00:00.000 3           10,00
2007-07-03 00:00:00.000 4           20,00

(2 row(s) affected)

*/

DROP TABLE dbo.M_Dettagli_Acquisti, dbo.M_Testata_Acquisti;