Skip to content

Instantly share code, notes, and snippets.

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