rv498
Yak Posting Veteran
60 Posts |
Posted - 2014-03-18 : 21:43:35
|
I these these two SELECT statements need to be there but I don't know whether I need HAVING/GROUP BY in here.List all customers and the most recent date they placed an order. Do not show the time with the order date. -- First find the number of unique customers to determine that your results includes the correct number of customers.-- Then determine which fields are needed to create accurate information about the customer.-- 18484 Rows SELECT COUNT(DISTINCT CustomerKey) FROM FactInternetSales SELECT CustomerKey, MAX(OrderDate) from FactInternetSales GROUP BYCustomerKeySo, these are the table structures. dbo.DimCustomer, dbo.FactInternetSales [dbo].[DimCustomer]([CustomerKey] [int] IDENTITY(1,1) NOT NULL,[GeographyKey] [int] NULL,[CustomerAlternateKey] [nvarchar](15) NOT NULL,[Title] [nvarchar](8) NULL,[FirstName] [nvarchar](50) NULL,[MiddleName] [nvarchar](50) NULL,[LastName] [nvarchar](50) NULL,[NameStyle] [bit] NULL,[BirthDate] [date] NULL,[MaritalStatus] [nchar](1) NULL,[Suffix] [nvarchar](10) NULL,[Gender] [nvarchar](1) NULL,[EmailAddress] [nvarchar](50) NULL,[YearlyIncome] [money] NULL,[TotalChildren] [tinyint] NULL,[NumberChildrenAtHome] [tinyint] NULL,[EnglishEducation] [nvarchar](40) NULL,[SpanishEducation] [nvarchar](40) NULL,[FrenchEducation] [nvarchar](40) NULL,[EnglishOccupation] [nvarchar](100) NULL,[SpanishOccupation] [nvarchar](100) NULL,[FrenchOccupation] [nvarchar](100) NULL,[HouseOwnerFlag] [nchar](1) NULL,[NumberCarsOwned] [tinyint] NULL,[AddressLine1] [nvarchar](120) NULL,[AddressLine2] [nvarchar](120) NULL,[Phone] [nvarchar](20) NULL,[DateFirstPurchase] [date] NULL, 2nd table [dbo].[FactInternetSales]([ProductKey] [int] NOT NULL,[OrderDateKey] [int] NOT NULL,[DueDateKey] [int] NOT NULL,[ShipDateKey] [int] NOT NULL,[CustomerKey] [int] NOT NULL,[PromotionKey] [int] NOT NULL,[CurrencyKey] [int] NOT NULL,[SalesTerritoryKey] [int] NOT NULL,[SalesOrderNumber] [nvarchar](20) NOT NULL,[SalesOrderLineNumber] [tinyint] NOT NULL,[RevisionNumber] [tinyint] NOT NULL,[OrderQuantity] [smallint] NOT NULL,[UnitPrice] [money] NOT NULL,[ExtendedAmount] [money] NOT NULL,[UnitPriceDiscountPct] [float] NOT NULL,[DiscountAmount] [float] NOT NULL,[ProductStandardCost] [money] NOT NULL,[TotalProductCost] [money] NOT NULL,[SalesAmount] [money] NOT NULL,[TaxAmt] [money] NOT NULL,[Freight] [money] NOT NULL,[CarrierTrackingNumber] [nvarchar](25) NULL,[CustomerPONumber] [nvarchar](25) NULL,[OrderDate] [datetime] NULL,[DueDate] [datetime] NULL,[ShipDate] [datetime] NULL, |
|