Top 5 & Bottom 5 Person by SalesAmt

Top 5 & Bottom 5 Sales Person SalesAmount and Previous SalesAmount

CREATE PROCEDURE [dbo].[pcget_SalesPerson_List]
-- Add the parameters for the stored procedure here
@Year int
AS

BEGIN

SET NOCOUNT ON;
SELECT SalesPersonName, SalesPersonKey, Year, CurrentSalesAmt, '(' + CONVERT(VARCHAR(50), PreviousSalesAmt) + ')' AS PreviousSalesAmt,
(CASE WHEN CurrentSalesAmt > PreviousSalesAmt THEN 1 WHEN CurrentSalesAmt < PreviousSalesAmt THEN -1 ELSE 0 END)
AS Trend
FROM
(
SELECT TOP 5 CAST(SUM(SalesAmt)/1000 AS NUMERIC(10)) AS CurrentSalesAmt
, DimDate.Year AS Year
, DimSalesPerson.SalesPersonName
, DimSalesPerson.SalesPersonKey
FROM FactSales
JOIN DimDate ON DimDate.DateKey = FactSales.DateKey
JOIN DimSalesPerson ON DimSalesPerson.SalesPersonKey = FactSales.SalesPersonKey

WHERE DimDate.Year = @year
GROUP BY DimDate.Year
, DimSalesPerson.SalesPersonName
, DimSalesPerson.SalesPersonKey
ORDER BY 1 DESC
) AS A
CROSS Apply
(
SELECT TOP 5 CAST(SUM(SalesAmt)/1000 AS NUMERIC(10)) AS PreviousSalesAmt
FROM FactSales
JOIN DimDate ON DimDate.DateKey = FactSales.DateKey
WHERE DimDate.Year = A.Year - 1 and FactSales.SalesPersonKey = a.SalesPersonKey
) AS B

SELECT SalesPersonName,SalesPersonKey,Year, CurrentSalesAmt,'(' + CONVERT(VARCHAR(50), PreviousSalesAmt) + ')' AS PreviousSalesAmt,
(CASE WHEN CurrentSalesAmt > PreviousSalesAmt THEN 1 WHEN CurrentSalesAmt < PreviousSalesAmt THEN -1 ELSE 0 END)
AS Trend
FROM
(
SELECT TOP 5 CAST(SUM(SalesAmt)/1000 AS NUMERIC(10)) AS CurrentSalesAmt
, DimDate.Year AS Year
, DimSalesPerson.SalesPersonName
, DimSalesPerson.SalesPersonKey
FROM FactSales
JOIN DimDate ON DimDate.DateKey = FactSales.DateKey
JOIN DimSalesPerson ON DimSalesPerson.SalesPersonKey = FactSales.SalesPersonKey
WHERE DimDate.Year = @year
GROUP BY DimDate.Year
, DimSalesPerson.SalesPersonName
, DimSalesPerson.SalesPersonKey
ORDER BY 1 ASC
) AS A
CROSS Apply
(
SELECT TOP 5 CAST(SUM(SalesAmt)/1000 AS NUMERIC(10)) AS PreviousSalesAmt
FROM FactSales
JOIN DimDate ON DimDate.DateKey = FactSales.DateKey
where DimDate.Year = A.Year - 1 and FactSales.SalesPersonKey = A.SalesPersonKey
) as B
END