Untitled
unknown
sqlserver
2 years ago
5.8 kB
8
Indexable
declare @date_start date = '2023-07-01'; declare @date_end date = '2023-07-27'; with cte_payment_way as ( select distinct eoth.JOURNALID , first_value(eoth.NEWVALUE) over (partition by eoth.journalid order by eoth.CREATEDDATETIME asc) as paymenttype_first from [StagingArea].[S088].[ESHOPORDERTABLEHISTORY] eoth join [StagingArea].[S088].[TSM_ESHOPORDERTABLE] eot on eoth.JOURNALID = eot.JOURNALID where eoth.FIELDLABEL = 'Способ оплаты' and eot.PAYMENTTYPEID = 0 -- смешанная оплата ) , cte_orders as ( select eot.ORDERID , year(cast(dateadd(hh, +3, eot.ORDERDATETIME) as date)) as year , month(cast(dateadd(hh, +3, eot.ORDERDATETIME) as date)) as month , cast(dateadd(hh, +3, eot.ORDERDATETIME) as date) as date , eot.DISCCARDHOLDERID as holderid , case when DISCCARDTYPEID like 'Black%' then 'Black' when DISCCARDTYPEID like 'Orange%' then 'Orange' when DISCCARDTYPEID like 'White%' then 'White' else 'No loyal' end as client_type , eof.ISVIPONLINEORDER as is_vip , cast(eot.AMOUNTGROSS as numeric) as gross , cast(eot.AMOUNTAPPROVED as numeric) as approved , cast(eot.AMOUNTNET as numeric) as net , case when eot.AMOUNTGROSS > 0 then 1 else 0 end as gross_order , case when eot.AMOUNTAPPROVED > 0 then 1 else 0 end as approved_order , case when eot.AMOUNTNET > 0 then 1 else 0 end as net_order , case when eot.PAYMENTTYPEID in (1, 7) then 'Post-payment' when eot.PAYMENTTYPEID in (8, 9, 11) then 'Pre-payment' when eot.PAYMENTTYPEID = 0 and pw.paymenttype_first in (1, 7) then 'Post-payment' when eot.PAYMENTTYPEID = 0 and pw.paymenttype_first in (8, 9, 11) then 'Pre-payment' else 'None' end as payment_way , case when eot.ORDERSOURCE in ('5') then 'iOS App' when eot.ORDERSOURCE in ('10') then 'Android App' when eot.ORDERSOURCE in ('1') then 'Website' when eot.ORDERSOURCE in ('3') then 'CC Chat' when eot.ORDERSOURCE in ('2', '4', '6', '7', '11', '12','13') then 'CC Phone' when eot.ORDERSOURCE in ('9') then 'Mobile Assistant' end as platform , case when TXT in ( 'Барвиха - Самовывоз', 'ДЛТ - заказ из торгового зала', 'ДЛТ - Самовывоз', 'Екатеринбург - Самовывоз (DPD)', 'Екатеринбург - Самовывоз (Pony)', 'Казань - Самовывоз (DPD)', 'Казань - Самовывоз (Pony)', 'Краснодар - Самовывоз (DPD)', 'Краснодар - Самовывоз (Pony)', 'Кутузовский - Самовывоз', 'Ленинградский - Самовывоз (Алкон)', 'Ростов-на-Дону - Самовывоз (DPD)', 'Ростов-на-Дону - Самовывоз (Pony)', 'Самара - Самовывоз (DPD)', 'Самара - Самовывоз (Pony)', 'Сочи - Самовывоз (DPD)', 'Сочи - Самовывоз (Pony)', 'ЦУМ - заказ из торгового зала', 'ЦУМ - Самовывоз', 'ЦУМ - VIP 4 ПВЗ', 'Шуваловский - Самовывоз' ) then 'ПВЗ' when TXT in ( 'Екатеринбург - ССД (Pony)', 'СПБ ЦУМ - собствен.курьеры', 'Казань - ССД (DPD)', 'Екатеринбург - ССД (DPD)', 'Краснодар - ССД (DPD)', 'ЦУМ - ССД (Претензия)', 'Ростов-на-Дону - ССД (DPD)', 'Новосибирск - ССД (DPD)', 'ДЛТ - Экспресс', 'VIP доставка СПб', 'VIP доставка МСК', 'Сочи - ССД (Pony)', 'Самара - ССД (Pony)', 'Самара - ССД (DPD)', 'Санкт-Петербург ССД Экспресс', 'Краснодар - ССД (Pony)', 'Сочи – ССД (DPD)', 'Казань - ССД (Pony)', 'Ростов-на-Дону - ССД (Pony)', 'Новосибирск - ССД (PONY)', 'ЦУМ - ССД', 'ЦУМ - Экспресс', 'Москва ССД', 'VIP Москва ССД', 'VIP Санкт-Петербург ССД', 'Москва ССД Экспресс', 'Санкт-Петербург ССД', 'Москва ССД (Претензия)' ) then 'ССД' when TXT in ( 'Далли Сервис - дальняя МО (до 100 км)', 'Далли Сервис - Москва и ближняя МО (до 30 км)', 'КСЭ VIP', 'TopDelivery - Москва и ближняя МО (до 30км)', 'Top Delivery РФ', 'Pony Express', 'Pony Армения RUS', 'DPD Казахстан KAZ', 'DPD Казахстан RUS', 'EMS - до 300 000 руб.', 'Pony Армения ARM', 'DPD', 'DPD авиа', 'DPD Беларусь' ) then 'Агенты' when TXT = 'Без доставки' then 'Без доставки' when TXT = 'Свободный адрес' then 'Свободный адрес' else TXT end as delivery_method from [StagingArea].[S088].[TSM_ESHOPORDERTABLE] eot left join cte_payment_way pw on eot.JOURNALID = pw.JOURNALID left join [StagingArea].[S088].[DLVMODE] dm on eot.DLVMODEID = dm.CODE left join [StagingArea].[S088].[ESHOPORDERFEATURE] eof on eot.ORDERID = eof.ORDERID where eot.DATAAREAID = 'tsm' and cast(dateadd(hh, +3, eot.ORDERDATETIME) as date) >= @date_start and cast(dateadd(hh, +3, eot.ORDERDATETIME) as date) < @date_end and eot.REJECTREASON != 'ИМ19' and eot.AMOUNTGROSS > 0 ) select date , delivery_method , count(distinct holderid) as holderid , sum(gross) as gross , sum(approved) as approved , sum(net) as net , sum(gross_order) as gross_order , sum(approved_order) as approved_order , sum(net_order) as net_order from cte_orders group by date , delivery_method
Editor is loading...