List the details of employee ,department with a reference column

Question

Here I have 2 tables, Employee and Department, and the data as follows.

Employee:

Empid   Empname Deptid  salary
-----------------------------------------
1       rama    2       20000.00
2       sita    2       30000.00
3       gita    4       45000.00
4       rohit   4       40000.00
5       lata    5       50000.00
6       sami    2       23000.00
7       lala    3       35000.00
8       samta   4       41000.00
9       shika   5       55000.00
10      venu    4       4400.00

Department:

Deptid DeptName       DeptReference
---------------------------------------
1      HR                1
2      Engineering       2
3      marketing         1
4      Planning          2
5      Admin             1
6      sales             2

The required output is

• List of all departments with Employee details for any n number of employees where n = dept reference for dept.
• If n exceeds that actual employee count in that dept then as many existing employees to be shown while the rest would show null values

Output: (Deptid, Deptname, empid, empname)

Thanks in advance

Narendra

Answer 1

You don't say what criteria you will use to choose which employees you want to see for each department. My example shows the highest paid employee(s). You should be able to change this easily for whatever you choose.

select
    *
from
(
    select
        e.*
        ,d.DeptReference
        ,ROW_NUMBER() OVER (partition by e.Deptid order by e.salary desc) as Row
    from Employee as e
    inner join Department as d
        on e.Deptid = d.Deptid
)as xx
where xx.Row <= xx.DeptReference;

Answer 2

The first step to doing this is to get n number of rows per department (where n is DeptReference), you can do this by cross joining to a numbers table, I will assume you don't have one and create one on the fly each time, but if you do then great you can just use that:

SELECT  *
FROM    Department d
        CROSS JOIN
        (   SELECT  Number = ROW_NUMBER() OVER(ORDER BY o.object_id) 
            FROM    sys.all_objects o
        ) n
WHERE   n.Number <= d.DeptReference;

Or

SELECT  *
FROM    Department d
        CROSS JOIN Numbers n
WHERE   n.Number <= d.DeptReference;

So this will give something like

Deptid DeptName       DeptReference Number
1      HR                1          1
2      Engineering       2          1
2      Engineering       2          2

So engineering is duplicated. You then need to put your employees in some kind of order by department using ROW_NUMBER, I don't know what your order should be so will just use EmpID:

SELECT  e.DeptID, 
        e.EmpID,
        e.EmpName,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY e.DeptID ORDER BY e.EmpID)
FROM    Employee e;

So for DeptID = 4 this will give:

Empid   Empname Deptid  RowNUmber
3       gita    4       1
4       rohit   4       2
8       samta   4       3
10      venu    4       4

Then it is just a matter of joining the Number column created on department to the RowNumber column created for employee.

N.B. I can't work out if you want to limit the employees shown (i.e If DeptReference is 2 and there are 3 employees then only show 2), If you want to show all of them then just uncomment the clause commented out

SELECT  d.DeptID,
        d.DeptName,
        e.EmpID,
        e.EmpName
FROM    Department d
        CROSS JOIN
        (   SELECT  Number = ROW_NUMBER() OVER(ORDER BY o.object_id) 
            FROM    sys.all_objects o
        ) n
        LEFT JOIN
        (   SELECT  e.DeptID, 
                    e.EmpID,
                    e.EmpName,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY e.DeptID ORDER BY e.EmpID)
            FROM    Employee e
        ) e
            ON e.DeptID = d.DeptID
            AND e.RowNumber = n.Number
WHERE   n.Number <= d.DeptReference
--OR        e.EmpID IS NOT NULL
ORDER BY DeptID, EmpID;

Since there are no employees in the Sales department in your sample data, and the DeptReference is 2, then this will generate the following for Sales:

DeptID  DeptName    EmpID   EmpName
6       sales       (null)  (null)
6       sales       (null)  (null)

Example on SQL Fiddle

Answer 3

If query should ignore extra additional employees beyond count of n for each department, then Try this:

 with DeptEmps (deptId, empSequence) As
   (Select deptId, DeptReference
    From Departments d 
    union all
    Select deptId, empSequence - 1 
    From DeptEmps 
    Where empSequence > 1)

 Select d.DeptId, DeptName, 
    EmpId, EmpName, Salary
 From Departments d
    join DeptEmps de 
       on de.deptId = d.DeptId
   Left Join Employees e
       on e.DeptId = d.DeptId
          And de.empSequence =
            (Select Count(*)
             From Employees
             Where DeptId = e.DeptId
                and EmpId <= e.EmpId)
  Order By d.Deptname

If query should include extra additional employees beyond count of n for each department, then it gets a bit more complex:

with DeptEmps (deptId, empSequence) As
  (Select deptId, 
      Case When d.DeptReference > 
          (Select Count(*) From Employees 
           Where DeptId = d.DeptId) Then d.DeptReference
   Else  (Select Count(*) From Employees 
              Where DeptId = d.DeptId) End empSequence  
   From Depts d 
      union all
   Select deptId, empSequence - 1 
   From DeptEmps 
   Where empSequence > 1)
Select  d.DeptId, DeptName, 
   EmpId, EmpName, Salary
From Depts d
   join DeptEmps de 
      on de.deptId = d.DeptId
   left Join Employees e
     on e.DeptId = de.DeptId
        And (Select Count(*)
             From Employees
             Where DeptId = e.DeptId
                and EmpId <= e.EmpId)
         = de.empSequence          
Order By d.Deptname