>SELECT a.ANNO, a.MESE,
>sum (b.QTA)
>FROM QTA as B RIGHT OUTER JOIN
>MESI as A ON year (b.data_ad) = a.ANNO AND
>month (b.data_ad) = a.MESE
>group by year (b.data_adA) , month (b.data_ad), a.ANNO, a.MESE
Ciao Elena,
Sbagli il raggruppamento: è sufficiente specificare anno e mese della tabella Mesi.
USE tempdb;
CREATE TABLE dbo.Qta(
data_ad date NOT NULL,
codice int NOT NULL,
qta int NULL
);
INSERT dbo.Qta VALUES
('20090110', 1, 5)
, ('20090111', 2, 2)
, ('20090116', 3, 6)
, ('20090120', 2, 8)
, ('20090301', 5, 9)
, ('20090302', 5, 6)
, ('20090405', 1, 4)
, ('20090406', 1, 7)
, ('20080101', 1, 5)
, ('20080105', 5, 8)
, ('20060205', 3, 9)
, ('20080906', 2, 6)
, ('20080902', 1, 6)
, ('20081112', 2, 6);
CREATE TABLE dbo.Mesi(
anno int NOT NULL,
mese int NOT NULL
);
INSERT dbo.Mesi VALUES
(2008, 1)
, (2008, 2)
, (2008, 3)
, (2008, 4)
, (2008, 5)
, (2008, 6)
, (2008, 7)
, (2008, 8)
, (2008, 9)
, (2008, 10)
, (2008, 11)
, (2008, 12)
, (2009, 1)
, (2009, 2)
, (2009, 3);
SELECT M.mese, M.anno, COALESCE(SUM(Q.Qta), 0) AS tot
FROM dbo.Mesi AS M
LEFT JOIN dbo.Qta AS Q
ON M.anno = YEAR(Q.data_ad)
AND M.mese = MONTH(Q.data_ad)
GROUP BY M.mese, M.anno;
/* Output:
mese anno tot
----------- ----------- -----------
1 2008 13
2 2008 0
3 2008 0
4 2008 0
5 2008 0
6 2008 0
7 2008 0
8 2008 0
9 2008 12
10 2008 0
11 2008 6
12 2008 0
1 2009 21
2 2009 0
3 2009 15
(15 row(s) affected)
*/
DROP TABLE dbo.Qta, dbo.Mesi;
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org