
                
    with curDT as (select localtimestamp as curDate)
    
                
                SELECT 
        S.SMI_id as "SMI_id",
        S.SMI_objectID as "SMI_objectID",  -- ID документа
        S.SMI_SMIDate as "SMI_SMIDate", -- Дата создания документа
        to_char(S.SMI_SMIDate, 'DD.MM.YYYY') as "SMIDate",  -- Форматированная дата создания документа
           -- Форматированная дата отправки документа
        to_char(VJQ.VimisJournalQuery_OutDT, 'DD.MM.YYYY') as "LastOutDT",
        S.Lpu_id as "Lpu_id",
        Lpu.Lpu_Nick as "Lpu_Nick",
        SVS.SMIVersionStatus_id as "SMIVersionStatus_id",
        SVS.SMIVersionStatus_Code as "SMIVersionStatus_Code",
        SVS.SMIVersionStatus_Name || '(' || coalesce(to_char(VJQ.VimisJournalQuery_OutDT,
            'DD.MM.YYYY'), 'нет даты') || ')' as "SMIVersionStatus_Name",
        PS.Person_SurName || coalesce(' ' || PS.Person_FirName,'') ||
            coalesce(' ' || PS.Person_SecName,'') as "Person_FIO", -- ФИО пациента
        to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay",  -- Форматированная Д/Р пациента
        ST.SMIType_Name as "SMIType_Name", -- Тип СМС
        S.VimisType_id as "VimisType_id",
        VT.VimisType_Name as "VimisType_Name", -- Профиль ВИМИС
        STL.SMITypeLocal_id as "SMITypeLocal_id",
        STL.SMITypeLocal_Nick as "SMITypeLocal_Nick",
        STL.SMITypeLocal_Decsr as "SMITypeLocal_Decsr",
        STL.SMITypeLocal_Document as "SMITypeLocal_Document",
        VTT.VimisTriggerType_Nick as "VimisTriggerType_Nick",   -- Триггер
        VTT.VimisTriggerType_Descr as "VimisTriggerType_Descr",
        VJQ.VimisJournalQuery_id as "VimisJournalQuery_id",
        VJQ.VimisJournalQuery_MessageID as "VimisJournalQuery_MessageID",
        S.LpuBuilding_id as "LpuBuilding_id",
        COALESCE( LB.LpuBuilding_Name, LB.LpuBuilding_Nick ) as "LpuBuilding_Nick",
        S.MedPersonal_id as "MedPersonal_id",
        mp.Person_Fio as "MedPersonal_Fio",
        COALESCE(t.CNT, 0) as "VimisQueryError_Count"
        
                FROM
                
        vimis.v_SMI S
        inner join vimis.VimisType VT on vt.VimisType_id = s.VimisType_id
        INNER JOIN LATERAL (
            select
                SMIVersion_id,
                SMIVersionStatus_id
            from
                vimis.v_SMIVersion
            where
                SMI_id = S.SMI_id
            order by
                SMIVersion_id desc
            LIMIT 1
        ) SV ON TRUE
        inner join vimis.v_VimisTriggerSMI VTS ON SV.SMIVersion_id = VTS.SMIVersion_id
        inner join vimis.v_VimisTrigger VTg on VTS.VimisTrigger_id = VTg.VimisTrigger_id
        inner join vimis.v_VimisTriggerType VTT ON VTT.VimisTriggerType_id = VTg.VimisTriggerType_id
            
        inner join vimis.v_SMITypeLocal STL on STL.SMITypeLocal_id = S.SMITypeLocal_id
        inner join vimis.v_SMIType ST on STL.SMIType_id = ST.SMIType_id 
        inner join vimis.v_SMIVersionStatus SVS on SV.SMIVersionStatus_id = SVS.SMIVersionStatus_id
            
        inner join dbo.v_PersonState PS ON S.Person_id = PS.Person_id
        inner join v_Lpu_lite Lpu on Lpu.Lpu_id = s.Lpu_id
        -- Фильтруем СМС-ки только для активных вимисов, т.к. в базе появились СМС для сторонних сервисов.
        left join dbo.v_LpuBuilding LB ON S.LpuBuilding_id = LB.LpuBuilding_id
        left join lateral (
            select
                msfc.MedStaffFact_id,
                msfc.Person_Fin,
                msfc.Person_Fio,
                msfc.lpu_id
            from
                v_MedStaffFact msfc
            where
                msfc.MedPersonal_id = s.MedPersonal_id and
                msfc.Lpu_id = s.Lpu_id and
                msfc.WorkData_begDate < s.SMI_SMIDate
            order by
                MedStaffFact_id
                offset 0 rows fetch next 1 rows only
        ) mp on true

        left join vimis.v_VimisJournalQuery VJQ ON (VTS.VimisTriggerSMI_id = VJQ.VimisTriggerSMI_id)
        LEFT JOIN LATERAL (
            select
                count(E.VimisQueryError_id) as CNT
            from
                vimis.v_VimisQueryError E
            where
                E.VimisJournalQuery_id = VJQ.VimisJournalQuery_id
        ) t ON TRUE
         WHERE 
        (1=1)
         and ((S.SMI_SMIDate >= CAST('2022-07-09' as date) and S.SMI_SMIDate < CAST('2023-07-10' as date)) and
        VJQ.VimisJournalQuery_OutDT is null and
        S.VimisType_id in (1,4,3,2))
        
                
                        ORDER BY
                            
        S.SMI_SMIDate
        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
