Database Reference
In-Depth Information
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
WHERE
@ShowAll = 'True'
OR
[Date] BETWEEN @StartDate AND @EndDate
AND
[TitleId] LIKE @Prefix
ORDER BY DP.PublisherName, [Title], [OrderDate]
When this query is executed, the results are similar to those shown in Figure
13-14
.
Figure 13-14.
Using a parameter flag to show all results
Adding Aggregations
One common request is for reports to display totals and subtotals. In SQL queries, this can be accomplished by
using aggregate functions. To demonstrate this, let's go ahead and summarize the sales quantities using the
SUM
aggregate function, as shown in Listing 13-19.
Listing 13-19.
Adding Aggregate Values to Our Results
DECLARE
@ShowAll nVarchar(4) = 'False'
, @StartDate datetime = '09/13/1994'
, @EndDate datetime = '09/14/1994'
, @Prefix nVarchar(3) = 'PS%'
