Click here to Skip to main content
15,902,939 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
How can find-out Department wise Every Designation maximum GS And Minimum GS with empid.

What I have tried:

select A.EmpID,A.EmpName,B.DeptName,A.DeptId,D.DesigName,A.DesigID, GS from tblEmp_Info AS A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID 
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where IsInactive=0 and A.DeptId=4
order by A.deptid,A.desigid,GS desc


this is my data base code, here is show all gs(gross salary) with maximum to minimum.but i want only Department wise every designation miximum and minimun GS with empid. pls help me ASAP.
Posted
Updated 9-Apr-17 2:25am

Check this: sql - Highest Salary in each department - Stack Overflow[^]

You have to change it to your needs:
SQL
WITH cteRowNum AS (
    SELECT DeptID, EmpName, Salary,
           DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
        FROM EmpDetails
)
SELECT DeptID, EmpName, Salary
    FROM cteRowNum
    WHERE RowNum = 1;
 
Share this answer
 
Comments
Glorious007 9-Apr-17 8:47am    
ohhhh nice!
i got it

WITH cteRowNum AS (
SELECT B.DeptName,A.DeptID,D.DesigName,A.desigid, EmpName, GS,
DENSE_RANK() OVER(PARTITION BY A.Desigid ORDER BY GS DESC) AS RowNum
FROM tblEmp_Info As A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where A.DeptId=4 and A.IsInactive=0
)
SELECT DeptName,DesigName, EmpName, GS
FROM cteRowNum
WHERE RowNum = 1;
Maciej Los 9-Apr-17 13:55pm    
You're very welcome!
Cheers,
Maciej
Use aggregate function with group by clause on the selected columns on which u want to retrieve.

select B.DeptName,A.DeptId,D.DesigName,A.DesigID, max(GS) as MaxSal,min(GS) as MinSal from tblEmp_Info AS A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where IsInactive=0 and A.DeptId=4
group by B.DeptName,A.DeptId,D.DesigName,A.DesigID,GS
order by A.deptid,A.desigid,GS desc
 
Share this answer
 
v2
Comments
Glorious007 9-Apr-17 6:26am    
i see here is MaxSal and MinSal column both are same show. i need individual maximum and minimum code.
such as: empid DeptName designame gs
5 Garments Ass.Enginer 20,000(Max)
200 Garments Sr. Engineer 30,000(Max)

empid DeptName designame gs
20 Garments Ass.Enginer 7,000(Min)
45 Garments Sr. Engineer 12,000(Min)
Ramesh Kumar Barik 9-Apr-17 7:35am    
Is one employee having multiple designation?
Can you pls share the some data of your tables in scree shot?
Glorious007 9-Apr-17 8:30am    
one id for one designation, here is group Department, and designation
here i can't show screen shot. please give me your email id.
Glorious007 9-Apr-17 8:49am    
now solve my problem. Many Many Thanks Ramesh Kumar Barik .

WITH cteRowNum AS (
SELECT B.DeptName,A.DeptID,D.DesigName,A.desigid, EmpName, GS,
DENSE_RANK() OVER(PARTITION BY A.Desigid ORDER BY GS DESC) AS RowNum
FROM tblEmp_Info As A
inner join tblCat_Department AS B ON A.DeptId=B.DeptID
inner join tblCat_Desig AS D ON A.DesigId=D.DesigId
where A.DeptId=4 and A.IsInactive=0
)
SELECT DeptName,DesigName, EmpName, GS
FROM cteRowNum
WHERE RowNum = 1;
Ramesh Kumar Barik 9-Apr-17 9:00am    
Good to know problem solved.. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900