SELECT 
        RED,
        MicroRed AS MICRORED,
        E.Codigo_Unico AS RENAES,
        E.Nombre_Establecimiento AS ESTABLECIMIENTO, 
        SUM(CASE WHEN COD_ITEM IN ('90722','90723') AND TIP_EDAD = 'M' AND LAB in ('D3','3') THEN 1 ELSE 0 END) AS num_PENTA_3_1A,
        SUM(CASE WHEN COD_ITEM = '90707' AND EDAD = 1 AND TIP_EDAD = 'A' AND LAB IN ('D2','2')  THEN 1 ELSE 0 END) AS SPR2_1A,
        SUM(CASE WHEN COD_ITEM IN ('90658','90657') AND EDAD = 2 AND TIP_EDAD = 'A'     THEN 1 ELSE 0 END) AS num_INFL_2A,
        SUM(CASE WHEN COD_ITEM IN ('90658','90657') AND EDAD = 3 AND TIP_EDAD = 'A'     THEN 1 ELSE 0 END) AS num_INFL_3A,
        SUM(CASE WHEN COD_ITEM = '90701' AND EDAD = 4 AND TIP_EDAD = 'A' AND LAB IN ('D2','2','DA','DDA') AND ID_DOSIS='9' THEN 1 ELSE 0 END) AS num_DPT_2REF_4A,

        SUM(CASE WHEN COD_ITEM = '90649' AND EDAD BETWEEN 9 AND 18 AND TIP_EDAD = 'A' THEN 1 ELSE 0 END) AS num_VPH_9_18A,
        SUM(CASE WHEN COD_ITEM = '90715' AND TIP_EDAD = 'A' AND SEXO = 'F' THEN 1 ELSE 0   END) AS DPTA,
        SUM(CASE WHEN COD_ITEM = '90670' AND EDAD >= 60 AND TIP_EDAD = 'A' THEN 1 ELSE 0 END) AS num_NEUMO_60,
        SUM(CASE WHEN COD_ITEM IN ('90658','90657') AND EDAD >= 60 AND TIP_EDAD = 'A'  THEN 1 ELSE 0  END) AS num_INFLUENZA_60,


        -- PERSONAS ÚNICAS MENORES DE 5 SOLO DE LAS VACUNAS CONSIDERADAS
        COUNT(DISTINCT CASE 
                WHEN (
                        (COD_ITEM IN ('90722','90723') AND TIP_EDAD = 'M' AND LAB in ('D3','3'))
                     OR (COD_ITEM = '90707' AND EDAD = 1 AND TIP_EDAD = 'A' AND LAB IN ('D2','2'))
                     OR (COD_ITEM IN ('90658','90657') AND EDAD = 2 AND TIP_EDAD = 'A')
                     OR (COD_ITEM IN ('90658','90657') AND EDAD = 3 AND TIP_EDAD = 'A')
                     OR (COD_ITEM = '90701' AND EDAD = 4 AND TIP_EDAD = 'A' AND LAB IN ('D2','2','DA','DDA') AND ID_DOSIS='9')
                    )
                THEN NRO_DOC
            END) AS num_PMENOR_5,

         -- PERSONAS ÚNICAS DE 5 A MÁS SOLO DE LAS VACUNAS CONSIDERADAS
        COUNT(DISTINCT CASE 
                WHEN (
                        (COD_ITEM = '90649' AND EDAD BETWEEN 9 AND 18 AND TIP_EDAD = 'A')
                     OR (COD_ITEM = '90715' AND TIP_EDAD = 'A' AND SEXO = 'F')
                     OR (COD_ITEM = '90670' AND EDAD >= 60 AND TIP_EDAD = 'A')
                     OR (COD_ITEM IN ('90658','90657') AND EDAD >= 60 AND TIP_EDAD = 'A')
                    )
                THEN NRO_DOC
            END) AS num_PMAYOR_5,

        -- DOSIS MENORES DE 5 SOLO DE LAS VACUNAS CONSIDERADAS
        SUM(CASE 
                WHEN (
                        (COD_ITEM IN ('90722','90723') AND TIP_EDAD = 'M' AND LAB in ('D3','3'))
                     OR (COD_ITEM = '90707' AND EDAD = 1 AND TIP_EDAD = 'A' AND LAB IN ('D2','2'))
                     OR (COD_ITEM IN ('90658','90657') AND EDAD = 2 AND TIP_EDAD = 'A')
                     OR (COD_ITEM IN ('90658','90657') AND EDAD = 3 AND TIP_EDAD = 'A')
                     OR (COD_ITEM = '90701' AND EDAD = 4 AND TIP_EDAD = 'A' AND LAB IN ('D2','2','DA','DDA') AND ID_DOSIS='9')
                                         
                    )
                THEN 1 ELSE 0 
            END) AS num_DMENOR_5,

        -- DOSIS DE 5 A MÁS SOLO DE LAS VACUNAS CONSIDERADAS
        SUM(CASE 
                WHEN (
                        (COD_ITEM = '90649' AND EDAD BETWEEN 9 AND 18 AND TIP_EDAD = 'A')
                     OR (COD_ITEM = '90715' AND TIP_EDAD = 'A' AND SEXO = 'F')
                     OR (COD_ITEM = '90670' AND EDAD >= 60 AND TIP_EDAD = 'A')
                     OR (COD_ITEM IN ('90658','90657') AND EDAD >= 60 AND TIP_EDAD = 'A')
                    )
                THEN 1 ELSE 0 
            END) AS num_DMAYOR_5

    FROM DataHIS.dbo.HIS H INNER JOIN DataHIS.dbo.ESTABLECIMIENTOS E ON CONVERT(int, E.Codigo_Unico) = CONVERT(int, H.RENAES)
    WHERE FECHA >= '2026-04-25' AND FECHA <=  '2026-05-10' AND ID_L = 1
    GROUP BY E.RED, E.MicroRed,E.Codigo_Unico, E.Nombre_Establecimiento