# Размер ежемесячной абонентской платы по итогам перевода с пилота (с одной точки) - руб

SELECT
  Partner.PartnerID                            AS ID,
  Partner.Title                                AS 'PartnerName',
  (COUNT(DISTINCT SalesOutlet.SalesOutletID))     SalesOutlets,
  T.Amount AS Summ,
  CAST((T.Amount / (COUNT(DISTINCT SalesOutlet.SalesOutletID))) as DECIMAL(16,2)) AS KPI

FROM
  Partner
  JOIN BusinessType USING (BusinessTypeID)
  JOIN SalesOutlet USING (PartnerID)
  JOIN LegalEntity USING (PartnerID)
  JOIN PartnerBill ON PartnerBill.LegalEntityID = LegalEntity.LegalEntityID
  JOIN City ON City.CityID = Partner.CityID
  JOIN (SELECT
          Partner.PartnerID,
          SUM(PartnerBill.Amount) AS Amount
        FROM
          Partner
          JOIN LegalEntity USING (PartnerID)
          JOIN PartnerBill ON PartnerBill.LegalEntityID = LegalEntity.LegalEntityID
          JOIN PersonalPartnerData USING (PersonalPartnerDataID)
        WHERE
          PartnerStateID != 8
          AND PartnerBill.State = 4
          AND (PartnerBill.BillType = 1 OR PartnerBill.BillType = 4)
          AND MONTH(PartnerBill.BillPeriod) = MONTH(PersonalPartnerData.PartnerCreationTimestamp)
        GROUP BY Partner.PartnerID
       ) AS T USING (PartnerID)

WHERE
  PartnerStateID = 1
  AND SalesOutlet.Hide = 0
  AND City.CountryID = 1
  AND Partner.BusinessTypeID != 159
GROUP BY Partner.PartnerID