IF EXISTS
(
    SELECT 1
    FROM SYS.objects
    WHERE
        object_id = OBJECT_ID('EHR.V_MKB_GENERAL')
)
    BEGIN
        DROP VIEW EHR.V_MKB_GENERAL
    END
GO
CREATE VIEW EHR.V_MKB_GENERAL
AS
SELECT NEWID()  AS MkbID,
       MKB_CODE AS Code,
       NameEHr  AS Name,
       0        AS Type,
       ehr.Date AS ActualDate
FROM EHR.T_EHR_RUS_MKB308 ehr
WHERE
      ACTUAL = 1
  AND EhrRusRefBooks =
      (
          SELECT TOP 1 rf.EhrRusRefBooksID
          FROM EHR.T_EHR_RUS_REF_BOOKS rf
          JOIN VCLIB.T_ENTITIES e ON e.EntityID = rf.Entities
              AND e.EntName = 'EHR_RUS_MKB308'
          ORDER BY CAST('/' + rf.CurrVersion + '/' AS HIERARCHYID) DESC
      )
  AND NOT EXISTS
      (
          SELECT 1
          FROM EHR.T_EHR_RUS_MKB308 child
          WHERE child.EhrRusTree = ehr.EhrRusID
            AND ACTUAL = 1
      )

UNION

SELECT NEWID() AS MkbID,
       Code    AS Code,
       NameEhr AS Name,
       1       AS Type,
       NULL    AS ActualDate
FROM EHR.T_EHR_RUS_23G4LTVREC ehr
WHERE
      EhrRusRefBooks =
      (
          SELECT TOP 1 rf.EhrRusRefBooksID
          FROM EHR.T_EHR_RUS_REF_BOOKS rf
          JOIN VCLIB.T_ENTITIES e ON e.EntityID = rf.Entities
              AND e.EntName = 'EHR_RUS_23G4LTVREC'
          ORDER BY CAST('/' + rf.CurrVersion + '/' AS HIERARCHYID) DESC
      )
  AND NOT EXISTS
      (
          SELECT 1
          FROM EHR.T_EHR_RUS_23G4LTVREC child
          WHERE child.EhrRusTree = ehr.EhrRusID
      )
GO