1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
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
|
