Kirish I. Umumiy qism

FROM costs

FULL JOIN [sessions]

ON costs.[date] = [sessions].[date] AND costs.sourceMedium = [sessions].sourceMedium AND costs.campaign = [sessions].campaign

GROUP BY ISNULL(costs.[date], [sessions].[date]), ISNULL(costs.sourceMedium, [sessions].sourceMedium), ISNULL(costs.campaign, [sessions].campaign)

)

-- Запрашиваем заказы и доход

, orders AS (

SELECT

[date]

, sourceMedium

, campaign

, SUM(orders) AS 'orders'

, SUM(revenue) AS 'revenue'

FROM [Crm].[dbo].[orders] WITH (NOLOCK)

WHERE [date] BETWEEN @startDate AND @endDate

AND sourceMedium IN ('google / cpc', 'yandex / cpc')

GROUP BY [date], [sourceMedium], [campaign]

)

- Trafik ma'lumotlarini buyurtma ma'lumotlari bilan birlashtiring

, join_table AS (

SELECT

ISNULL(costs_sessions.[date], orders.[date]) AS 'date'

, ISNULL(costs_sessions.sourceMedium, orders.sourceMedium) AS 'sourceMedium'

, ISNULL(costs_sessions.campaign, orders.campaign) AS 'campaign'

, ISNULL(SUM(costs_sessions.cost), 0) AS 'cost'

, ISNULL(SUM(costs_sessions.impressions), 0) AS 'impressions'

, ISNULL(SUM(costs_sessions.clicks), 0) AS 'clicks'

, ISNULL(SUM(costs_sessions.[sessions]), 0) AS 'sessions'

,ISNULL(SUM(orders.orders), 0) AS 'orders'

, ISNULL(SUM(orders.revenue), 0) AS 'revenue'