DECLARE @subjectStr VARCHAR(16) = 'Arenda'
    , @table NVARCHAR(MAX) = ''
    , @tableHTML NVARCHAR(MAX)
;


WITH
    cte(Name, Phone, Description, ObjectDescription, SummaOwedTotal) AS (
        SELECT MAX(c.Name)
            , MAX(c.Phone)
            , MIN(dt.Description)
            , MIN(o.ObjectDescription)
            , SUM(a.SummaSaldoBegin)
              + (MAX(p.PlanUtilityPayment) + SUM(SummaReceipt)) AS 'SummaOwedTotal'
        FROM mart.ArendaSAPBalance a
            LEFT JOIN sap.Customer c
                ON a.CustomerID = c.CustomerID
            INNER JOIN dim.Object o
                ON o.ObjectID = a.ObjectID
            INNER JOIN dim.DebtType dt
                ON dt.DebtTypeID = a.DebtTypeID
            INNER JOIN dbo.v_Month m
                ON a.MonthID = m.MonthID
            LEFT JOIN dwh.xls.ArendaObjectPlan p
                ON p.ObjectID = a.ObjectID
                    AND p.ContractArendaTypeID = a.ContractArendaTypeID
                    AND p.CustomerID = a.CustomerID
                    AND p.MonthID = a.MonthID
                    AND p.ContractArendaTypeID = 1
                    OR (
                        p.ObjectID = a.ObjectID
                           AND p.ContractArendaTypeID = a.ContractArendaTypeID
                           AND p.MonthID = a.MonthID
                           AND p.ContractArendaTypeID = 4
                    )
        WHERE --ContractHeadEndDate > '2018-05-01'
            a.ContractArendaTypeID IN ( 1, 4 )
            AND a.DebtTypeID IN ( 4, 11 )
            AND m.Month = FORMAT(GETDATE(), 'yyyyMM')
            AND p.PlanUtilityPayment > 0
            AND a.SummaSaldoBegin + (p.PlanUtilityPayment + SummaReceipt) > 0
        GROUP BY c.CustomerID

        UNION SELECT c.Name
            , c.Phone
            , dt.Description
            , o.ObjectDescription
            , a.SummaSaldoBegin + (p.ArendaPaySum + SummaReceipt) AS 'SummaOwedTotal'
        FROM mart.ArendaSAPBalance a
            LEFT JOIN sap.Customer c
                ON a.CustomerID = c.CustomerID
            INNER JOIN dim.Object o
                ON o.ObjectID = a.ObjectID
            INNER JOIN dim.DebtType dt
                ON dt.DebtTypeID = a.DebtTypeID
            INNER JOIN dim.ContractArendaType cat
                ON cat.ContractArendaTypeID = a.ContractArendaTypeID
            INNER JOIN dbo.v_Month m
                ON a.MonthID = m.MonthID
            LEFT JOIN dwh.xls.ArendaObjectPlan p
                ON p.ObjectID = a.ObjectID
                    AND p.ContractArendaTypeID = a.ContractArendaTypeID
                    AND p.CustomerID = a.CustomerID
                    AND p.MonthID = a.MonthID
                    AND p.ContractArendaTypeID = 1
                    OR (
                        p.ObjectID = a.ObjectID
                           AND p.ContractArendaTypeID = a.ContractArendaTypeID
                           AND p.MonthID = a.MonthID
                           AND p.ContractArendaTypeID = 4
                    )
        WHERE ContractHeadEndDate > GETDATE()
            AND a.ContractArendaTypeID IN ( 1, 4 )
            AND a.DebtTypeID IN ( 2 )
            AND m.Month = FORMAT(GETDATE(), 'yyyyMM')
            AND a.SummaSaldoBegin + (p.ArendaPaySum + SummaReceipt) > 0
    )
SELECT @table = @table + '<tr>'
    + '<td>' + Name + '</td>'
    + '<td>' + Phone + '</td>'
    + '<td>' + Description + '</td>'
    + '<td>' + ObjectDescription + '</td>'
    + '<td>' + CONVERT(VARCHAR(64), SummaOwedTotal) + '</td>'
FROM cte;

SET @tableHTML = N'
<h3>
    <font color="black"> Добрый день! В приложении список арендаторов-должников, которым будут отправлены СМС уведомления. Просим подтвердить корректность данных в выгрузке, в противном случае СМС отправлены не будут! </h3>'
+N' <table border="1" align="center" cellpadding="2" cellspacing="0" style="text-align:center;">'
+N'     <tr style="font-size:12px;">
            <th>Name</th>
            <th>Phone</th>
            <th>Description</th>
            <th>ObjectDescription</th>
            <th>SummaOwedTotal</th>
        </tr>' + @table
+N' </table>'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DWH1',
    @recipients = 'bogdanova.maria@zoloto585.ru; kim.lestat@zoloto585.ru',--'; dvoryanov.dmitry@zoloto585.ru',
    @subject = @subjectStr,
    @body = @tableHTML,
    @body_format = 'HTML'
;