data-analysis-project/SQLQuery - DIM_CustomerTable.sql at main · zheke01/data-analysis-project · 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
39
--DIM_CustomerTable--
SELECT
  c.customerkey AS CustomerKey,
  --,[GeographyKey]
  --,[CustomerAlternateKey]
  --,[Title]
  c.firstname AS [First Name],
  --,[MiddleName]
  c.lastname AS [Last Name],
  c.firstname + ' ' + lastname AS [Full Name],
  -- Combined First and Last 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 DateFirstPurchase,
  --,[CommuteDistance]
  g.city AS [Customer City] -- Joined in Customer City from Geography Table
FROM
  [AdventureWorksDW2022].[dbo].[DimCustomer] as c
  LEFT JOIN [AdventureWorksDW2022].[dbo].[DimGeography] AS g ON g.geographykey = c.geographykey
ORDER BY
  CustomerKey ASC -- Ordered List by CustomerKey