-- Tạo bảng | |
CREATE TABLE [dbo].[deliver_2018]( | |
[DELIVERY_NO] [varchar](50) NULL, | |
[DEALER_CODE] [int] NULL, | |
[MODEL_CODE] [varchar](50) NULL, | |
[TYPE_CODE] [varchar](50) NULL, | |
[COLOR_CODE] [varchar](50) NULL, | |
[F_HEADER] [varchar](50) NULL, | |
[F_NO] [int] NULL, | |
[E_HEADER] [varchar](50) NULL, | |
[E_NO] [int] NULL, | |
[Engine] [varchar](50) NULL, | |
[Delivery_month] [int] NULL, | |
[Delivery_year] [int] NULL, | |
[Arrival_month] [int] NULL, | |
[Arrival_year] [int] NULL, | |
[Model_map] [varchar](50) NULL | |
) ON [PRIMARY] | |
CREATE TABLE [dbo].[registration_2018]( | |
[STT] [int] NULL, | |
[ngay_dk] [varchar](50) NULL, | |
[bien] [varchar](50) NULL, | |
[bien_cu] [varchar](50) NULL, | |
[chu_xe] [varchar](50) NULL, | |
[loai_xe] [varchar](50) NULL, | |
[nhan_hieu] [varchar](50) NULL, | |
[so_loai] [varchar](50) NULL, | |
[so_khung] [varchar](50) NULL, | |
[so_may] [varchar](50) NULL, | |
[ngay_dk_dau] [varchar](50) NULL, | |
[tinh_1] [varchar](50) NULL, | |
[quan_1] [varchar](50) NULL, | |
[phuong_xa] [varchar](50) NULL, | |
[mau] [varchar](50) NULL, | |
[cm] [varchar](50) NULL, | |
[so_nguoi_cho] [int] NULL, | |
[dangky_moi] [varchar](50) NULL, | |
[Model_final] [varchar](50) NULL, | |
[Maker_final] [varchar](50) NULL, | |
[Maker_category] [varchar](50) NULL, | |
[Maker_detail] [varchar](50) NULL, | |
[Brand_name] [varchar](50) NULL, | |
[Model_name] [varchar](50) NULL, | |
[Displa_group] [varchar](50) NULL, | |
[Displa] [varchar](50) NULL, | |
[category] [varchar](50) NULL, | |
[Segment] [varchar](50) NULL, | |
[tinh] [varchar](50) NULL, | |
[quan] [varchar](50) NULL, | |
[ngay] [int] NULL, | |
[thang] [int] NULL, | |
[nam] [int] NULL, | |
[SomayHVN_mapping] [varchar](50) NULL | |
) ON [PRIMARY] | |
CREATE TABLE [dbo].[registration_2019]( | |
[STT] [int] NULL, | |
[ngay_dk] [varchar](50) NULL, | |
[bien] [varchar](50) NULL, | |
[bien_cu] [varchar](50) NULL, | |
[chu_xe] [varchar](50) NULL, | |
[loai_xe] [varchar](50) NULL, | |
[nhan_hieu] [varchar](50) NULL, | |
[so_loai] [varchar](50) NULL, | |
[so_khung] [varchar](50) NULL, | |
[so_may] [varchar](50) NULL, | |
[ngay_dk_dau] [varchar](50) NULL, | |
[tinh_1] [varchar](50) NULL, | |
[quan_1] [varchar](50) NULL, | |
[phuong_xa] [varchar](50) NULL, | |
[mau] [varchar](50) NULL, | |
[cm] [varchar](50) NULL, | |
[so_nguoi_cho] [int] NULL, | |
[dangky_moi] [varchar](50) NULL, | |
[Model_final] [varchar](50) NULL, | |
[Maker_final] [varchar](50) NULL, | |
[Maker_category] [varchar](50) NULL, | |
[Maker_detail] [varchar](50) NULL, | |
[Brand_name] [varchar](50) NULL, | |
[Model_name] [varchar](50) NULL, | |
[Displa_group] [varchar](50) NULL, | |
[Displa] [varchar](50) NULL, | |
[category] [varchar](50) NULL, | |
[Segment] [varchar](50) NULL, | |
[tinh] [varchar](50) NULL, | |
[quan] [varchar](50) NULL, | |
[ngay] [int] NULL, | |
[thang] [int] NULL, | |
[nam] [int] NULL, | |
[SomayHVN_mapping] [varchar](50) NULL | |
) ON [PRIMARY] | |
create table map_tinh ( | |
Code int, | |
Tinh varchar(50) | |
) | |
-- Mockup dữ liệu 2018 | |
DECLARE @Counter INT | |
SET @Counter=1 | |
WHILE ( @Counter <= 1000) | |
BEGIN | |
BULK INSERT deliver_2018 FROM '/deliver-2018.csv' | |
WITH ( | |
FIRSTROW = 2, | |
FIELDTERMINATOR = ',', | |
ROWTERMINATOR = '\n' | |
); | |
SET @Counter = @Counter + 1 | |
END | |
DECLARE @Counter INT | |
SET @Counter=1 | |
WHILE ( @Counter <= 2010) | |
BEGIN | |
BULK INSERT registration_2018 FROM '/regis-2018.csv' | |
WITH ( | |
FIRSTROW = 2, | |
FIELDTERMINATOR = ',', | |
ROWTERMINATOR = '\n' | |
); | |
SET @Counter = @Counter + 1 | |
END | |
-- Mockup dữ liệu 2019 | |
DECLARE @Counter INT | |
SET @Counter=1 | |
WHILE ( @Counter <= 2010) | |
BEGIN | |
BULK INSERT registration_2019 FROM '/regis-2019.csv' | |
WITH ( | |
FIRSTROW = 2, | |
FIELDTERMINATOR = ',', | |
ROWTERMINATOR = '\n' | |
); | |
SET @Counter = @Counter + 1 | |
END | |
-- Tạo bảng | |
create table dbo.report_1abc ( | |
Maker_final varchar(50), | |
Model_final varchar(50), | |
category varchar(50), | |
tinh_1 varchar(50), | |
quan_1 varchar(50), | |
ngay int, | |
thang int, | |
nam int | |
) | |
-- Mockup dữ liệu tỉnh | |
BULK INSERT map_tinh FROM '/map-tinh.csv' | |
WITH ( | |
FIRSTROW = 2, | |
FIELDTERMINATOR = ',', | |
ROWTERMINATOR = '\n' | |
); | |
-- insert dữ liệu | |
insert into dbo.report_1abc | |
select Maker_final, Model_final, category, tinh, quan, ngay, thang, nam | |
from dbo.registration_raw2018 | |
where dangky_moi = 'New' | |
union all | |
select Maker_final, Model_final, category, tinh, quan, ngay, thang, nam | |
from dbo.registration_raw2019 | |
where dangky_moi = 'New' | |
-- Query báo cáo 1A | |
select Maker_final, Model_final, category, tinh, count(1) AS total_sales_by_tinh, | |
sum( count(1) ) over (partition by Maker_final, Model_final, category) | |
from report_1abc | |
where Maker_final IN ('China110', 'EM') | |
and Model_final IN ('Exciter') | |
group by Maker_final, Model_final, category, tinh | |
-- Query báo cáo 1B | |
select Maker_final, Model_final, category, tinh, ngay, thang, nam, | |
count(1) AS total_sales_by_tinh, | |
sum( count(1) ) over (partition by Maker_final, Model_final, category, ngay, thang, nam) | |
from report_1abc | |
group by Maker_final, Model_final, category, tinh, ngay, thang, nam | |
-- Query báo cáo 1C | |
select Maker_final, Model_final, category, tinh, quan, ngay, thang, nam, | |
count(1) AS total_sales_by_tinh | |
from report_1abc | |
group by Maker_final, Model_final, category, tinh, quan, ngay, thang, nam |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment