IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[APS].[T_RR_PATIENT]') AND name = N'IX_T_RR_PATIENT_SNILS_DocSeries_DocNumber')
DROP INDEX [IX_T_RR_PATIENT_SNILS_DocSeries_DocNumber] ON [APS].[T_RR_PATIENT]
GO
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[APS].[T_RR_PATIENT]') AND name = N'IX_T_RR_PATIENT_SNILS_DocSeries_DocNumber')
CREATE NONCLUSTERED INDEX [IX_T_RR_PATIENT_SNILS_DocSeries_DocNumber] ON [APS].[T_RR_PATIENT]
(
	[SNILS] ASC,
	[DocSeries] ASC,
	[DocNumber] ASC
)
INCLUDE ( 	[FatherName],
	[FirstName],
	[LastName],
	[Birthday],
	[Sex]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO



IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[APS].[V_DLO_PATIENT]'))
DROP VIEW [APS].[V_DLO_PATIENT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [APS].[V_DLO_PATIENT]
AS
with vrp as
        (
            SELECT LTRIM(RTRIM(rrp.LastName)) as LastName, LTRIM(RTRIM(rrp.FirstName)) as FirstName, LTRIM(RTRIM(rrp.FatherName)) as FatherName, rrp.Birthday, rrp.Sex - 10300 Sex, rrp.SNILS, LTRIM(RTRIM(rrp.DocSeries)) as DocSeries, LTRIM(RTRIM(rrp.DocNumber)) as DocNumber
				FROM [APS].T_RR_PATIENT rrp WITH(NOLOCK)
					UNION ALL
            SELECT LTRIM(RTRIM(LEFT(frp.LastName, 40))) as LastName, LTRIM(RTRIM(LEFT(frp.FirstName, 40))) as FirstName, LTRIM(RTRIM(LEFT(frp.FatherName, 40))) as FatherName, frp.Birthday, frp.Sex + 1, frp.SNILS, null, null
				FROM [APS].T_FR_PATIENT frp WITH(NOLOCK)
					UNION ALL
            SELECT LTRIM(RTRIM(LEFT(nsp.LastName, 40))) as LastName, LTRIM(RTRIM(LEFT(nsp.FirstName, 40)))as FirstName, LTRIM(RTRIM(LEFT(nsp.FatherName, 40))) as FatherName, nsp.Birthday, nsp.Sex - 10300, nsp.SNILS, LEFT(LTRIM(RTRIM(nsp.DocSeries)), 8), LEFT(LTRIM(RTRIM(nsp.DocNumber)), 8)
				FROM [APS].T_NOSOLOGY nsp WITH(NOLOCK)
					UNION ALL
            SELECT LTRIM(RTRIM(rfp.LastName)) as LastName, LTRIM(RTRIM(rfp.FirstName)) as FirstName, LTRIM(RTRIM(rfp.FatherName)) as FatherName, rfp.Birthday, rfp.Sex - 10300, rfp.Snils, null, null
				FROM [APS].T_REFERENCE rfp WITH(NOLOCK)
					UNION ALL
			SELECT LTRIM(RTRIM(TBR.LastName)) as LastName, LTRIM(RTRIM(TBR.FirstName)) as FirstName, LTRIM(RTRIM(TBR.FatherName)) as FatherName, TBR.Birthday, RIGHT(TBR.Sex, 1) AS [SEX], TBR.SNILS, LTRIM(RTRIM(TBR.DocSeries)), LTRIM(RTRIM(TBR.DocNumber))
				FROM APS.T_BSK_REGISTER TBR WITH(NOLOCK)
					UNION ALL
			SELECT LTRIM(RTRIM(TOR.LastName)) as LastName, LTRIM(RTRIM(TOR.FirstName)) as FirstName, LTRIM(RTRIM(TOR.FatherName)) as FatherName, TOR.Birthday, RIGHT(TOR.Sex, 1) AS [SEX], TOR.Snils, LTRIM(RTRIM(TOR.DocSeries)), LTRIM(RTRIM(TOR.DocNumber))
				FROM APS.T_ORPHAN_REGISTER TOR WITH(NOLOCK)
					UNION ALL
			SELECT LTRIM(RTRIM(TPR.LastName)) as LastName, LTRIM(RTRIM(TPR.FirstName)) as FirstName, LTRIM(RTRIM(TPR.FatherName)) as FatherName, TPR.Birthday, RIGHT(TPR.Sex, 1) AS [SEX], TPR.SNILS, LTRIM(RTRIM(TPR.DocSeries)), LTRIM(RTRIM(TPR.DocNumber))
				FROM APS.T_PALLIATIVE_REGISTER TPR WITH(NOLOCK)
					UNION ALL
			SELECT LTRIM(RTRIM(SBR.LastName)) as LastName, LTRIM(RTRIM(SBR.FirstName)) as FirstName, LTRIM(RTRIM(SBR.FatherName)) as FatherName, SBR.Birthday, SBR.Sex - 10300 AS [SEX], SBR.SNILS, LTRIM(RTRIM(SBR.DocSeries)), LTRIM(RTRIM(SBR.DocNumber))
				FROM APS.T_SPECIAL_BENEFITS_REGISTR SBR WITH(NOLOCK)

        )

    -- Группировка по SNILS + ДР
    select CAST(vrp.SNILS + CONVERT(varchar(10), vrp.Birthday, 104) as varchar(90)) as Id, MAX(vrp.LastName) as LastName, MAX(vrp.FirstName) as FirstName, MAX(vrp.FatherName) as FatherName, vrp.Birthday, MAX(vrp.Sex) as Sex, vrp.SNILS, MAX(vrp.DocSeries) as DocSeries, MAX(vrp.DocNumber) as DocNumber, 0 PatientOrder
    from vrp
    where vrp.SNILS is not null
    group by vrp.SNILS, vrp.Birthday
    union all
    -- Группировка по серии и номеру документа
    select CAST(vrp.DocSeries + vrp.DocNumber as varchar(90)), MAX(vrp.LastName), MAX(vrp.FirstName), MAX(vrp.FatherName), MAX(vrp.Birthday), MAX(vrp.Sex), MAX(vrp.SNILS), vrp.DocSeries, vrp.DocNumber, 1
    from vrp
    where vrp.DocSeries is not null
            and
        vrp.DocSeries <> ''
            and
        vrp.DocNumber is not null
            and
        vrp.DocNumber <> ''
            and
        vrp.SNILS is null
    group by vrp.DocSeries, vrp.DocNumber
    union all
    -- Группировка по ФИО + ДР
    select CAST(vrp.LastName + vrp.FirstName + ISNULL(vrp.FatherName, '') + CONVERT(varchar(10), vrp.Birthday, 104) as varchar(90)), vrp.LastName, vrp.FirstName, vrp.FatherName, vrp.Birthday, MAX(vrp.Sex), MAX(vrp.SNILS), MAX(vrp.DocSeries), MAX(vrp.DocNumber), 2
    from vrp
    where vrp.SNILS is null
            and
        (vrp.DocSeries is null or vrp.DocNumber is null or vrp.DocSeries = '' or vrp.DocNumber = '')
    group by vrp.LastName, vrp.FirstName, vrp.FatherName, vrp.Birthday
GO