This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to divide employee table into three partitions basis on salary ranges.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
EmpId EmpName Salary ----------- ---------- ----------- 1 abc 10000 2 xyz 20000 3 bcd 40000 4 rtf 30000 5 qwe 45000 6 tyu 65000 7 fgh 52000 8 dfc 35000 9 asd 45000 10 xcv 52000 11 olp 65000 12 lmj 25000 13 vbn 20000 14 efe 10000 |
Expected Output:
|
1 2 3 4 5 |
NumberOfPartitions SalaryRanges Counts -------------------- ------------------------- ----------- 1 10000-25000 5 2 30000-45000 5 3 52000-65000 4 |
Create a table with data:
|
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 |
CREATE TABLE tbl_Emp ( EmpId INT ,EmpName VARCHAR(10) ,Salary INT ) GO INSERT INTO tbl_Emp VALUES (1,'abc',10000), (2,'xyz',20000), (3,'bcd',40000), (4,'rtf',30000), (5,'qwe',45000), (6,'tyu',65000), (7,'fgh',52000), (8,'dfc',35000), (9,'asd',45000), (10,'xcv',52000), (11,'olp',65000), (12,'lmj',25000), (13,'vbn',20000), (14,'efe',10000) GO |
Solution: Using NTILE()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
;WITH CTE AS ( SELECT Salary ,NTILE(3) OVER (ORDER BY Salary) as NumberOfPartitions FROM tbl_Emp ) SELECT NumberOfPartitions ,CONCAT(MIN(Salary),'-',MAX(Salary)) SalaryRanges ,COUNT(*) Counts FROM CTE GROUP BY NumberOfPartitions |
Please try the different solution for this puzzle and share it via comment...
