Select codalbergo,codcategoria,sum(nrletti)+sum(nrlettiextra),count(nrcamera),SUM(nrbagni) From dbo.anagrafica_camere group by codalbergo,codcategoria


except

select ap.codalbergo,a.codcategoria,sum(a.nrletti)+sum(a.nrlettiextra),count(a.nrcamera),SUM(a.nrbagni) from Apertura_chiusura ap
join
dbo.anagrafica_camere a
on a.codalbergo=ap.codalbergo 

where ap.datafineattivita <>'9999-01-01' 
or (ap.tipoapertura=0 and @data between ap.datatemp1 and ap.datatemp2) 
group by ap.codalbergo,codcategoria