Database Reference
In-Depth Information
Outputs:
Name
Type
Example
Description
PublisherName
nVarchar(50)
New Moon Books
Name of publisher
Title
nVarchar(100)
Life Without Fear
Title of book
TitleId
nVarchar(6)
PS2106
Natural ID used for a title
OrderDate
Varchar(50)
01/13/2011
Date of order
Total for the
Int
108
Total quantity based on title and
date
Date by Title
Average Qty
Decimal
53.512
Total overall average quantity
from the entire table
in the FactSales Table
or Float
KPI on Avg Quantity
Int
-1, 0, 1
Total overall average quantity
compared to the sum of that
title (filter by search) and
categorized into three groups
based on the following:
-1 = Quantity is lower than
average by 5
0 = Quantity is between 5 less to
5 more of the average, inclusive
1 = Quantity is more than
average by 5
Source Code:
CREATE PROCEDURE pSelQuantitiesByTitleAndDate
(
-- 1) Define the parameter list:
-- Parameter Name, Data Type, Default Value --
@ShowAll nVarchar(4) = 'True'
-- 'True|False'
, @StartDate datetime = '01/01/1990'
-- 'Any valid date in the mm/dd/yyyy format'
, @EndDate datetime = '01/01/2100'
-- 'Any valid date in the mm/dd/yyyy format'
, @Prefix nVarchar(3) = '%'
-- '0 or more characters'
)
AS
BEGIN
-- the body of the stored procedure -
-
-- 2) Create and Set the @AverageQty to get the overall average of sales qty
.
DECLARE @AverageQty int
SELECT @AverageQty = Avg(SalesQuantity) FROM DWPubsSales.dbo.FactSales
--3) Get the Report Data with this select statement
SELECT
