Adventure-Works/Dim_Customer.sql at main · Tapas-Gope/Adventure-Works · GitHub

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
-- Cleansed DimCustomer Table --
SELECT
  c.CustomerKey AS CustomerKey,
  --[GeographyKey],
  --[CustomerAlternateKey],
  --[Title],
  c.firstname AS [FirstName],
  --[MiddleName],
  c.lastname AS [LastName],
  c.firstname + ' ' + c.lastname AS [Full Name],
  --[NameStyle],
  --[BirthDate],
  --[MaritalStatus],
  --[Suffix],
  CASE c.gender WHEN 'M' THEN 'MALE' WHEN 'F' THEN 'FEMALE' END AS Gender,
  --[EmailAddress],
  --[YearlyIncome],
  --[TotalChildren],
  --[NumberChildrenAtHome],
  --[EnglishEducation],
  --[SpanishEducation],
  --[FrenchEducation],
  --[EnglishOccupation],
  --[SpanishOccupation],
  --[FrenchOccupation],
  --[HouseOwnerFlag],
  --[NumberCarsOwned],
  --[AddressLine1],
  --[AddressLine2],
  --[Phone],
  c.datefirstpurchase AS [Date First Purchase],
  --[CommuteDistance]
  g.city as [Customer City] -- Joined in Customer City from Geography tool
FROM
  dbo.dimcustomer AS c
  LEFT JOIN dbo.dimgeography AS g ON g.geographykey = c.geographykey
ORDER BY
  CustomerKey ASC