DECLARE @NUMGIORNIDIALISI INT
DECLARE @NUMDIALISI INT
DECLARE @DURATAMEDIADIALISI INT
DECLARE @NUMGIORNIDIALISIREALI INT
DECLARE @PRIMOGIORNODIALISI DATETIME
DECLARE @ULTIMOGIORNODIALISI DATETIME
DECLARE @PAZ1 INT
DECLARE @PAZ2 INT
DECLARE @PAZ3 INT
DECLARE @PAZ4 INT
DECLARE @NUMPOSTILETTO INT

------------------------------------------------------------------------------------------------
USE BEDNET
SELECT 
	@NUMGIORNIDIALISI = DATEDIFF(D,MIN(FIRST_REC_TIME),MAX(FIRST_REC_TIME)) ,	
	@NUMDIALISI =COUNT(FIRST_REC_TIME)  ,
	@PRIMOGIORNODIALISI =MIN(FIRST_REC_TIME)  ,
	@ULTIMOGIORNODIALISI = MAX(FIRST_REC_TIME)  
FROM 
	APP_DIA_HEADER 

SELECT @NUMGIORNIDIALISIREALI = COUNT(*) FROM (SELECT CONVERT(CHAR(8),FIRST_REC_TIME,112) FIRST_REC_TIME FROM APP_DIA_HEADER GROUP BY CONVERT(CHAR(8),FIRST_REC_TIME,112)) ADH

SELECT @DURATAMEDIADIALISI = AVG(NUMREC) FROM (SELECT WAN_ID,DIA_COUNT ,COUNT(REC_ID) NUMREC FROM APP_DIA_MON GROUP BY WAN_ID,DIA_COUNT ) A

SELECT
	@NUMGIORNIDIALISI [Numero giorni],
	@NUMGIORNIDIALISIREALI [Numero giorni con dialisi],
	@NUMDIALISI [Numero dialisi],
	@DURATAMEDIADIALISI [Durata media dialisi],
	@PRIMOGIORNODIALISI [Primo giorno di dialisi],
	@ULTIMOGIORNODIALISI [Ultimo giorno di dialisi]

------------------------------------------------------------------------------------------------
USE SINEDCARTELLA 	

SELECT
	@PAZ1 = COUNT(*)
FROM 
	(
	SELECT 
		COUNT(RECID) NUM
	FROM 
		DIALISI_GEN 
	WHERE 
		SE_DATA>=DATEADD(D,-30,GETDATE()) 
		AND SE_DATA<=GETDATE() 
	GROUP BY 
		SE_COD_PAZ 
	--HAVING 
		--COUNT(RECID)>10
	) PAZ

SELECT
	@PAZ2 = COUNT(*)
FROM 
	(
	SELECT 
		COUNT(RECID) NUM
	FROM 
		ESAMI_STRUM 
	WHERE 
		EV_DATA>=DATEADD(D,-30,GETDATE()) 
		AND EV_DATA<=GETDATE() 
	GROUP BY 
		EV_COD_PAZ 
	--HAVING 
		--COUNT(RECID)>10
	) PAZ
	
SELECT
	@PAZ3 = COUNT(*)
FROM 
	(
	SELECT 
		COUNT(RECID) NUM
	FROM 
		ESAMI_BIO 
	WHERE 
		ES_DATA>=DATEADD(D,-30,GETDATE()) 
		AND ES_DATA<=GETDATE() 
	GROUP BY 
		ES_COD_PAZ 
	--HAVING 
		--COUNT(RECID)>10
	) PAZ	

SELECT
	@PAZ4 = COUNT(*)
FROM 
	(
	SELECT 
		COUNT(RECID) NUM
	FROM 
		DOC_PAZIENTE 
	WHERE 
		DP_DATA_AGG>=DATEADD(D,-30,GETDATE()) 
		AND DP_DATA_AGG<=GETDATE() 
	GROUP BY 
		DP_COD_PAZ 
	--HAVING 
		--COUNT(RECID)>10
	) PAZ	

SELECT (@PAZ1+ @PAZ2 + @PAZ3 + @PAZ4) / 4 [Numero pazienti stimato]


SELECT @NUMPOSTILETTO = COUNT(*) FROM TAB_POSTI WHERE N_EXPERT IS NOT NULL

SELECT 'Lunedì' GIORNO, COUNT(*) NUM_TURNI FROM (SELECT DISTINCT SD_TURNO_LUN FROM DIA_TURNI WHERE GIORNO = 1) D
UNION ALL SELECT 'Martedì' GIORNO, COUNT(*) NUM_TURNI FROM (SELECT DISTINCT SD_TURNO_MAR FROM DIA_TURNI WHERE GIORNO = 2) D
UNION ALL SELECT 'Mercoledì' GIORNO, COUNT(*) NUM_TURNI FROM (SELECT DISTINCT SD_TURNO_MER FROM DIA_TURNI WHERE GIORNO = 3) D
UNION ALL SELECT 'Giovedì' GIORNO, COUNT(*) NUM_TURNI FROM (SELECT DISTINCT SD_TURNO_GIO FROM DIA_TURNI WHERE GIORNO = 4) D
UNION ALL SELECT 'Venerdì' GIORNO, COUNT(*) NUM_TURNI FROM (SELECT DISTINCT SD_TURNO_VEN FROM DIA_TURNI WHERE GIORNO = 5) D
UNION ALL SELECT 'Sabato' GIORNO, COUNT(*) NUM_TURNI FROM (SELECT DISTINCT SD_TURNO_SAB FROM DIA_TURNI WHERE GIORNO = 6) D
UNION ALL SELECT 'Domenica' GIORNO, COUNT(*) NUM_TURNI FROM (SELECT DISTINCT SD_TURNO_DOM FROM DIA_TURNI WHERE GIORNO = 7) D

SELECT 
	GIORNO [Giorno],
	COUNT(SD_POSTO) [Numero posti letto]
FROM 
	(
	SELECT DISTINCT
		GIORNO,
		SD_POSTO
	FROM
		DIA_TURNI
	WHERE 
			SD_TURNO_LUN IS NOT NULL 
			OR SD_TURNO_MAR IS NOT NULL 
			OR SD_TURNO_MER IS NOT NULL 
			OR SD_TURNO_GIO IS NOT NULL 
			OR SD_TURNO_VEN IS NOT NULL 
			OR SD_TURNO_SAB IS NOT NULL 
			OR SD_TURNO_DOM IS NOT NULL 
	) PROG	
GROUP BY 
	GIORNO