Estrazione errata

USE tempdb;

CREATE TABLE dbo.Anagrafiche(
codcliente int NOT NULL PRIMARY KEY,
nomecliente varchar(10) NOT NULL,
rappresentante char(3) NOT NULL,
citta varchar(10) NOT NULL
);

CREATE TABLE dbo.Ordini(
idordine int NOT NULL PRIMARY KEY,
codcliente int NOT NULL,
data date NULL,
CONSTRAINT FK_Ordini_Anagrafiche FOREIGN KEY(codcliente)
REFERENCES dbo.Anagrafiche(codcliente)
);

INSERT dbo.Anagrafiche
VALUES (1, 'rossi', '004', 'milano')
     , (2, 'verdi', '004', 'milano')
     , (3, 'bianchi', '003', 'milano')
     , (4, 'gialli', '004', 'torino');

INSERT dbo.Ordini
VALUES (1000, 1, '20110506')
     , (1001, 1, '20110506')
     , (1002, 1, '20110506')
     , (1003, 2, NULL)
     , (1004, 2, NULL)
     , (1005, 3, '20110506')
     , (1006, 3, '20110506')
     , (1007, 4, '20110506');
GO

/* Soluzione per SQL Server */
CREATE PROCEDURE dbo.up_ClientiPerCittà
@rappresentante char(3)
AS
SELECT A.citta, COUNT(DISTINCT A.nomecliente) AS cliente
FROM dbo.Anagrafiche AS A
INNER JOIN dbo.Ordini AS O
ON A.codcliente = O.codcliente
WHERE A.rappresentante = @rappresentante
AND O.data IS NOT NULL
GROUP BY A.citta;
GO

EXEC dbo.up_ClientiPerCittà '004';

/* Output:

citta      cliente
---------- -----------
milano     1
torino     1

(2 row(s) affected)

*/

EXEC dbo.up_ClientiPerCittà '003';

/* Output:

citta      cliente
---------- -----------
milano     1

(1 row(s) affected)

*/


/* Soluzione compatibile con Access */
SELECT A.citta, COUNT(*) AS cliente
FROM Anagrafiche AS A
INNER JOIN (
    SELECT codcliente 
    FROM Ordini
    WHERE data IS NOT NULL
    GROUP BY codcliente
) AS O
ON A.codcliente = O.codcliente
WHERE A.rappresentante = '004'
GROUP BY A.citta;

/* Output:

citta      cliente
---------- -----------
milano     1
torino     1

(2 row(s) affected)

*/

SELECT A.citta, COUNT(*) AS cliente
FROM Anagrafiche AS A
INNER JOIN (
    SELECT codcliente 
    FROM Ordini
    WHERE data IS NOT NULL
    GROUP BY codcliente
) AS O
ON A.codcliente = O.codcliente
WHERE A.rappresentante = '003'
GROUP BY A.citta;

/* Output:

citta      cliente
---------- -----------
milano     1

(1 row(s) affected)

*/

DROP PROCEDURE dbo.up_ClientiPerCittà;
DROP TABLE dbo.Ordini, dbo.Anagrafiche;