Query complessa

USE tempdb;
GO

/* Definisco le tabelle */
CREATE TABLE dbo.Tabella1(
id int NOT NULL IDENTITY PRIMARY KEY,
id_cod int NOT NULL, 
nome char(3) NOT NULL,
data smalldatetime NOT NULL,
prezzo_id int NOT NULL
);
GO

CREATE TABLE dbo.Tabella2(
id_lista int NOT NULL IDENTITY PRIMARY KEY,
prezzo_id int NOT NULL,
categoria char(4) NOT NULL,
prezzo decimal(4,2) NOT NULL
);
GO

/* Le popolo */
INSERT dbo.Tabella1 VALUES(2, 'bo1', '20031010', 1);
INSERT dbo.Tabella1 VALUES(2, 'bo2', '20031012', 1);
INSERT dbo.Tabella1 VALUES(2, 'bo3', '20031013', 1);
INSERT dbo.Tabella1 VALUES(3, 'bo4', '20031018', 2);
INSERT dbo.Tabella1 VALUES(3, 'bo5', '20031016', 2);
INSERT dbo.Tabella1 VALUES(3, 'bo6', '20031019', 2);

INSERT dbo.Tabella2 VALUES(1, 'bo a', 10.50);
INSERT dbo.Tabella2 VALUES(3, 'bo s', 17.50);
INSERT dbo.Tabella2 VALUES(2, 'bo d', 12.50);
GO

/* Query */
SELECT 
    Q2.nome
  , CONVERT(varchar, Q2.data, 103) AS data
  , T2.prezzo
  , T2.categoria
FROM dbo.Tabella2 AS T2
JOIN (
  SELECT T1.Nome, Q1.*
  FROM dbo.Tabella1 AS T1
  JOIN (
    SELECT id_cod, prezzo_id, MAX(data) AS data
    FROM dbo.Tabella1
    GROUP BY id_cod, prezzo_id
  ) AS Q1
  ON T1.id_cod = Q1.id_cod
  AND T1.prezzo_id = Q1.prezzo_id
  AND T1.data = Q1.data
) AS Q2
ON T2.prezzo_id = Q2.prezzo_id
GO

/* Output:

nome data        prezzo  categoria
---- ----------- ------- ---------
bo3  13/10/2003  10.50   bo a
bo6  19/10/2003  12.50   bo d

(2 row(s) affected)

*/

/* Pulizia */
DROP TABLE dbo.Tabella1, dbo.Tabella2;

>PS GRAZIE

USE tempdb;
GO

/* Definisco le tabelle */
CREATE TABLE dbo.articoli(
id_cod int NOT NULL PRIMARY KEY,
nome char(3) NOT NULL
);
GO

CREATE TABLE dbo.listin(
id_lista int IDENTITY(1, 1) NOT NULL,
listino int NOT NULL,
data_agg datetime NOT NULL,
prezzo money NOT NULL,
li_cod int NOT NULL,
CONSTRAINT FK_listin_articoli FOREIGN KEY(li_cod)
REFERENCES dbo.articoli(id_cod)
);
GO

/* Le popolo */
INSERT dbo.articoli VALUES(1, 'bo1');
INSERT dbo.articoli VALUES(2, 'bo2');
INSERT dbo.articoli VALUES(3, 'bo3');

INSERT dbo.listin VALUES(1, '2006-04-28 00:00:00.000', 12.2300, '1');
INSERT dbo.listin VALUES(1, '2006-04-22 00:00:00.000', 35.5600, '1');
INSERT dbo.listin VALUES(1, '2005-04-23 00:00:00.000', 56.0000, '2');
INSERT dbo.listin VALUES(1, '2006-02-22 00:00:00.000', 10.2300, '2');
GO

/* Query */
SELECT 
    A.id_cod
  , A.nome
  , Q2.prezzo
  , CONVERT(varchar, Q2.data_agg, 103) AS data_agg
FROM dbo.articoli AS A
JOIN (
  SELECT Q.*, L.prezzo
  FROM (
    SELECT li_cod, MAX(data_agg) AS data_agg
    FROM dbo.listin
    GROUP BY li_cod
  ) AS Q
  JOIN dbo.listin AS L
  ON Q.li_cod = L.li_cod
  AND Q.data_agg = L.data_agg
) AS Q2
ON A.id_cod = Q2.li_cod
ORDER BY A.id_cod;
GO

/* Output:

id_cod      nome prezzo                data_agg
----------- ---- --------------------- -----------
1           bo1  12,23                 28/04/2006
2           bo2  10,23                 22/02/2006

(2 row(s) affected)

*/

/* Pulizia */
DROP TABLE dbo.listin, dbo.articoli;

>ciao e grazie

SELECT li_cod, MAX(data_agg) AS data_agg
FROM dbo.listin
WHERE listino = 1
GROUP BY li_cod

>ciao e grazie devo dire che sei potente