
            with curDT as (select localtimestamp as curDate)
            select 
            A.Analyzer_id as "Analyzer_id",
            A.Analyzer_Name as "Analyzer_Name",
            A.Analyzer_Code as "Analyzer_Code",
            A.pmUser_insID as "pmUser_insID",
            A.AnalyzerModel_id as "AnalyzerModel_id",
            A.MedService_id as "MedService_id",
            to_char(A.Analyzer_begDT, 'dd.mm.yyyy') as "Analyzer_begDT",
            to_char(A.Analyzer_endDT,'dd.mm.yyyy') as "Analyzer_endDT",
            A.Analyzer_LisClientId as "Analyzer_LisClientId",
            A.Analyzer_LisCompany as "Analyzer_LisCompany",
            A.Analyzer_LisLab as "Analyzer_LisLab",
            A.Analyzer_LisMachine as "Analyzer_LisMachine",
            A.Analyzer_LisLogin as "Analyzer_LisLogin",
            A.Analyzer_LisPassword as "Analyzer_LisPassword",
            A.Analyzer_LisNote as "Analyzer_LisNote",
            case when A.Analyzer_2wayComm = 2 then 1 else 0 end as "Analyzer_2wayComm",
            case when A.Analyzer_IsUseAutoReg = 2 then 1 else 0 end as "Analyzer_IsUseAutoReg",
            case when A.Analyzer_IsNotActive = 2 then 1 else 0 end as "Analyzer_IsNotActive",
            case when A.Analyzer_IsAutoOk = 2 then 1 else 0 end as "Analyzer_IsAutoOk",
            case when a.Analyzer_IsAutoGood = 2 then 1 else 0 end as "Analyzer_IsAutoGood",
            case when A.Analyzer_isFEK = 2 then 1 else 0 end as "Analyzer_isFEK",
            case when A.Analyzer_IsManualTechnic = 2 then 1 else 0 end as "Analyzer_IsManualTechnic",
            case when A.Analyzer_IsCito = 2 then 1 else 0 end as "Analyzer_IsCito",
            case when A.Analyzer_IsExternalQuality = 2 then 1 else 0 end as "Analyzer_IsExternalQuality",
            AnalyzerModel_id_ref.AnalyzerModel_Name as "AnalyzerModel_id_Name",
            MedService_id_ref.MedService_Name as "MedService_id_Name",
            MedService_id_ref.MedService_IsTechnologyCardWork as "MedService_IsTechnologyCardWork",
            case when MedService_id_ref.MedService_isAvtoTestApprove = 2 then 1
                else 0 end as "MedService_isAvtoTestApprove"
        
                ,case when A.Analyzer_IsNotActive = 2 then 'Неактивный' else 'Активный' end as "AnalyzerIsNotActive"
                ,rcn.registrationCertificateNumber as "registrationCertificateNumber"
            
                        ,coalesce(cnt.new, 0) as "new"
                        ,coalesce(cnt.toanalizAndSomeOk, 0) as "toanalizAndSomeOk"
                        ,coalesce(cnt.execAndOk, 0) as "execAndOk"
                    
            , MPCSN.MedProductCard_SerialNumber as "MedProductCard_SerialNumber"
            
            from 
            lis.v_Analyzer A
            left join lis.v_AnalyzerModel AnalyzerModel_id_ref ON AnalyzerModel_id_ref.AnalyzerModel_id = A.AnalyzerModel_id
            left join dbo.v_MedService MedService_id_ref ON MedService_id_ref.MedService_id = A.MedService_id
            
                    left join lateral (
                        select
                            AnalyzerFromMBU_cards as registrationCertificateNumber
                        from
                            lis.v_AnalyzerFromMBU
                        where
                            Analyzer_id = a.Analyzer_id
                            and (AnalyzerFromMBU_date is null or cast(AnalyzerFromMBU_date as date) > cast((select curDate from curDT) as date))
                    ) as rcn on true
                
                        left join lateral (
                            select
                                SUM(case when elr.SampleStatusType_id in (4) THEN 1 ELSE 0 END) as new,
                                SUM(case when elr.SampleStatusType_id in (5,7) THEN 1 ELSE 0 END) as toanalizAndSomeOk,
                                SUM(case when elr.SampleStatusType_id in (6,8) THEN 1 ELSE 0 END) as execAndOk
                            from v_EvnLabSample els
                                inner join v_EvnLabRequest elr on els.EvnLabRequest_id = elr.EvnLabRequest_id
                            where elr.SampleStatusType_id in (4,5,7,6,8)
                                and '2023-07-20' = COALESCE(
                                    to_char(els.EvnLabSample_StudyDT, 'yyyy-mm-dd'),
                                    to_char(els.EvnLabSample_setDate, 'yyyy-mm-dd'),
                                    to_char(elr.EvnLabRequest_didDate, 'yyyy-mm-dd')
                                )
                                and els.Analyzer_id = a.Analyzer_id
                        ) as cnt on true
                    
                    left join lateral (
                        select
                            MPC.MedProductCard_SerialNumber as MedProductCard_SerialNumber
                        from
                            v_MedProductAnalyzerLink mpal
                            inner join passport.v_MedProductCard MPC on MPC.MedProductCard_id = mpal.MedProductCard_id
                        where
                            mpal.Analyzer_id = a.Analyzer_id
                            and mpal.MedProductAnalyzerLink_endDT is null
                    ) as MPCSN on true
                
        
            WHERE (1=1)
            
                and a.Analyzer_2wayComm in (1,2) and a.AnalyzerModel_id is not null
            
                    and (cast((select curDate from curDT) as date) <= a.Analyzer_endDT or a.Analyzer_endDT is null)
                    and A.MedService_id is not null
                
        
