phụ-lục-1D-1E.sql

-- Cập nhật cột SomayHVN_mapping ở bảng registration
update top (100) registration_2018 set SomayHVN_mapping = 'JF86E0036270'

update top (100) registration_2019 set SomayHVN_mapping = 'JF86E0036270'

-- Tạo bảng trung gian
create table report_1de (
    DEALER_CODE varchar(50), 
    deliver_tinh varchar(50), 
    Arrival_month int, 
    Arrival_year int, 
    Engine varchar(50),
    SomayHVN_mapping varchar(50),
    category varchar(50),
    Maker_final varchar(50),
    Model_final varchar(50), 
    sale_tinh varchar(50),
    thang int,
    nam int
)

-- Thêm dữ liệu
insert into report_1de 
SELECT *
FROM
(
    select deliver_2018.DEALER_CODE, map_tinh.Tinh, deliver_2018.Arrival_month, deliver_2018.Arrival_year, deliver_2018.Engine
    from Delivery_raw.dbo.Deliver_raw2018 AS deliver_2018 
	left join Delivery_raw.dbo.map_tinh AS map_tinh on deliver_2018.DEALER_CODE = map_tinh.Code

) delivery

JOIN

(

    select SomayHVN_mapping, category, Maker_final, Model_final, tinh, thang, nam
    from registration_raw.dbo.registration_raw2018 AS registration_2018

    union all

    select SomayHVN_mapping, category, Maker_final, Model_final, tinh, thang, nam
    from registration_raw.dbo.registration_raw2019 registration_2019

) sale

on delivery.Engine = sale.SomayHVN_mapping

insert into report_1de 
SELECT *
FROM
(
    select deliver_2018.DEALER_CODE, map_tinh.Tinh, deliver_2018.Arrival_month, deliver_2018.Arrival_year, deliver_2018.Engine
    from deliver_2018 left join map_tinh on deliver_2018.DEALER_CODE = map_tinh.Code

    union all

    select deliver_2019.DEALER_CODE, map_tinh.Tinh, deliver_2019.Arrival_month, deliver_2019.Arrival_year, deliver_2019.Engine
    from deliver_2019 left join map_tinh on deliver_2019.DEALER_CODE = map_tinh.Code
) delivery

JOIN

(
    select SomayHVN_mapping, category, Maker_final, Model_final, tinh, thang, nam
    from registration_2018

    union all

    select SomayHVN_mapping, category, Maker_final, Model_final, tinh, thang, nam
    from registration_2019
) sale

on delivery.Engine = sale.SomayHVN_mapping
where delivery.Engine = 'JF86E0036270'

----------------------------------------- Phụ lục 1D ----------------------------------------------------

-- Query báo cáo 1D - Table 4
SELECT *
FROM
(
    select DEALER_CODE, deliver_tinh, Arrival_month, Arrival_year, count(1) AS allo_quantity
    from report_1de_new
    --where DEALER_CODE = 10001
    group by DEALER_CODE, deliver_tinh, Arrival_month, Arrival_year
    --order by Arrival_year, Arrival_month, deliver_tinh
) AS deliver_info

left join

(
    select DEALER_CODE, Arrival_month, Arrival_year, sale_tinh, thang, nam, count(1) AS sale_amount_in_tinh
    from report_1de_new
    --where DEALER_CODE = 10001
    group by DEALER_CODE, Arrival_month, Arrival_year, sale_tinh, thang, nam
   -- order by Arrival_month, Arrival_year, sale_tinh, nam, thang
) AS sale_info

ON deliver_info.DEALER_CODE = sale_info.DEALER_CODE and deliver_info.Arrival_month = sale_info.Arrival_month 
and deliver_info.Arrival_year = sale_info.Arrival_year

--where deliver_tinh = 'Ha Noi'

order by deliver_info.DEALER_CODE,  deliver_info.Arrival_year, deliver_info.Arrival_month, deliver_tinh, sale_tinh, nam, thang


-- Query báo cáo 1D - Table 5
SELECT *
FROM
(
    select deliver_tinh, Arrival_month, Arrival_year, count(1) AS allo_quantity
    from report_1de_new
    --where DEALER_CODE = 10001
    group by deliver_tinh, Arrival_month, Arrival_year
    --order by Arrival_year, Arrival_month, deliver_tinh
) AS deliver_info

left join

(
    select Arrival_month, Arrival_year, deliver_tinh, CASE WHEN sale_tinh = deliver_tinh then sale_tinh else 'Other' end AS tinh, 
    thang, nam, count(1) AS sale_amount_in_tinh
    from report_1de_new
    --where DEALER_CODE = 10001
    group by Arrival_month, Arrival_year, deliver_tinh, CASE WHEN sale_tinh = deliver_tinh then sale_tinh else 'Other' end, thang, nam
   -- order by Arrival_month, Arrival_year, sale_tinh, nam, thang
) AS sale_info

ON deliver_info.deliver_tinh = sale_info.deliver_tinh and deliver_info.Arrival_month = sale_info.Arrival_month 
and deliver_info.Arrival_year = sale_info.Arrival_year

--where deliver_info.deliver_tinh = 'Ha Noi'

order by deliver_info.Arrival_year, deliver_info.Arrival_month, deliver_info.deliver_tinh, nam, thang, sale_info.tinh

-- Query báo cáo 1D - Table 6
SELECT *
FROM
(
    select DEALER_CODE, deliver_tinh, Arrival_month, Arrival_year, count(1) AS allo_quantity
    from report_1de_new
    --where DEALER_CODE = 10001
    group by DEALER_CODE, deliver_tinh, Arrival_month, Arrival_year
    --order by Arrival_year, Arrival_month, deliver_tinh
) AS deliver_info

left join

(
    select DEALER_CODE, Arrival_month, Arrival_year, CASE WHEN sale_tinh = deliver_tinh then sale_tinh else 'Other' end AS tinh, 
    thang, nam, count(1) AS sale_amount_in_tinh
    from report_1de_new
    --where DEALER_CODE = 10001
    group by DEALER_CODE, Arrival_month, Arrival_year, CASE WHEN sale_tinh = deliver_tinh then sale_tinh else 'Other' end, thang, nam
   -- order by Arrival_month, Arrival_year, sale_tinh, nam, thang
) AS sale_info

ON deliver_info.DEALER_CODE = sale_info.DEALER_CODE and deliver_info.Arrival_month = sale_info.Arrival_month 
and deliver_info.Arrival_year = sale_info.Arrival_year

--where deliver_tinh = 'Ha Noi'

order by deliver_info.DEALER_CODE,  deliver_info.Arrival_year, deliver_info.Arrival_month, deliver_tinh, tinh, nam, thang

----------------------------------------- Hết 1D ----------------------------------------------------



-------------------------------------------- Phụ lục 1E --------------------------------------------------------

-- Thêm cột sale_quan vào bảng report_1de
alter table report_1de add sale_quan int

-- Update các giá trị random integer cho cột sale_quan
UPDATE report_1de
SET sale_quan = abs(checksum(NewId()) % 50)

-- Table 7
SELECT *
FROM
(
    select deliver_tinh, Arrival_month, Arrival_year, count(1) AS allo_quantity
    from report_1de_new
    --where DEALER_CODE = 10001
    group by deliver_tinh, Arrival_month, Arrival_year
    --order by Arrival_year, Arrival_month, deliver_tinh
) AS deliver_info

left join

(
    select Arrival_month, Arrival_year, deliver_tinh, sale_tinh, sale_quan, thang, nam, count(1) AS sale_amount_in_tinh
    from report_1de_new
    --where DEALER_CODE = 10001
    group by Arrival_month, Arrival_year, deliver_tinh, sale_tinh, sale_quan, thang, nam
   -- order by Arrival_month, Arrival_year, sale_tinh, nam, thang
) AS sale_info

ON deliver_info.deliver_tinh = sale_info.deliver_tinh and deliver_info.Arrival_month = sale_info.Arrival_month 
and deliver_info.Arrival_year = sale_info.Arrival_year

--where deliver_info.deliver_tinh = 'Ha Noi' and deliver_info.Arrival_year = 2018 and deliver_info.Arrival_month = 5

order by deliver_info.Arrival_year, deliver_info.Arrival_month, deliver_info.deliver_tinh, nam, thang, sale_info.sale_tinh, sale_info.sale_quan


-- Table 8
SELECT *
FROM
(
    select DEALER_CODE, deliver_tinh, count(1) AS allo_quantity
    from report_1de_new
    --where DEALER_CODE = 10001
    group by DEALER_CODE, deliver_tinh
    --order by Arrival_year, Arrival_month, deliver_tinh
) AS deliver_info

left join

(
    select DEALER_CODE, deliver_tinh, sale_tinh, sale_quan, thang, nam, count(1) AS sale_amount_in_tinh
    from report_1de_new
    --where DEALER_CODE = 10001
    group by DEALER_CODE, deliver_tinh, sale_tinh, sale_quan, thang, nam
   -- order by Arrival_month, Arrival_year, sale_tinh, nam, thang
) AS sale_info

ON deliver_info.DEALER_CODE = sale_info.DEALER_CODE

--where deliver_info.DEALER_CODE = 10001

order by deliver_info.DEALER_CODE, deliver_info.deliver_tinh, nam, thang, sale_info.sale_tinh, sale_info.sale_quan

-- Table 9
SELECT *
FROM
(
    select deliver_tinh, count(1) AS allo_quantity
    from report_1de_new
    --where deliver_tinh
    group by deliver_tinh
    --order by Arrival_year, Arrival_month, deliver_tinh
) AS deliver_info

left join

(
    select deliver_tinh, case when sale_tinh = deliver_tinh then sale_tinh else 'Other' end as sale_tinh, 
   case when sale_tinh = deliver_tinh then sale_quan else -1 end as sale_quan, thang, nam, count(1) AS sale_amount_in_tinh
    from report_1de_new
    --where deliver_tinh
    group by deliver_tinh, case when sale_tinh = deliver_tinh then sale_tinh else 'Other' end, 
    case when sale_tinh = deliver_tinh then sale_quan else -1 end, thang, nam
   -- order by Arrival_month, Arrival_year, sale_tinh, nam, thang
) AS sale_info

ON deliver_info.deliver_tinh = sale_info.deliver_tinh

--where deliver_info.deliver_tinh = 'AN GIANG'

order by deliver_info.deliver_tinh, nam, thang, sale_info.sale_tinh, sale_info.sale_quan