Запрос чернакову

SELECT
rbg.Name as [Гарантия],
rbg.Id as [Уникальный номер Элма],
applicant.OGRNIP as [ОГРН фирмы],
applicant.INN as [ИНН фирмы],
rbg.CreationDate as [Дата получения заявки],
applicant.RegistrationDate as [Дата регистрации фирмы],
addressOfTheManagement.RegionCode as [Регион клиента],
contractSubType.Name as [Подтип контракта],
customer.Name as [Наименование бенефициара],
customer.OGRN as [ОГРН бенефициара],
customer.INN as [ИНН бенефиуиара],
(CASE WHEN clearSpendingContract.regionCode IS NOT NULL
        THEN clearSpendingContract.regionCode
        ELSE customerLegalAddress.RegionCode END)  as [Регион исполнения контракта],
rbg.ContractSum as [Сумма контракта],
rbg.CountOfWorkedDays as [Срок гарантии],
taxForm.Name as [Система налогообложения принципала],
[HeaderColumnActual].Date as [Актуальная отчётная дата],
[HeaderColumnYear].Date as [Отчетный год]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='1600'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Активы на актуальную отчётную дату (тыс. руб.)]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='1200'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Оборотные активы на актуальную отчётную дату (тыс. руб.)]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='1100'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Внеборотные активы на актуальную отчётную дату (тыс. руб.)]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='1300'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Капитал и резервы на актуальную отчётную дату (тыс. руб.)]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='1300'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Капитал и резервы на актуальную отчётную дату (тыс. руб.)]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='1500'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Краткосрочные обязательства на актуальную отчётную дату (тыс. руб.)]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='1400'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Долгосрочные обязательства на актуальную отчётную дату (тыс. руб.)]
,(SELECT  TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='2400'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Прибыль на актуальную отчётную дату (тыс. руб.)]

-- здесь должна быть Прибыль на актуальную отчётную дату - 12 мес (тыс. руб.)

,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='2400'
    AND frhc.Date = [HeaderColumnYear].Date ) as [Прибыль на конец последнего полного года (тыс. руб.)]
,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='2110'
    AND frhc.Date = [HeaderColumnActual].Date ) as [Выручка на актуальную отчётную дату (тыс. руб.)]

-- Выручка на актуальную отчётную дату "минус" 12 мес. (тыc. руб.)

,(SELECT TOP 1 frc.Value
    FROM FinReportingColumn as frc
    left join FinReportingRow frr on frr.Id = frc.[Row]
    left join MetricName mn on mn.Id = frr.Metric
    left join FinReportingHeaderColumn frhc on frhc.Id = frc.Header
    left join ReportingGroup rg1 on rg1.Id = frr.[Group]
    left join ReportingGroup rg2 on rg2.Id = rg1.ParentGroup
    left join ReportingGroup rg3 on rg3.Id = rg2.ParentGroup
    left join RequestGuaranteePage rgp on rgp.MainGroup = rg3.Id OR rgp.MainGroup = rg2.Id OR rgp.MainGroup = rg1.Id
    left join M_RBG_ReportingAndMetrics as [rbg_rgp] on [rbg_rgp].Child = rgp.Id
    WHERE [rbg_rgp].Parent = rbg.Id
    AND mn.Number='2110'
    AND frhc.Date = [HeaderColumnYear].Date ) as [Выручка на конец последнего полного года (тыс. руб.)]
,(SELECT TOP 1 BuhRow.EndValue
    FROM BuhRow
    left join M_BuhForm_Form1 buhFrom_form1 on buhFrom_form1.Child = BuhRow.Id
    left join M_BuhForm_Form2 buhFrom_form2 on buhFrom_form2.Child = BuhRow.Id
    left join BuhForm on BuhForm.Id = buhFrom_form1.Parent OR BuhForm.Id = buhFrom_form2.Parent
    left join M_BuhForms_Buh appl_buhForms on appl_buhForms.Child = BuhForm.Id
    WHERE appl_buhForms.Parent = rbg.Applicant
    AND BuhRow.Code = 2110
    AND BuhForm.Year = (YEAR([HeaderColumnYear].Date)-1)) as [Выручка на конец последнего полного года "минус" 12 мес. (тыс. руб.)]
,(SELECT TOP 1 BuhRow.StartValue
    FROM BuhRow
    left join M_BuhForm_Form1 buhFrom_form1 on buhFrom_form1.Child = BuhRow.Id
    left join M_BuhForm_Form2 buhFrom_form2 on buhFrom_form2.Child = BuhRow.Id
    left join BuhForm on BuhForm.Id = buhFrom_form1.Parent OR BuhForm.Id = buhFrom_form2.Parent
    left join M_BuhForms_Buh appl_buhForms on appl_buhForms.Child = BuhForm.Id
    WHERE appl_buhForms.Parent = rbg.Applicant
    AND BuhRow.Code = 2110
    AND BuhForm.Year = (YEAR([HeaderColumnYear].Date)-1)) as [Выручка на конец последнего полного года "минус" 24 мес. (тыс. руб.)]
,analytics.s2001 as [Арбитраж. Дела в качестве ответчика. Сумма всех исков  за 12 последних месяцев (в рублях)]
,(SELECT TOP 1
    SUM(StateContract.Sum)
    FROM StateContract
    WHERE StateContract.[Status] = 0 --исполнен
    AND StateContract.Subject = rbg.Applicant
    AND StateContract.Date < DATEADD(year,-1, SYSDATETIME())) as [Сумма контрактов 223-ФЗ, 44-ФЗ заключенных более 12 месяцев назад]
,analytics.S4002 as [Сумма контрактов 223-ФЗ, 44-ФЗ заключенных за 12 последних месяцев]
,(SELECT TOP 1
    COUNT(StateContract.Id)
    FROM StateContract
    WHERE
    StateContract.CustomerINN = customer.INN
    AND StateContract.Subject = rbg.Applicant
    AND StateContract.Date > DATEADD(year,-1, SYSDATETIME())) as [Количество заключенных контрактов с тем же бенефициаром за 12 последних месяцев]
,(SELECT TOP 1
    COUNT(StateContract.Id)
    FROM StateContract
    WHERE
    StateContract.CustomerINN = customer.INN
    AND StateContract.Subject = rbg.Applicant
    AND StateContract.Date < DATEADD(year,-1, SYSDATETIME())) as [Количество заключенных контрактов более 12 месяцев назад с тем же бенефициаром]
,(SELECT TOP 1 SUM(purchase.ContractSum)
    FROM PurchaseOfParticipant as purchase
    left join M_SubjectMSP_POP appl_pop on appl_pop.Child = purchase.Id
    WHERE appl_pop.Parent = rbg.Applicant
    AND purchase.PublicationDate > DATEADD(year,-1, SYSDATETIME())
    ) as [Сумма заявок на контракты 223-ФЗ, 44-ФЗ за 12 последних месяцев]
,(SELECT TOP 1
    COUNT(StateContract.Id)
    FROM StateContract
    WHERE StateContract.Subject = rbg.Applicant
    AND StateContract.Date > DATEADD(year,-1, SYSDATETIME())) as [Количество заключенных контрактов за 12 последних месяцев]
,(SELECT TOP 1
    COUNT(StateContract.Id)
    FROM StateContract
    left join ClearSpendingContract stClearSpendingContract on stClearSpendingContract.Id = StateContract.GovInformation
    WHERE StateContract.Subject = rbg.Applicant
    AND stClearSpendingContract.Id in (
        SELECT csc_products.Parent
        FROM M_CSC_products as csc_products
        left join ClearSpendingProduct csp on csp.Id =csc_products.Child
        left join ClearSpandingCodeNamePair codeName on codeName.Id = csp.OKPD2
        WHERE SUBSTRING(codeName.code,0,charindex('.',codeName.code,0)) in (
            SELECT okpd2.Number
            FROM OKPD2Short as okpd2
            left join M_RBG_CurrentOKPDs [rbg_currentOKPDS] on [rbg_currentOKPDS].Child = okpd2.Id
            WHERE [rbg_currentOKPDS].Parent = rbg.Id
            )
        )
    AND StateContract.Date > DATEADD(year,-1, SYSDATETIME())) as [Количество заключенных контрактов за 12 последних месяцев того же типа]
,(SELECT TOP 1
    COUNT(StateContract.Id)
    FROM StateContract
    left join ClearSpendingContract stClearSpendingContract on stClearSpendingContract.Id = StateContract.GovInformation
    WHERE StateContract.Subject = rbg.Applicant
    AND stClearSpendingContract.Id in (
        SELECT csc_products.Parent
        FROM M_CSC_products as csc_products
        left join ClearSpendingProduct csp on csp.Id =csc_products.Child
        left join ClearSpandingCodeNamePair codeName on codeName.Id = csp.OKPD2
        WHERE  SUBSTRING(codeName.code,0,charindex('.',codeName.code,0)) in (
            SELECT okpd2.Number
            FROM OKPD2Short as okpd2
            left join M_RBG_CurrentOKPDs [rbg_currentOKPDS] on [rbg_currentOKPDS].Child = okpd2.Id
            WHERE [rbg_currentOKPDS].Parent = rbg.Id
            )
        )
    AND StateContract.Date < DATEADD(year,-1, SYSDATETIME())) as [Количество заключенных контрактов более 12 месяцев назад того же типа]
,(SELECT TOP 1 COUNT(purchase.Id)
    FROM PurchaseOfParticipant as purchase
    left join M_SubjectMSP_POP appl_pop on appl_pop.Child = purchase.Id
    WHERE appl_pop.Parent = rbg.Applicant
    AND purchase.PublicationDate > DATEADD(year,-1, SYSDATETIME())
    ) as [Количество заявок на контракты 223-ФЗ, 44-ФЗ за 12 последних месяцев]
,okved.Code as [ОКВЭД принципала]
,riskClass.Name as [Score segment]

--PD

,rbg.RiskRate as [Балл]
FROM RequestBankGuarantee rbg
left join RiskClass riskClass on riskClass.Id = rbg.RiskKlass
left join TaxForm taxForm on rbg.TaxForm=taxForm.Id
left join SubjectMSP applicant on rbg.Applicant=applicant.Id
left join Analytics analytics on analytics.Id = applicant.Analitics
left join GuaranteeType guaranteeType on rbg.Type=guaranteeType.Id
left join Customer customer on rbg.Customer=customer.Id
left join OkvedMSP okved on okved.Id = customer.OKVED
left join AddressKLADR addressOfTheManagement on applicant.AddressOfTheManagement = addressOfTheManagement.Id
left join ContractSubType contractSubType on contractSubType.Id = rbg.SubTypeContract
left join AddressKLADR customerLegalAddress on customerLegalAddress.Id = customer.LegalAddress
left join ClearSpendingContract clearSpendingContract on clearSpendingContract.Id = rbg.GovInformation

left join FinReportingHeaderColumn [HeaderColumnActual] on [HeaderColumnActual].Id = ((SELECT TOP 1 [HeaderColumn].Id
FROM FinReportingHeaderColumn as [HeaderColumn]
WHERE HeaderColumn.Id in (
    SELECT  rgp_head.Child
    FROM M_RGP_Headers as rgp_head
    WHERE rgp_head.Parent in (
        SELECT [rgp].Id FROM RequestGuaranteePage as [rgp]
        WHERE [rgp].Id in (
            SELECT [rbg_rgp].Child FROM M_RBG_ReportingAndMetrics as [rbg_rgp]
            WHERE [rbg_rgp].Parent  = rbg.Id
            )
        )
    )
    ORDER BY [HeaderColumn].Date DESC))
left join FinReportingHeaderColumn [HeaderColumnYear] on [HeaderColumnYear].Id = ((SELECT TOP 1 [HeaderColumn].Id
FROM FinReportingHeaderColumn as [HeaderColumn]
WHERE HeaderColumn.Id in (
    SELECT rgp_head.Child
    FROM M_RGP_Headers as rgp_head
    WHERE rgp_head.Parent in (
        SELECT [rgp].Id FROM RequestGuaranteePage as [rgp]
        WHERE [rgp].Id in (
            SELECT [rbg_rgp].Child FROM M_RBG_ReportingAndMetrics as [rbg_rgp]
            WHERE [rbg_rgp].Parent  = rbg.Id
            )
        )
    )
    AND MONTH(HeaderColumn.Date) = 12
    ORDER BY [HeaderColumn].Date DESC))
WHERE rbg.RiskRate > 0