Database Reference
In-Depth Information
dbo.MonthYear(M1.Month,M1.Year) AS Month,
M1.SalesAmount, M2.SalesAmount AS PreviousMonth
FROM MonthlySalesProd M1 LEFT OUTER JOIN
MonthlySalesProd M2 ON
M1.ProductName = M2.ProductName AND
M1.Month-1 = M2.Month AND M1.Year = M2.Year
WHERE M1.Month
>
1
UNION
SELECT M1.ProductName,
dbo.MonthYear(M1.Month,M1.Year) AS Month,
M1.SalesAmount, M2.SalesAmount AS PreviousMonth
FROM MonthlySalesProd M1 LEFT OUTER JOIN
MonthlySalesProd M2 ON
M1.ProductName = M2.ProductName AND
M1.Month+11 = M2.Month AND M1.Year-1 = M2.Year
WHERE M1.Month=1 )
SELECT ProductName, Month,
FORMAT(SalesAmount,
'
$###,##0.00
'
)ASSalesAmount,
FORMAT(PreviousMonth,
'
$###,##0.00
'
) AS PreviousMonth,
FORMAT(SalesAmount - PreviousMonth,
'
$###,##0.00; $-###,##0.00
'
)ASSalesGrowth
FROM MonthlySalesProdComp
ORDER BY ProductName, Month
Here, we first define a temporary table
MonthlySalesProd
that computes the
monthly sales by product. In the second temporary table
MonthlySalesProd-
Comp
, the previous temporary table is used twice for obtaining through a
left outer join the sales of a month and the previous month. Notice that two
cases must be accounted for. In the first case, the previous month belongs to
the same year, while in the second case, the previous month for January is
December of the previous year. Finally, the main query is used for ordering
the tuples of the second temporary table and to define their display format.
Note that the above query cannot be written with window functions, since
it would combine the sales of a month with the sales of the previous
existing
month. For example, if there are no sales for February, the tuple for March
will compare the sales of March and those of January.
Query 6.5.
Three best-selling employees.
SELECT TOP(3) E.FirstName +
''
+ E.LastName AS EmployeeName,
FORMAT(SUM(F.SalesAmount),
'
$###,##0.00
'
) AS SalesAmount
FROM Sales F, Employee E
WHERE F.EmployeeKey = E.EmployeeKey
GROUP BY E.FirstName, E.LastName
ORDER BY SUM(F.SalesAmount) DESC
In the above query, we group the sales by employee and apply the
SUM
aggregation to each group. The result is then sorted in descending order of
the aggregated sales, and the
TOP
function is used to obtain the first three
tuples.
Search WWH ::
Custom Search