1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
| CREATE FUNCTION fn_GetSalesData (@aYear smallint)
RETURNS @ReturnTable2 TABLE
(
BusinessEntityID INT,
FirstName VARCHAR(10),
LastName VARCHAR(30),
[Actual Subtotal] INT,
[Rang année courante] INT,
[Previous Subtotal] INT,
[Rang année précédente] INT,
[Indicateur] VARCHAR (3)
)
AS
BEGIN
INSERT INTO @ReturnTable2
SELECT
sp.BusinessEntityID as 'ID du commis de vente',
pp.FirstName as 'Prénom',
pp.LastName as 'Nom de famille',
act.[Actual Subtotal] as 'Sous-Total année courante',
act.[Rang année courante],
prv.[Previous Subtotal] as 'Sous-Total année précédente',
prv.[Rang année précédente],
CASE
WHEN CONVERT(INT, RANK() OVER(ORDER BY SUM(soh.SubTotal) DESC)) - CONVERT(INT, RANK() OVER(ORDER BY SUM(soh.SubTotal) DESC)) > 0 THEN ''+''
WHEN CONVERT(INT, RANK() OVER(ORDER BY SUM(soh.SubTotal) DESC)) - CONVERT(INT, RANK() OVER(ORDER BY SUM(soh.SubTotal) DESC)) < 0 THEN ''-''
ELSE '='
END as Indicateur
FROM AdventureWorks2019.Sales.SalesPerson sp
INNER JOIN AdventureWorks2019.Person.Person pp on pp.BusinessEntityID = sp.BusinessEntityID
INNER JOIN AdventureWorks2019.Sales.SalesOrderHeader soh on soh.SalesPersonID = sp.BusinessEntityID
INNER JOIN (
SELECT
FORMAT(SUM(soh1.SubTotal), 'c', 'en-us') as 'Actual Subtotal',
RANK() OVER(ORDER BY SUM(soh1.SubTotal) DESC) as 'Rang année courante',
SalesPersonID
FROM AdventureWorks2019.Sales.SalesOrderHeader soh1
INNER JOIN AdventureWorks2019.Sales.SalesPerson sp1 on sp1.BusinessEntityID = soh1.SalesPersonID
WHERE DATEPART(YEAR, soh1.OrderDate) = @aYear
GROUP BY SalesPersonID) as act on act.SalesPersonID = sp.BusinessEntityID
INNER JOIN (
SELECT
FORMAT(SUM(soh1.SubTotal), 'c', 'en-us') as 'Previous Subtotal',
RANK() OVER(ORDER BY SUM(soh1.SubTotal) DESC) as 'Rang année précédente',
SalesPersonID
FROM AdventureWorks2019.Sales.SalesOrderHeader soh1
INNER JOIN AdventureWorks2019.Sales.SalesPerson sp1 on sp1.BusinessEntityID = soh1.SalesPersonID
WHERE DATEPART(YEAR, soh1.OrderDate) = (@aYear - 1)
GROUP BY SalesPersonID) as prv on prv.SalesPersonID = sp.BusinessEntityID
WHERE DATEPART(YEAR, soh.OrderDate) = @aYear
GROUP BY
sp.BusinessEntityID,
pp.FirstName,
pp.LastName,
act.[Actual Subtotal],
act.[Rang année courante],
prv.[Previous Subtotal],
prv.[Rang année précédente]
RETURN
END; |
