query.sql

WITH mov_patologie AS (
	                        SELECT
		                        hh_zcal_movvac.codditt,
		                        hh_zcal_movvac.mv_idcapo	AS xx_idcapo,
		                        hh_zcal_testvac.tv_data		AS xx_data,
		                        hh_zcal_movvac.mv_codpato	AS xx_codpato
	                        FROM hh_zcal_movvac
	                        INNER JOIN hh_zcal_testvac
		                        ON hh_zcal_testvac.codditt = hh_zcal_movvac.codditt
		                        AND hh_zcal_testvac.tv_tipo = hh_zcal_movvac.mv_tipo
		                        AND hh_zcal_testvac.tv_anno = hh_zcal_movvac.mv_anno
		                        AND hh_zcal_testvac.tv_serie = hh_zcal_movvac.mv_serie
		                        AND hh_zcal_testvac.tv_num = hh_zcal_movvac.mv_num
	                        WHERE
		                        hh_zcal_movvac.mv_codpato <> 0
	                        UNION ALL
	                        SELECT
		                        hh_zcal_testmed.codditt,
		                        hh_zcal_medcapi.mc_idcapo	AS xx_idcapo,
		                        hh_zcal_testmed.tm_data		AS xx_data,
		                        hh_zcal_medcapi.mc_codpato	AS xx_codpato
	                        FROM hh_zcal_testmed
	                        INNER JOIN hh_zcal_medcapi
		                        ON hh_zcal_testmed.codditt = hh_zcal_medcapi.codditt
		                        AND hh_zcal_testmed.tm_tipo = hh_zcal_medcapi.mc_tipo
		                        AND hh_zcal_testmed.tm_anno = hh_zcal_medcapi.mc_anno
		                        AND hh_zcal_testmed.tm_serie = hh_zcal_medcapi.mc_serie
		                        AND hh_zcal_testmed.tm_num = hh_zcal_medcapi.mc_num
	                        WHERE
		                        hh_zcal_medcapi.mc_codpato <> 0
                        ), 
                        mov_tipoanimale AS (
	                        SELECT
		                        hh_zcal_movvac.codditt,
		                        hh_zcal_movvac.mv_idcapo	AS xx_idcapo,
		                        hh_zcal_testvac.tv_data		AS xx_data,
		                        hh_zcal_movvac.mv_codtpan	AS xx_codtpan
	                        FROM hh_zcal_movvac
	                        INNER JOIN hh_zcal_testvac
		                        ON hh_zcal_testvac.codditt = hh_zcal_movvac.codditt
		                        AND hh_zcal_testvac.tv_tipo = hh_zcal_movvac.mv_tipo
		                        AND hh_zcal_testvac.tv_anno = hh_zcal_movvac.mv_anno
		                        AND hh_zcal_testvac.tv_serie = hh_zcal_movvac.mv_serie
		                        AND hh_zcal_testvac.tv_num = hh_zcal_movvac.mv_num
	                        WHERE
		                        hh_zcal_movvac.mv_codtpan <> 0
	                        UNION ALL
	                        SELECT
		                        hh_zcal_testmed.codditt,
		                        hh_zcal_medcapi.mc_idcapo	AS xx_idcapo,
		                        hh_zcal_testmed.tm_data		AS xx_data,
		                        hh_zcal_medcapi.mc_codtpan	AS xx_codtpan
	                        FROM hh_zcal_testmed
	                        INNER JOIN hh_zcal_medcapi
		                        ON hh_zcal_testmed.codditt = hh_zcal_medcapi.codditt
		                        AND hh_zcal_testmed.tm_tipo = hh_zcal_medcapi.mc_tipo
		                        AND hh_zcal_testmed.tm_anno = hh_zcal_medcapi.mc_anno
		                        AND hh_zcal_testmed.tm_serie = hh_zcal_medcapi.mc_serie
		                        AND hh_zcal_testmed.tm_num = hh_zcal_medcapi.mc_num
	                        WHERE
		                        hh_zcal_medcapi.mc_codtpan <> 0
                        )
                        UPDATE hh_zcal_anagcapi
                        SET
	                        ac_codpato = COALESCE(ultima_patologia.xx_codpato, ultima_entrata.mp_codpato, 0),
	                        ac_codimpa = COALESCE(ultimo_movimento.mv_codimpa_a, ultima_entrata.mp_codimpa, 0),
	                        ac_cod_box = COALESCE(ultimo_movimento.mv_cod_box_a, ultima_entrata.mp_cod_box, ''),
	                        ac_datbox = COALESCE(ultimo_movimento.tv_data, ultima_entrata.tp_data, NULL),
	                        ac_datsca = COALESCE(ultimo_scarico.tv_data, NULL),
	                        ac_codtpsc = COALESCE(ultima_morte.mv_codtpsc, ultimo_scarico.mv_codtpsc, 0),
                            ac_dating = COALESCE(ultima_entrata.tp_data, NULL),
	                        ac_flmorto = CASE WHEN ultima_morte.tv_data IS NULL THEN 'N' ELSE 'S' END,
	                        ac_datmorte = COALESCE(ultima_morte.tv_data, NULL),
	                        ac_codtpan = COALESCE(ultimo_tipoanimale.xx_codtpan, ultima_entrata.mp_codtpan, 0)
                        FROM hh_zcal_anagcapi
                        OUTER APPLY (
	                        SELECT TOP 1 
		                        hh_zcal_testpar.tp_data,
		                        hh_zcal_movpar.mp_codpato,
		                        hh_zcal_movpar.mp_codtpan,
		                        hh_zcal_movpar.mp_codimpa,
		                        hh_zcal_movpar.mp_cod_box
	                        FROM hh_zcal_movpar
	                        INNER JOIN hh_zcal_testpar
		                        ON hh_zcal_testpar.codditt = hh_zcal_movpar.codditt
		                        AND hh_zcal_testpar.tp_tipo = hh_zcal_movpar.mp_tipo
		                        AND hh_zcal_testpar.tp_anno = hh_zcal_movpar.mp_anno
		                        AND hh_zcal_testpar.tp_serie = hh_zcal_movpar.mp_serie
		                        AND hh_zcal_testpar.tp_num = hh_zcal_movpar.mp_num
	                        WHERE
		                        hh_zcal_movpar.codditt = hh_zcal_anagcapi.codditt
		                        AND hh_zcal_movpar.mp_idcapo = hh_zcal_anagcapi.ac_id
	                        ORDER BY hh_zcal_testpar.tp_data DESC
                        ) AS ultima_entrata
                        OUTER APPLY (
	                        SELECT TOP 1 
		                        hh_zcal_testvac.tv_data,
		                        hh_zcal_movvac.mv_codimpa_a,
		                        hh_zcal_movvac.mv_cod_box_a
	                        FROM hh_zcal_movvac
	                        INNER JOIN hh_zcal_testvac
		                        ON hh_zcal_testvac.codditt = hh_zcal_movvac.codditt
		                        AND hh_zcal_testvac.tv_tipo = hh_zcal_movvac.mv_tipo
		                        AND hh_zcal_testvac.tv_anno = hh_zcal_movvac.mv_anno
		                        AND hh_zcal_testvac.tv_serie = hh_zcal_movvac.mv_serie
		                        AND hh_zcal_testvac.tv_num = hh_zcal_movvac.mv_num
	                        WHERE
		                        hh_zcal_movvac.codditt = hh_zcal_anagcapi.codditt
		                        AND hh_zcal_movvac.mv_idcapo = hh_zcal_anagcapi.ac_id
		                        AND hh_zcal_testvac.tv_data >= ultima_entrata.tp_data
		                        AND hh_zcal_testvac.tv_tipo IN ('M', 'P')
	                        ORDER BY hh_zcal_testvac.tv_data DESC
                        ) AS ultimo_movimento
                        OUTER APPLY (
	                        SELECT TOP 1 
		                        hh_zcal_testvac.tv_data,
		                        hh_zcal_movvac.mv_codtpsc
	                        FROM hh_zcal_movvac
	                        INNER JOIN hh_zcal_testvac
		                        ON hh_zcal_testvac.codditt = hh_zcal_movvac.codditt
		                        AND hh_zcal_testvac.tv_tipo = hh_zcal_movvac.mv_tipo
		                        AND hh_zcal_testvac.tv_anno = hh_zcal_movvac.mv_anno
		                        AND hh_zcal_testvac.tv_serie = hh_zcal_movvac.mv_serie
		                        AND hh_zcal_testvac.tv_num = hh_zcal_movvac.mv_num
	                        WHERE
		                        hh_zcal_movvac.codditt = hh_zcal_anagcapi.codditt
		                        AND hh_zcal_movvac.mv_idcapo = hh_zcal_anagcapi.ac_id
		                        AND hh_zcal_testvac.tv_data >= ultima_entrata.tp_data
		                        AND hh_zcal_testvac.tv_tipo IN ('S')
	                        ORDER BY hh_zcal_testvac.tv_data DESC
                        ) AS ultimo_scarico
                        OUTER APPLY (
	                        SELECT TOP 1
		                        mov_patologie.xx_data,
		                        mov_patologie.xx_codpato
	                        FROM mov_patologie
	                        WHERE
		                        mov_patologie.codditt = hh_zcal_anagcapi.codditt
		                        AND mov_patologie.xx_idcapo = hh_zcal_anagcapi.ac_id
		                        AND mov_patologie.xx_data >= ultima_entrata.tp_data
	                        ORDER BY mov_patologie.xx_data DESC
                        ) AS ultima_patologia
                        OUTER APPLY (
	                        SELECT TOP 1
		                        mov_tipoanimale.xx_data,
		                        mov_tipoanimale.xx_codtpan
	                        FROM mov_tipoanimale
	                        WHERE
		                        mov_tipoanimale.codditt = hh_zcal_anagcapi.codditt
		                        AND mov_tipoanimale.xx_idcapo = hh_zcal_anagcapi.ac_id
		                        AND mov_tipoanimale.xx_data >= ultima_entrata.tp_data
	                        ORDER BY mov_tipoanimale.xx_data DESC
                        ) AS ultimo_tipoanimale
                        OUTER APPLY (
	                        SELECT TOP 1 
		                        hh_zcal_testvac.tv_data,
		                        hh_zcal_movvac.mv_codtpsc
	                        FROM hh_zcal_movvac
	                        INNER JOIN hh_zcal_testvac
		                        ON hh_zcal_testvac.codditt = hh_zcal_movvac.codditt
		                        AND hh_zcal_testvac.tv_tipo = hh_zcal_movvac.mv_tipo
		                        AND hh_zcal_testvac.tv_anno = hh_zcal_movvac.mv_anno
		                        AND hh_zcal_testvac.tv_serie = hh_zcal_movvac.mv_serie
		                        AND hh_zcal_testvac.tv_num = hh_zcal_movvac.mv_num
	                        WHERE
		                        hh_zcal_movvac.codditt = hh_zcal_anagcapi.codditt
		                        AND hh_zcal_movvac.mv_idcapo = hh_zcal_anagcapi.ac_id
		                        AND hh_zcal_testvac.tv_data >= ultima_entrata.tp_data
		                        AND hh_zcal_testvac.tv_tipo IN ('D')
	                        ORDER BY hh_zcal_testvac.tv_data DESC
                        ) AS ultima_morte