-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathNumber - Load Data.sql
305 lines (272 loc) · 13.4 KB
/
Number - Load Data.sql
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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
-- SQL Prompt formatting off
-- TRUNCATE TABLE Dimension.Number;
/**********************************************************************************************************************
** Change @HowManyNumberToCreate to the number of numbers you wish to create in your numbers table + zero
** Load up your number (tally) dimension table once. The quantity of numbers you need will depend on your data.
** This is done with a while loops vs a generic batch statement.
**
** Runtimes on (AMD Ryzen 7 3800x 8 cores /16 logical processors | 32 GB memory)
** 1,000 Numbers = 0 seconds (0.102 MB)
** 10,000 Numbers = 3 seconds (1.070 MB)
** 100,000 Numbers = 1 minutes 34 seconds (11.016 MB)
** 1,000,000 Numbers = 23 minutes 39 seconds (121.914 MB)
**
** Numbers table uses:
** Join to find gaps in number join
** Lists to output results in a query for the count of number, this allows a result of 0 count for a specific number
** Join to return the NumberWord to print on checks, join again for cents
** Join to output Roman Numerals
** Join to group by number placements tens, hundreds, millions
** Join to use as a dimension slicer
** Query to find binary, hex numbers
** Removing Duplicates from Strings in SQL Serve https://www.mssqltips.com/sqlservertip/4140/removing-duplicates-from-strings-in-sql-server/
** Validate the contents of large dynamic SQL strings in SQL Server https://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server/
** Sort characters withing a string https://vyaskn.tripod.com/fun_with_numbers_in_t-sql_queries.htm
** View other uses via Stack Overflow https://stackoverflow.com/search?page=1&tab=Relevance&q=%22numbers%20table%22
** View other uses via Stack Exchange https://dba.stackexchange.com/search?q=%22numbers+table%22
**
**********************************************************************************************************************/
DECLARE @HowManyNumberToCreate BIGINT = 1000;
SET NOCOUNT ON;
/* NumberWord Variables */
DROP TABLE IF EXISTS #NumbersTable;
CREATE TABLE #NumbersTable (Number CHAR(2) NOT NULL, Word VARCHAR(10) NOT NULL);
DECLARE @InputNumber VARCHAR(38);
DECLARE @NumberWord VARCHAR(8000);
DECLARE @Counter INT;
DECLARE @Loops INT;
DECLARE @Position INT;
DECLARE @Chunk CHAR(3);
DECLARE @TensOnes CHAR(2);
DECLARE @Hundreds CHAR(1);
DECLARE @Tens CHAR(1);
DECLARE @Ones CHAR(1);
/* BinaryNumber variables*/
DECLARE @Binary VARCHAR(16);
/* HexNumber Variables */
DECLARE @HexNumber BIGINT;
DECLARE @HexSequence CHAR(16) = '0123456789ABCDEF';
DECLARE @HexValue VARCHAR(50);
DECLARE @HexDigit CHAR(1);
/* RomanNumeral Variables */
DECLARE @RomanNumeral VARCHAR(9);
/* Number Placements Variables */
DECLARE @NumberString VARCHAR(19);
DECLARE @Number BIGINT = 0;
WHILE @Number <= @HowManyNumberToCreate
BEGIN
/* Initialize NumberWord variables */
SELECT
@InputNumber = CONVERT(VARCHAR(38), @Number)
,@NumberWord = ''
,@Counter = 1;
SELECT @Position = LEN(@InputNumber) - 2, @Loops = LEN(@InputNumber) / 3;
/* Make sure there is an extra loop added for the remaining numbers */
IF LEN(@InputNumber) % 3 <> 0
BEGIN
SET @Loops = @Loops + 1;
END;
/* Insert data for the numbers and words */
INSERT INTO #NumbersTable (Number, Word)
VALUES
('00', '') ,('01', 'one') ,('02', 'two')
,('03', 'three') ,('04', 'four') ,('05', 'five')
,('06', 'six') ,('07', 'seven') ,('08', 'eight')
,('09', 'nine') ,('10', 'ten') ,('11', 'eleven')
,('12', 'twelve') ,('13', 'thirteen') ,('14', 'fourteen')
,('15', 'fifteen') ,('16', 'sixteen') ,('17', 'seventeen')
,('18', 'eighteen') ,('19', 'nineteen') ,('20', 'twenty')
,('30', 'thirty') ,('40', 'forty') ,('50', 'fifty')
,('60', 'sixty') ,('70', 'seventy') ,('80', 'eighty')
,('90', 'ninety');
WHILE @Counter <= @Loops
BEGIN
/* Get chunks of 3 numbers at a time, padded with leading zeros */
SET @Chunk = RIGHT('000' + SUBSTRING(@InputNumber, @Position, 3), 3);
IF @Chunk <> '000'
BEGIN
SELECT
@TensOnes = SUBSTRING(@Chunk, 2, 2)
,@Hundreds = SUBSTRING(@Chunk, 1, 1)
,@Tens = SUBSTRING(@Chunk, 2, 1)
,@Ones = SUBSTRING(@Chunk, 3, 1);
/* If twenty or less, use the word directly from #NumbersTable */
IF CONVERT(INT, @TensOnes) <= 20 OR @Ones = '0'
BEGIN
SET @NumberWord = (
SELECT TOP (1) Word
FROM #NumbersTable
WHERE @TensOnes = Number
) + CASE @Counter
WHEN 1 THEN '' /* No name */
WHEN 2 THEN ' thousand '
WHEN 3 THEN ' million '
WHEN 4 THEN ' billion '
WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion '
WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion '
WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion '
WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion '
WHEN 13 THEN ' undecillion '
ELSE ''
END + @NumberWord;
END;
ELSE
BEGIN
SET @NumberWord = ' ' + (
SELECT TOP (1) Word
FROM #NumbersTable
WHERE @Tens + '0' = Number
) + '-' + (
SELECT TOP (1) Word
FROM #NumbersTable WHERE
'0' + @Ones = Number
) + CASE @Counter
WHEN 1 THEN '' /* No name */
WHEN 2 THEN ' thousand '
WHEN 3 THEN ' million '
WHEN 4 THEN ' billion '
WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion '
WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion '
WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion '
WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion '
WHEN 13 THEN ' undecillion '
ELSE ''
END + @NumberWord;
END;
/* Get the hundreds */
IF @Hundreds <> '0'
BEGIN
SET @NumberWord = (
SELECT TOP (1) Word
FROM #NumbersTable
WHERE '0' + @Hundreds = Number
) + ' hundred ' + @NumberWord;
END;
END;
SELECT @Counter = @Counter + 1, @Position = @Position - 3;
END;
/* Remove any double spaces */
SET @NumberWord = LTRIM(RTRIM(REPLACE(@NumberWord, ' ', ' ')));
SET @NumberWord = UPPER(LEFT(@NumberWord, 1)) + SUBSTRING(@NumberWord, 2, 8000);
/* Binary Number */
SET @Binary =
CASE WHEN CONVERT(VARCHAR(16), @Number & 32768) > 0 THEN '1' ELSE '0'END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 16384) > 0 THEN '1' ELSE '0'END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 8192) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 4096) > 0 THEN '1'ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 2048) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 1024) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 512) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 256) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 128) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 64) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 32) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 16) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 8) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 4) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 2) > 0 THEN '1' ELSE '0' END +
CASE WHEN CONVERT(VARCHAR(16), @Number & 1) > 0 THEN '1' ELSE '0' END;
/*Hex Number */
SET @HexNumber = @Number;
SET @HexValue = SUBSTRING(@HexSequence, (@HexNumber % 16) + 1, 1);
WHILE @HexNumber > 0
BEGIN
SET @HexDigit = SUBSTRING(@HexSequence, ((@HexNumber / 16) % 16) + 1, 1);
SET @HexNumber = @HexNumber / 16;
IF @HexNumber <> 0
BEGIN
SET @HexValue = @HexDigit + @HexValue;
END;
END;
/* Roman Numerals */
IF @Number < 4000
BEGIN
SET @RomanNumeral =
REPLICATE('M', @Number / 1000) +
REPLACE(REPLACE(REPLACE(
REPLICATE('C', @Number % 1000 / 100),
REPLICATE('C', 9), 'CM'),
REPLICATE('C', 5), 'D'),
REPLICATE('C', 4), 'CD') +
REPLACE(REPLACE(REPLACE(
REPLICATE('X', @Number % 100 / 10),
REPLICATE('X', 9), 'XC'),
REPLICATE('X', 5), 'L'),
REPLICATE('X', 4), 'XL') +
REPLACE(REPLACE(REPLACE(
REPLICATE('I', @Number % 10),
REPLICATE('I', 9), 'IX'),
REPLICATE('I', 5), 'V'),
REPLICATE('I', 4), 'IV');
END;
ELSE
BEGIN
SET @RomanNumeral = '';
END;
/* Number Placements */
SET @NumberString = @Number;
INSERT INTO Dimension.Number (
[Number Key]
,[Number Word]
,[Binary Number]
,[Hex Number]
,[Even Odd]
,[Roman Numeral]
,Ones
,Tens
,Hundreds
,Thousands
,[Ten Thousands]
,[Hundred Thousands]
,Millions
,[Ten Millions]
,[Hundred Millions]
,Billions
,[Ten Billions]
,[Hundred Billions]
,Trillions
,[Ten Trillions]
,[Hundred Trillions]
,Quadrillions
,[Ten Quadrillions]
,[Hundred Quadrillions]
,Quintillions
)
SELECT
NumberId = @Number
,NumberWord = IIF(@Number = 0, 'Zero', @NumberWord)
,BinaryNumber = @Binary
,HexNumber = @HexValue
,EvenOdd = CASE @Number % 2 WHEN 0 THEN 'Even' ELSE 'Odd' END
,RomanNumeral = IIF(@Number = 0, 'Nulla', @RomanNumeral)
,Ones = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 0, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 0, 1), 0)
,Tens = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 1, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 1, 1), 0)
,Hundreds = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 2, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 2, 1), 0)
,Thousands = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 3, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 3, 1), 0)
,TenThousands = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 4, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 4, 1), 0)
,HundredThousands = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 5, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 5, 1), 0)
,Millions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 6, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 6, 1), 0)
,TenMillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 7, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 7, 1), 0)
,HundredMillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 8, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 8, 1), 0)
,Billions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 9, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 9, 1), 0)
,TenBillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 10, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 10, 1), 0)
,HundredBillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 11, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 11, 1), 0)
,Trillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 12, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 12, 1), 0)
,TenTrillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 13, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 13, 1), 0)
,HundredTrillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 14, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 14, 1), 0)
,Quadrillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 15, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 15, 1), 0)
,TenQuadrillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 16, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 16, 1), 0)
,HundredQuadrillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 17, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 17, 1), 0)
,Quintillions = IIF(LEN(SUBSTRING(@NumberString, LEN(@NumberString) - 18, 1)) > 0, SUBSTRING(@NumberString, LEN(@NumberString) - 18, 1), 0);
SET @Number = @Number + 1;
END;
SET NOCOUNT OFF;
-- SQL Prompt formatting on