Database Reference
In-Depth Information
INNER JOIN DWPubsSales.dbo.DimTitles AS DT
ON FS.TitleKey = DT.TitleKey
INNER JOIN DWPubsSales.dbo.DimDates AS DD
ON FS.OrderDateKey = DD.DateKey
INNER JOIN DWPubsSales.dbo.DimPublishers AS DP
ON DT.PublisherKey = DP.PublisherKey
ORDER BY DP.PublisherName, [Title], [OrderDate]
■
Leaving columns in your original code and simply commenting them out, rather than completely
removing them, is often helpful for troubleshooting and validating your code. After the code is stabilized, they
can then be removed permanently. For instructional purposes and clarity, we remove them from the future listings
in this chapter.
Tip
Formatting Results Using SQL Functions
To make the data more user-friendly, we use the SQL function
CONVERT
to change the data into a string of
characters with the typical United States presentation. The
CONVERT
function was designed by Microsoft to do just
this. Microsoft has also incorporated a set of numbers to determine which U.S. format to use. For example, format
number 110 gives a date with the
day - month - year
format, while format number 101 gives you a date with the
day/month/year
format (Listing 13-10).
Listing 13-10.
Using the
CONVERT
Function
SELECT
DP.PublisherName
, [Title] = DT.TitleName
, [TitleId] = DT.TitleId
, [OrderDate]
=CONVERT(varchar(50), [Date], 101)
, SalesQuantity
FROM DWPubsSales.dbo.FactSales AS FS
INNER JOIN DWPubsSales.dbo.DimTitles AS DT
ON FS.TitleKey = DT.TitleKey
INNER JOIN DWPubsSales.dbo.DimDates AS DD
ON FS.OrderDateKey = DD.DateKey
INNER JOIN DWPubsSales.dbo.DimPublishers AS DP
ON DT.PublisherKey = DP.PublisherKey
ORDER BY DP.PublisherName, [Title], [OrderDate]
The results now include formatted dates, as shown in Figure
13-6
.
