Stored Procedure for calculating total no of leaves

I have 2 tables 'Employee' and 'Leaves'.

Employee Table having fields - Empid, Empname, Designation, Department

and

Leave Table having fields - Empid, LeaveId, Fromdate, Todate, NoOfDays, Reason, LeaveStatus

I want a procedure which will use cursor to loop through employee ids taken from employee table and then calculate the total number of leaves for all 12 months of a given year.

The output should be in a table format having columns

empid, empname, department, year, month1, month2, month3, month4, month5 ..... month12

Sample of leave table below

hi,

you can use PIVOT table in SQL to achive your desired out put

try following code.

create table #emp
(
Empid int,
Empname varchar(20),
Designation varchar(10),
Department varchar(5)
)

insert into #emp values(100,'Jignesh','SSE','New'),
(101,'Tejas','SSE','New'),
(102,'Rakesh','SSE','New')

Create table #leave
(
Empid int,
LeaveId varchar(2),
Fromdate date,
Todate date,
NoOfDays int
)

insert into #leave values(100,'L1','2008-05-10','2008-05-13',3),
(100,'L1','2008-05-10','2008-05-13',3),
(100,'L2','2008-05-20','2008-05-21',1),
(101,'L3','2008-05-10','2008-05-13',3),
(102,'L4','2008-05-10','2008-05-13',3),
(101,'L5','2008-07-10','2008-07-13',3),
(102,'L6','2008-08-10','2008-08-13',3),
(100,'L7','2008-06-10','2008-06-13',3)

SELECT *
FROM
(
SELECT
   p.Empid,
   e.Empname,
   e.Department,
   DATENAME(year, p.Fromdate) year,
      DATENAME(month, p.Fromdate) mth,
      p.NoOfDays as Total
FROM #leave p
join #emp e on p.Empid = e.Empid
) x
PIVOT
(
SUM(Total)
FOR
     mth IN ([January], [February], [March], [April], [May],
             [June], [July], [August], September, [October],
             [November], [December])
) p

hope this will help you.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
hi,
you can use PIVOT table in SQL to achive your desired out put
try following code.
create table #emp
(
Empid int,
Empname varchar(20),
Designation varchar(10),
Department varchar(5)
)
insert into #emp values(100,'Jignesh','SSE','New'),
(101,'Tejas','SSE','New'),
(102,'Rakesh','SSE','New')
Create table #leave
(
Empid int,
LeaveId varchar(2),
Fromdate date,
Todate date,
NoOfDays int
)
insert into #leave values(100,'L1','2008-05-10','2008-05-13',3),
(100,'L1','2008-05-10','2008-05-13',3),
(100,'L2','2008-05-20','2008-05-21',1),
(101,'L3','2008-05-10','2008-05-13',3),
(102,'L4','2008-05-10','2008-05-13',3),
(101,'L5','2008-07-10','2008-07-13',3),
(102,'L6','2008-08-10','2008-08-13',3),
(100,'L7','2008-06-10','2008-06-13',3)

SELECT *
FROM
(
SELECT
   p.Empid,
   e.Empname,
   e.Department,
   DATENAME(year, p.Fromdate) year,
      DATENAME(month, p.Fromdate) mth,
      p.NoOfDays as Total
FROM #leave p
join #emp e on p.Empid = e.Empid
) x
PIVOT
(
SUM(Total)
FOR
     mth IN ([January], [February], [March], [April], [May],
             [June], [July], [August], September, [October],
             [November], [December])
) p
hope this will help you.
+1