Database Reference
In-Depth Information
in this exercise, you wrote a report query that captured sales data based on sales by stores. in the next
exercise, you use this code to create a reporting stores procedure. But, we have a few other items to talk
about first.
Using Subqueries
Multiple queries can be used at the same time to achieve a single result. There are a couple ways to do this, and
using subqueries is a popular choice. Subqueries are often used in a
WHERE
clause to retrieve a set of values that
can be examined conditionally. Another use for subqueries is to fill up the variables with data based on the
results of the subquery. Listing 13-20 shows an example of this.
Listing 13-20.
Fill from Your Querying Parameters with Subqueries
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
DP.PublisherName
, [Title] = DT.TitleName
, [TitleId] = DT.TitleId
, [OrderDate] = CONVERT(varchar(50), [Date], 101)
, [Total for that Date by Title] = Sum(SalesQuantity)
, [Average Qty in the FactSales Table] = @AverageQty
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
GROUP BY
DP.PublisherName
, DT.TitleName
, DT.TitleId
, Convert(varchar(50), [Date], 101)
ORDER BY DP.PublisherName, [Title], [OrderDate]
