Добавил десятое задание

1 files changed, 164 insertions, 0 deletions

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