From 31d4558821bfffbc07d5fae54fa063c019a6f7a9 Mon Sep 17 00:00:00 2001 From: Andrew Guschin Date: Tue, 12 Apr 2022 12:42:41 +0400 Subject: =?UTF-8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=B8=D0=BB=20=D0=B4?= =?UTF-8?q?=D0=B5=D1=81=D1=8F=D1=82=D0=BE=D0=B5=20=D0=B7=D0=B0=D0=B4=D0=B0?= =?UTF-8?q?=D0=BD=D0=B8=D0=B5?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- task10-generate.sql | 164 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 164 insertions(+) create mode 100644 task10-generate.sql (limited to 'task10-generate.sql') diff --git a/task10-generate.sql b/task10-generate.sql new file mode 100644 index 0000000..14b8e34 --- /dev/null +++ b/task10-generate.sql @@ -0,0 +1,164 @@ +-------------- +-- dim_date -- +-------------- + +SET LANGUAGE Russian +use shipping_storage + +DECLARE @startday datetime = '2018-01-01T12:00:00' +DECLARE @endday datetime = '2026-01-01T12:00:00' +DECLARE @inc datetime +SET @inc = @startday +DECLARE @keyDate bigint + +WHILE @inc <= @endday +BEGIN +SET @keyDate = ( SELECT DATEPART(hour, @inc) + + DATEPART(day, @inc) * 100 + + DATEPART(month, @inc) * 10000 + + DATEPART(year, @inc) * 1000000 ) + + INSERT INTO shipping_storage.dbo.dim_date + (date_key, date, year, quarter, month, week, day, hour) + VALUES + ( @keyDate, + @inc, + year(@inc), + datepart(quarter, @inc), + month(@inc), + datepart(week,@inc), + day(@inc), + datename(hour, @inc) ) + SET @inc = DATEADD(DAY, 1, @inc) +END +GO + +------------------- +-- dim_geography -- +------------------- + +SET LANGUAGE Russian +use shipping_storage + +INSERT INTO shipping_storage.dbo.dim_geography +(region_name, region_code, postal_code, city_name) +VALUES +(N'Саратовская область', 64, 410065, N'Саратов'), +(N'Московская область', 77, 410065, N'Подольск'), +(N'Московская область', 77, 410065, N'Москва') +GO + + +------------------ +-- dim_wrapping -- +------------------ + +INSERT INTO shipping_storage.dbo.dim_wrapping +(name, price) +VALUES (N'Жёсткая', 100), + (N'Полужёсткая', 100), + (N'Мягкая', 100), + (N'Воздушно-Пузырьковая', 100), + (N'Рулон', 100) +GO + + +---------------------- +-- dim_payment_kind -- +---------------------- + +INSERT INTO shipping_storage.dbo.dim_payment_kind +(name) +VALUES (N'Наличными'), + (N'Картой'), + (N'Картой МИР'), + (N'Чеком') +GO + + +----------------------------- +-- dim_customer_demography -- +----------------------------- + +SET LANGUAGE Russian +use shipping_storage + +DECLARE @n INT +SET @n = 1 + +WHILE @n <= 1000 +BEGIN + INSERT INTO shipping_storage.dbo.dim_customer_demography + (age, marital_status, number_of_children) + VALUES (20, N'Не женат', 0) + SET @n = @n + 1 +END +GO + +------------------ +-- dim_customer -- +------------------ + +SET LANGUAGE Russian +use shipping_storage + +DECLARE @n INT +SET @n = 1 + +WHILE @n <= 1000 +BEGIN + INSERT INTO shipping_storage.dbo.dim_customer + (demography_key, name, email, birthday, gender, phone_number, tax_number, geography_key, entry_date, discount, discount_start, discount_end, flag) + VALUES (@n, N'Иван Улитин', 'ivan@ulitin.ru', 2019043012, N'Мужской', N'+7900112233', 11223344, 1, 2022043012, 0.2, 2022042512, 2022043012, 1) + SET @n = @n + 1 +END +GO + +------------------ +-- dim_vehicles -- +------------------ + +SET LANGUAGE Russian +USE shipping_storage + +INSERT INTO shipping_storage.dbo.dim_vehicles +(name, tonnage, gas_consumption, on_route, route_start, route_end, flag) +VALUES +(N'Камаз', 100, 100, 0, 2022042512, 2022042612, 0) +GO + + +------------------- +-- fact_delivery -- +------------------- + +SET LANGUAGE Russian +USE shipping_storage + +DECLARE @startday datetime = '2018-01-01T12:00:00' +DECLARE @endday datetime = '2026-01-01T12:00:00' +DECLARE @inc DATETIME +SET @inc = @startday +DECLARE @keyDate BIGINT + +DECLARE @n INT +SET @n = 1 + +WHILE @inc < @endday +BEGIN + SET @keyDate = ( SELECT DATEPART(hour, @inc) + + DATEPART(day, @inc) * 100 + + DATEPART(month, @inc) * 10000 + + DATEPART(year, @inc) * 1000000 ) + + INSERT INTO shipping_storage.dbo.fact_delivery + (wrapping_key, payment_kind_key, volume, weight, customer_key, order_date, expected_delivery_date, factual_delivery_date, geography_from_key, geography_to_key, vehicle_key, subtotal, discount, tax_amount, total) + VALUES + (1, 1, 10, 10, @n % 1000 + 1, @keyDate, @keyDate, @keyDate, 1, 1, 1, 1000, 0, 200, 1200) + + SET @inc = DATEADD(DAY, 1, @inc) + SET @n = @n + 1 +END +GO + +-- DELETE FROM shipping_storage.dbo.archive_fact_delivery \ No newline at end of file -- cgit v1.2.3