CREATE FUNCTION dbo.GetMovimenti
(  
@CodAlbergo Int = Null,
@AnnoGestionale Int = Null,
@Data1 SmallDateTime = Null,
@Data2 SmallDateTime = Null
)
RETURNS TABLE 
AS
RETURN 
(
SELECT * From (
SELECT 'ITALIANI' AS TipoMov, COUNT(*)As Movimenti,'A' AS Stato FROM dbo.SchedinePS PS
WHERE
(PS.Codalbergo=@CodAlbergo Or @CodAlbergo Is Null) AND (PS.AnnoGestionale=@AnnoGestionale Or @AnnoGestionale Is Null)
AND (PS.DataArrivo Between @Data1 And @data2 Or @Data1 IS NULL Or @Data2 IS NULL)
AND (PS.IdStatoResidenza=121) 
UNION
SELECT 'ITALIANI' AS TipoMov, COUNT(*)As Movimenti,'P' AS Stato FROM dbo.SchedinePS PS
WHERE
(PS.Codalbergo=@CodAlbergo Or @CodAlbergo Is Null) AND (PS.AnnoGestionale=@AnnoGestionale Or @AnnoGestionale Is Null)
AND (PS.DataPartenza Between @Data1 And @data2 Or PS.DataPartenza=Case When @Data1 IS Null Or @Data2 IS Null Then DataPartenza END)
AND (PS.IdStatoResidenza=121) 
)As TblTemp

UNION

SELECT * From (
SELECT 'STRANIERI' AS TipoMov, COUNT(*)As Movimenti, 'A' AS Stato FROM dbo.SchedinePS PS
WHERE
(PS.Codalbergo=@CodAlbergo Or @CodAlbergo Is Null) AND (PS.AnnoGestionale=@AnnoGestionale Or @AnnoGestionale Is Null)
AND (PS.DataArrivo Between @Data1 And @data2 Or @Data1 IS NULL Or @Data2 IS NULL) 
AND (PS.IdStatoResidenza<>121) 
UNION
SELECT 'STRANIERI' AS TipoMov, COUNT(*)As Movimenti, 'P' AS Stato FROM dbo.SchedinePS PS
WHERE
(PS.Codalbergo=@CodAlbergo Or @CodAlbergo Is Null) AND (PS.AnnoGestionale=@AnnoGestionale Or @AnnoGestionale Is Null)
AND (PS.DataPartenza Between @Data1 And @data2 Or PS.DataPartenza=Case When @Data1 IS Null Or @Data2 IS Null Then DataPartenza END)
AND (PS.IdStatoResidenza<>121) 
)As TblTemp 
)
GO