Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Размер ежемесячной абонентской платы по итогам перевода с пилота (с одной точки) - руб
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement