([TripNumber], [DateKey], [LocationKey], [DriverKey], [TripMilage], [TripChange]
ID: 3701112 • Letter: #
Question
([TripNumber], [DateKey], [LocationKey], [DriverKey], [TripMilage], [TripChange])
SELECT
[TripNumber] = CAST([number] AS nVarchar(50)),
[DateKey] = [DWService].[dbo].[DimDates].[DateKey],
[LocationKey] = [DWService].[dbo].[DimLocation].[LocationKey],
[DriverKey] = [DWService].[dbo].[DimDriver].[DriverKey],
[TripMilage] = Cast ( IsNull([milage], 0) AS Decimal(18,4)),
[TripCharge] = Cast ( IsNull([charge], 0) AS Decimal(18,4))
FROM [serviceDB].[dbo].[Trip] INNER JOIN [DWService].[dbo].[DimDates]
ON [serviceDB].[dbo].[Trip].[Date] = [DWService].[Dbo].[DimDates].[Date]
Inner Join serviceDB.dbo.Street
ON serviceDB.dbo.Trip.Street_Code = serviceDB.dbo.Street.Street_Code
INNER JOIN [DWService].[dbo].[DimLocation]
ON serviceDB.dbo.Street.StreetName = DWService.dbo.DimLocation.Street
INNER JOIN [DWService].[dbo].[DimDriver]
ON [serviceDB].[dbo].[Trip].[Driver_ID] = [Dwservice].[dbo].[dimdriver].[DriverID]
GO
Dim DaHK Date hey -?Drive Lhej Date /}.Tr t$?010 inttr Streek Coda ocatic 0i that ge fom cy aTtp ? stieet matehed, r Hias inse steet it Then steet Code neSeet oame OLTP goes Dimlo Chant Dimlrcation so damey no oty City? eity Tablh and "Stud table.? Citl staeet Cod. steet-name. Code
Explanation / Answer
SELECT TRIP#, DATEKEY, LOCATIONKEY, DRIVERKEY, TRIP_MILAGE, TRIP_CHARGE FROM FACT_TRIP
INNER JOIN TRIP ON FACT_TRIP.TRIP# = TRIP.NUMBER -- Inner Join TRIP on Trip#
INNER JOIN DIMDATE ON FACT_TRIP.DATEKEY = DIMDATE.DATEKEY -- Inner JOIN DIMDATE on Datekey
INNER JOIN DIMLOCATTION ON FACT_TRIP.LOCATIONKEY = DIMLOCATION.LOCATIONKEY -- Inner Join Dimlocation on LocationKey
INNER JOIN DIMCITY ON DIMCITY.CITYID = TRIP.STREET_CODE AND TRIP.NUMBER = FACT_TRIP.TRIP#; --- Inner JOIN Dimcity on cityid and Trip on Number
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.