AeXimiZC

1--Drop Sales.Quotas table if it exists  
2 IF OBJECT_ID (N   'Sales.Quotas'   , N   'U'   )   IS    NOT    NULL  
3 DROP    TABLE   Sales.Quotas  
4 GO  
5 --Create Sales.Quotas table  
6 SELECT   e.FirstName, e.LastName, q.SalesQuota   AS   Quota,  
7 DATENAME(m,q.QuotaDate)   AS   [   MONTH   ],   YEAR   (q.QuotaDate)   AS   [   YEAR   ]  
8 INTO   Sales.Quotas  
9 FROM   Sales.SalesPersonQuotaHistory q  
10 INNER    JOIN   HumanResources.vEmployee e  
11 ON   q.SalesPersonID   =   e.EmployeeID  
12 WHERE   SalesQuota   BETWEEN   210000   AND   280000  
13 ORDER BY   e.LastName, q.QuotaDate  
14
15 /*  
16 AS you can see, I simply pull DATA FROM a couple other tables IN the DATABASE IN order TO CREATE a SET OF meaningful test DATA.  
17
18 Here's the SELECT statement I use to query the new table:  
19
20 */  
21
22
23 SELECT   
24 ROW_NUMBER() OVER(ORDER BY Quota   DESC   )   AS   [RowNumber],  
25 RANK() OVER(ORDER BY Quota   DESC   )   AS   [Rank],  
26 DENSE_RANK() OVER(ORDER BY Quota   DESC   )   AS   [DenseRank],  
27 NTILE(5) OVER(ORDER BY Quota   DESC   )   AS   [NTile],  
28 LastName, Quota, [   MONTH   ], [   YEAR   ]  
29 FROM   Sales.Quotas