CREATE PROCEDURE [dbo].[Print_rilevazione] 
(
@Codalbergo Int,
@CodComAlb Int,
@DallaData smalldatetime,
@AllaData smalldatetime
)
AS
BEGIN
SELECT D.codalbergo,D.codcomalb,D.data1
,IsNull(A.arr_ita,0) As Arrivi_ITA,IsNull(B.arr_str,0) As Arrivi_STR,IsNull(C.Par_ita,0) As Partiti_ITA,IsNull(D.par_str,0) As Partiti_STR
FROM(
--ARRIVI ITALIANI
SELECT codalbergo,codcomalb,data_elaborazione As Data1,
sum(CASE [stato] WHEN 'A' then ncomp END)AS Arr_Ita
FROM dbo.Archivio_mov 
WHERE
codalbergo=@Codalbergo
AND CodComAlb=@CodComAlb
AND data_elaborazione BETWEEN @DallaData AND @AllaData 
AND codstato='0' AND flagriporto=0 GROUP BY codalbergo,codcomalb,data_elaborazione)AS A
FULL JOIN
--ARRIVI STRANIERI
(SELECT codalbergo,codcomalb,data_elaborazione As Data1,
sum(CASE [stato] WHEN 'A' then ncomp END)AS Arr_str
FROM dbo.Archivio_mov 
WHERE
codalbergo=@Codalbergo
AND CodComAlb=@CodComAlb
AND data_elaborazione BETWEEN @DallaData AND @AllaData 
AND codstato<>'0' AND flagriporto=0 GROUP BY codalbergo,codcomalb,data_elaborazione) AS B
ON A.data1=B.Data1
FULL JOIN
--PARTENZE ITALIANI
(SELECT codalbergo,codcomalb,data_elaborazione As Data1,
sum(CASE [stato] WHEN 'P' then ncomp END)AS Par_Ita
FROM dbo.Archivio_mov 
WHERE
codalbergo=@Codalbergo
AND CodComAlb=@CodComAlb
AND data_elaborazione BETWEEN @DallaData AND @AllaData 
AND codstato='0' AND flagriporto=0 GROUP BY codalbergo,codcomalb,data_elaborazione) AS C
ON B.data1=C.data1
FULL JOIN
--PARTENZE STRANIERI
(SELECT codalbergo,codcomalb,data_elaborazione As Data1,
sum(CASE [stato] WHEN 'P' then ncomp END)AS Par_Str
FROM dbo.Archivio_mov 
WHERE
codalbergo=@Codalbergo
AND CodComAlb=@CodComAlb
AND data_elaborazione BETWEEN @DallaData AND @AllaData 
AND codstato<>'0' AND flagriporto=0 GROUP BY codalbergo,codcomalb,data_elaborazione) AS D 
ON C.data1=D.data1

END