-
Notifications
You must be signed in to change notification settings - Fork 6
/
Stg_DimLocation.sql
45 lines (40 loc) · 1.3 KB
/
Stg_DimLocation.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
--SETUP TEMPORARY TABLE AND GET OLTP DATA
USE IMSports;
IF OBJECT_ID('tempdb.dbo.##tempLocation') IS NOT NULL
DROP TABLE [##tempLocation]
CREATE TABLE [##tempLocation] (
[locBusinessID] int,
[locCountryCode] nvarchar(3),
[locCityName] nvarchar(50),
[locPostalCode] nvarchar(50),
[locStateName] nvarchar(50),
[locSalesTerritoryName] nvarchar(50),
[locSalesTerritoryGroup] nvarchar(50)
)
INSERT INTO ##tempLocation
SELECT
People.Address.AddressID AS locBusinessID,
People.State.CountryRegionCode AS locCountryCode,
People.Address.City AS locCityName,
People.Address.[Postal-Code] AS locPostalCode,
People.State.Name AS locStateName,
Sales.SalesTerritory.Name AS locSalesTerritoryName,
Sales.SalesTerritory.[Group] AS locSalesTerritoryGroup
FROM People.Address
INNER JOIN People.State
ON People.State.StateProvinceID = People.Address.StateProvinceID
INNER JOIN Sales.SalesTerritory
ON People.State.TerritoryID = Sales.SalesTerritory.TerritoryID
;
--TRANSFORM DATA
USE IMSports_SalesSA;
SELECT DISTINCT
##tempLocation.locBusinessID,
##tempLocation.locCountryCode,
##tempLocation.locCityName,
##tempLocation.locPostalCode,
##tempLocation.locStateName,
##tempLocation.locSalesTerritoryName,
##tempLocation.locSalesTerritoryGroup
FROM ##tempLocation
;