SELECT lpu.lpu_ogrn AS lpu_ogrn,
       lpu.lpu_nick AS lpu_nick,
       ls.lpusection_id,
       ls.lpusection_name,
       addr.address_address,
       ls.lpusectionprofile_code,
       ls.lpusectionprofile_name,
       CASE
           WHEN lsp.lpusectionbedprofile_id = '3138' THEN '84_1'
           WHEN lsp.lpusectionbedprofile_id = '3178' THEN '3_1'
           WHEN lsp.lpusectionbedprofile_id = '3069' THEN '24_1'
           WHEN lsp.lpusectionbedprofile_code = '27' THEN '74_1'
           ELSE lspfed.lpusectionbedprofile_code
       END LpuSectionBedProfile_Code,
       lspfed.lpusectionbedprofile_name,
       lsp.lpusectionbedprofile_code AS bedprofile_code,
       lsp.lpusectionbedprofile_name AS bedprofile_name,
       coalesce(lss.lpusectionbedstate_fact, 0) AS fact,
       coalesce(lss.lpusectionbedstate_malefact, 0) AS factmale,
       coalesce(lss.lpusectionbedstate_femalefact, 0) AS factfemale,
       CASE
           WHEN lsp.lpusectionbedprofile_ischild = 2 THEN coalesce(lss.lpusectionbedstate_fact, 0)
           ELSE 0
       END factchild,
       CASE
           WHEN coalesce(lsp.lpusectionbedprofile_ischild, 1)=1 THEN coalesce(lss.lpusectionbedstate_fact, 0)-coalesce(lss.lpusectionbedstate_malefact, 0)-coalesce(lss.lpusectionbedstate_femalefact, 0)
           ELSE 0
       END AS factob,
       es.zan AS zan,
 (coalesce(lss.lpusectionbedstate_fact, 0) - es.zan) AS Svob,
 CASE
     WHEN (coalesce(lss.lpusectionbedstate_malefact, 0) - es.Male)>0 THEN coalesce(lss.lpusectionbedstate_malefact, 0) - es.Male
     ELSE 0
 END AS SvobMale,
 CASE
     WHEN (coalesce(lss.lpusectionbedstate_femalefact, 0) - es.Female)>0 THEN coalesce(lss.lpusectionbedstate_femalefact, 0) - es.Female
     ELSE 0
 END AS SvobFeMale,
 CASE
     WHEN (CASE
               WHEN lsp.lpusectionbedprofile_ischild = 2 THEN coalesce(lss.lpusectionbedstate_fact, 0)
               ELSE 0
           END - es.Child)>0 THEN CASE
                                      WHEN lsp.lpusectionbedprofile_ischild = 2 THEN coalesce(lss.lpusectionbedstate_fact, 0)
                                      ELSE 0
                                  END - es.Child
     ELSE 0
 END AS SvobChild,
 ((CASE
       WHEN coalesce(lsp.lpusectionbedprofile_ischild, 1)=1 THEN coalesce(lss.lpusectionbedstate_fact, 0)-coalesce(lss.lpusectionbedstate_malefact, 0)-coalesce(lss.lpusectionbedstate_femalefact, 0)
       ELSE 0
   END) - ((CASE
                WHEN (es.Male - coalesce(lss.lpusectionbedstate_malefact, 0))>0 THEN es.Male - coalesce(lss.lpusectionbedstate_malefact, 0)
                ELSE 0
            END) + (CASE
                        WHEN (es.Female - coalesce(lss.lpusectionbedstate_femalefact, 0))>0 THEN es.Female - coalesce(lss.lpusectionbedstate_femalefact, 0)
                        ELSE 0
                    END))) AS SvobOb
FROM dbo.v_lpusectionbedstate AS lss
INNER JOIN fed.v_lpusectionbedprofilelink AS lsplink ON lsplink.lpusectionbedprofilelink_id=lss.lpusectionbedprofileLink_fedid
INNER JOIN dbo.v_lpusectionbedprofile AS lsp ON lsp.lpusectionbedprofile_id=lsplink.lpusectionbedprofile_id
INNER JOIN fed.v_lpusectionbedprofile AS lspfed ON lsplink.lpusectionbedprofile_fedid=lspfed.lpusectionbedprofile_id
INNER JOIN dbo.v_lpusection AS ls ON lss.lpusection_id=ls.lpusection_id
INNER JOIN dbo.lpubuilding AS b ON ls.lpubuilding_id=b.lpubuilding_id
LEFT JOIN dbo.address AS addr ON addr.address_id=b.address_id
INNER JOIN dbo.v_lpu AS lpu ON ls.lpu_id=lpu.lpu_id
LEFT JOIN fed.passporttoken AS pt ON lpu.lpu_id=pt.lpu_id
INNER JOIN lateral
  (SELECT count(evns.evnsection_id) AS zan, coalesce(sum(CASE
                                                             WHEN ps.sex_id=1
                                                                  AND ps.person_age>=18 THEN 1
                                                             ELSE 0
                                                         END), 0) AS male, coalesce(sum(CASE
                                                                                            WHEN ps.sex_id=2
                                                                                                 AND ps.person_age>=18 THEN 1
                                                                                            ELSE 0
                                                                                        END), 0) AS female, coalesce(sum(CASE
                                                                                                                             WHEN ps.person_age<18 THEN 1
                                                                                                                             ELSE 0
                                                                                                                         END), 0) AS child
   FROM dbo.v_evnsection AS evns
   INNER JOIN dbo.v_personstate_all AS ps ON evns.person_id=ps.person_id
   WHERE evns.lpu_id=lpu.lpu_id
     AND evns.lpusection_id=lss.lpusection_id
     AND evns.evnsection_disdate IS NULL
     AND coalesce(evns.evnsection_ispriem, 1)=1 ) AS es ON TRUE
WHERE lss.lpusectionbedstate_enddate IS NULL
  AND lsp.lpusectionbedprofile_enddt IS NULL
  AND lpu.lpu_enddate IS NULL
GROUP BY lpu.lpu_ogrn,
         lpu.lpu_nick,
         ls.lpusection_id,
         ls.lpusection_name,
         addr.address_address,
         ls.lpusectionprofile_code,
         ls.lpusectionprofile_name,
         CASE
             WHEN lsp.lpusectionbedprofile_id = '3138' THEN '84_1'
             WHEN lsp.lpusectionbedprofile_id = '3178' THEN '3_1'
             WHEN lsp.lpusectionbedprofile_id = '3069' THEN '24_1'
             WHEN lsp.lpusectionbedprofile_code = '27' THEN '74_1'
             ELSE lspfed.lpusectionbedprofile_code
         END,
         lspfed.lpusectionbedprofile_name,
         lsp.lpusectionbedprofile_code,
         lsp.lpusectionbedprofile_name,
         coalesce(lss.lpusectionbedstate_fact, 0),
         coalesce(lss.lpusectionbedstate_malefact, 0),
         coalesce(lss.lpusectionbedstate_femalefact, 0),
         CASE
             WHEN lsp.lpusectionbedprofile_ischild = 2 THEN coalesce(lss.lpusectionbedstate_fact, 0)
             ELSE 0
         END,
         CASE
             WHEN coalesce(lsp.lpusectionbedprofile_ischild, 1)=1 THEN coalesce(lss.lpusectionbedstate_fact, 0)-coalesce(lss.lpusectionbedstate_malefact, 0)-coalesce(lss.lpusectionbedstate_femalefact, 0)
             ELSE 0
         END,
         es.zan, (coalesce(lss.lpusectionbedstate_fact, 0) - es.zan), CASE
                                                                          WHEN (coalesce(lss.lpusectionbedstate_malefact, 0) - es.Male)>0 THEN coalesce(lss.lpusectionbedstate_malefact, 0) - es.Male
                                                                          ELSE 0
                                                                      END,
                                                                      CASE
                                                                          WHEN (coalesce(lss.lpusectionbedstate_femalefact, 0) - es.Female)>0 THEN coalesce(lss.lpusectionbedstate_femalefact, 0) - es.Female
                                                                          ELSE 0
                                                                      END,
                                                                      CASE
                                                                          WHEN (CASE
                                                                                    WHEN lsp.lpusectionbedprofile_ischild = 2 THEN coalesce(lss.lpusectionbedstate_fact, 0)
                                                                                    ELSE 0
                                                                                END - es.Child)>0 THEN CASE
                                                                                                           WHEN lsp.lpusectionbedprofile_ischild = 2 THEN coalesce(lss.lpusectionbedstate_fact, 0)
                                                                                                           ELSE 0
                                                                                                       END - es.Child
                                                                          ELSE 0
                                                                      END, ((CASE
                                                                                 WHEN coalesce(lsp.lpusectionbedprofile_ischild, 1)=1 THEN coalesce(lss.lpusectionbedstate_fact, 0)-coalesce(lss.lpusectionbedstate_malefact, 0)-coalesce(lss.lpusectionbedstate_femalefact, 0)
                                                                                 ELSE 0
                                                                             END) - ((CASE
                                                                                          WHEN (es.Male - coalesce(lss.lpusectionbedstate_malefact, 0))>0 THEN es.Male - coalesce(lss.lpusectionbedstate_malefact, 0)
                                                                                          ELSE 0
                                                                                      END) + (CASE
                                                                                                  WHEN (es.Female - coalesce(lss.lpusectionbedstate_femalefact, 0))>0 THEN es.Female - coalesce(lss.lpusectionbedstate_femalefact, 0)
                                                                                                  ELSE 0
                                                                                              END)))
