Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- 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
You can’t perform that action at this time.