Database Reference
In-Depth Information
Figure 13-19.
Unfiltered Results from the view
he
ORDER BY
clause can be used when you retrieve results from the view, which can be quite helpful since
a view cannot store variables. Listing 13-24 shows an example of filtering the results of your view using the same
options we used before the view.
Listing 13-24.
Using Your View with the Previous Filters
DECLARE
@ShowAll nVarchar(4) = 'False'
, @StartDate datetime = '09/13/1994'
, @EndDate datetime = '09/14/1994'
, @Prefix nVarchar(3) = 'PS%'
, @AverageQty int
SELECT @AverageQty = AVG(SalesQuantity) FROM DWPubsSales.dbo.FactSales
SELECT
PublisherName
, [Title]
, [TitleId]
, [OrderDate]
, [Total for that Date by Title]
, [Average Qty in the FactSales Table] = @AverageQty
, [KPI on Avg Quantity] = CASE
WHEN [Total for that Date by Title]
between (@AverageQty - 5) and (@AverageQty + 5) THEN 0
WHEN [Total for that Date by Title] < (@AverageQty - 5) THEN -1
WHEN [Total for that Date by Title] > (@AverageQty + 5) THEN 1
END
FROM vQuantitiesByTitleAndDate
