-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment