I have the following stored procedure to obtain information on attorneys who have been on leave during a specified time frame. My problem is some people could have been on leave multiple times and I'm not sure what the best way to combine the information on one line would be. The procedure is below.
HOW DATA IS STORED IN TABLE
TK TK Name Title Leave
2492 Esson, Jr., Kenneth Associate Other Leave: Dec 29, 2008 to Mar 01, 2009, Paid
2492 Esson, Jr., Kenneth Associate Other Leave: Jun 11, 2009 to Jun 21, 2009, Paid
OUTPUT NEEDED
2492 Esson, Jr., Kenneth Associate Other Leave: Dec 29, 2008 to Mar 01, 2009, Paid; Other Leave: Jun 11, 2009 to Jun 21, 2009, Paid
ALTER PROCEDURE dbo._usp_report_FTE_Leaves
@BeginPeriod int
, @EndPeriod int
AS
SELECT e.EmployeeID
, e.LastName + ', ' + e.FirstName Name
, e.Title
, l.LeaveTypeID + ': ' + CONVERT(varchar, l.LeaveBeginDate, 107) + ' to ' + CONVERT(varchar, l.LeaveEndDate, 107)
+ ', ' + CASE WHEN l.IsPaidFlag is not null THEN 'Paid' ELSE 'Unpaid' END Leave
FROM Timekeeper.dbo.EmployeeLeave l
INNER JOIN Timekeeper.dbo.Employees e ON l.EmployeeID = e.EmployeeID
WHERE (datepart(yy, l.LeaveBeginDate) * 100 + DATEPART(mm, l.LeaveBeginDate) between @BeginPeriod and @EndPeriod
or datepart(yy, l.LeaveEndDate) * 100 + DATEPART(mm, l.LeaveEndDate) between @BeginPeriod and @EndPeriod)
HOW DATA IS STORED IN TABLE
TK TK Name Title Leave
2492 Esson, Jr., Kenneth Associate Other Leave: Dec 29, 2008 to Mar 01, 2009, Paid
2492 Esson, Jr., Kenneth Associate Other Leave: Jun 11, 2009 to Jun 21, 2009, Paid
OUTPUT NEEDED
2492 Esson, Jr., Kenneth Associate Other Leave: Dec 29, 2008 to Mar 01, 2009, Paid; Other Leave: Jun 11, 2009 to Jun 21, 2009, Paid
ALTER PROCEDURE dbo._usp_report_FTE_Leaves
@BeginPeriod int
, @EndPeriod int
AS
SELECT e.EmployeeID
, e.LastName + ', ' + e.FirstName Name
, e.Title
, l.LeaveTypeID + ': ' + CONVERT(varchar, l.LeaveBeginDate, 107) + ' to ' + CONVERT(varchar, l.LeaveEndDate, 107)
+ ', ' + CASE WHEN l.IsPaidFlag is not null THEN 'Paid' ELSE 'Unpaid' END Leave
FROM Timekeeper.dbo.EmployeeLeave l
INNER JOIN Timekeeper.dbo.Employees e ON l.EmployeeID = e.EmployeeID
WHERE (datepart(yy, l.LeaveBeginDate) * 100 + DATEPART(mm, l.LeaveBeginDate) between @BeginPeriod and @EndPeriod
or datepart(yy, l.LeaveEndDate) * 100 + DATEPART(mm, l.LeaveEndDate) between @BeginPeriod and @EndPeriod)