phụ-lục-1A-1B-1C.sql

-- 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