Database Reference
In-Depth Information
[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
,[KPI on Avg Quantity] = CASE
WHEN Sum(SalesQuantity)
between (@AverageQty- 5) and (@AverageQty + 5) THEN 0
WHEN Sum(SalesQuantity) < (@AverageQty- 5) THEN -1
WHEN Sum(SalesQuantity) > (@AverageQty + 5) THEN 1
END
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]
END
-- the body of the stored procedure --
In production code, comments are often removed from within the procedure, but in the sdK these comments
can be left in, and additional descriptions, instructions, and comments are written out in full sentences after
each section, as you can see in the far-right column of each table.
objects such as views, functions, and parameters should be stored together in groups and in alphabetical
order within those groups. We chose to use a table format for organization, but tables are not required.
This stored procedure was already titled pselQuantitiesByTitleAnddate within the BI solution. But, for better
searchability, we included a more human-friendly title: stored Procedure for sales Quantities by Title and
date. For additional organization, we suggest listing this type of sdK with other stored procedures that select
data within the library.
Any additional information for how this procedure is used including any links to other topics should also be
included on the same page. The idea is that if a developer adds to your process or uses these constructs,
they are able to see what they can work with.
