Combine Multiple Lines Into One 2

[scoobyroo]

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)

 

[JeffModen]

Ummm... why do you need to present the data in that fashion? Where will the data be used? This usually isn't a good thing to do and the answer may help us give you a better solution.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Agreed. This is usually the kind of thing that your client program (whatever it is) should be doing.

 

[scoobyroo]

They want to see this information on a report.

 

[Emtucifor]

What is your reporting application? Do a group by employee and have a sub-object (table or subreport or something) list all the leave periods.

 

[JeffModen]

scoobyroo (Programmer) 9 Oct 09 19:31
They want to see this information on a report.

Yeah... I figured that... a real business reason would have been better but whatever. ;-) Still I'd like to know... "Where will the data be used?" It could make a difference.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[scoobyroo]

The data will be published in a report on our reporting site. It is called Expert Report Center by Aderant. The report designer is not that great so I was hoping to do this in SQL.

 

[Emtucifor]

Not-So-Expert Report Center by Aberrant? I've never heard of that, but it doesn't inspire confidence.

Seriously, if your report program can't do a subreport or a subtable, fire it. SSRS is free, you know...

Next, even if you MUST do it in SQL, how many leave periods are possible to get per employee? The text could get really ugly. Anyway, I can show you a way to do this, but I need to know your version of SQL Server (2000, 2005, 2008) so I don't waste time using SQL 2000 syntax if you have SQL 2005.

Last, the @BeginPeriod and @EndPeriod WHERE conditions need some adjustment. Applying a function to a column means the function has to run once for each row--a sure performance degradation. Instead, rewrite the conditions so the functions are performed on the input variables instead. Plus, you may want to review a wiki page I wrote, Date Range WHERE Clause Simplification to understand my following suggestion:

ALTER PROCEDURE dbo._usp_report_FTE_Leaves
AS
SET NOCOUNT ON -- this should be standard in your SPs
@BeginPeriod datetime, -- pass in any date in the desired month
@EndPeriod datetime -- pass in any date in the desired month
AS

...

WHERE
   L.LeaveEndDate >= DateAdd(mm, DateDiff(mm, 0, @BeginPeriod), 0) -- Leave ends after the desired period begins
   AND L.LeaveBeginDate < DateAdd(mm, DateDiff(mm, 0, @EndPeriod) + 1, 0) -- Leave begins before the desired period ends

Assuming your dates are really dates, then the exact expressions I've given you are (I believe) correct, down to the >= for the first condition and < for the second (not <=). (I made the @EndPeriod refer to the 1st of the month after, but that condition is exclusive of the endpoint, so it only pulls dates in the previous month.)

If this isn't clear or you have any further questions about these date calculations, please ask. In any case, I guarantee that these two conditions with functions that get run only once per parameter is way better than four conditions with functions that get run on every row in the table.

 

[scoobyroo]

I'm using SQL 2005. The most leaves I have seen so far is 3 per an employee. Not to say they can never have more than that, but so far that is all they have.

Thanks for the advice on the dates. Makes sense.

 

[Emtucifor]

Okay, so there isn't really any great way to do this. SQL Server doesn't have a text concatenation aggregate function.

Here are some possible methods:

1. Put the raw query of Employees and their leave periods into a temp table. Put the distinct list of employees into a second temp table with a blank leave description column, then loop through the first temp table to update the descriptions in the second. This would be one loop per max number of leave periods per employee.

2. Use the same temp tables as the first, but loop through each employee and build the leave description for each one and update it in the second table.

3. Create a UDF that builds the leave description for each employee and call it once per row.

4. Use the manual pivot technique with enough expressions to cover the greatest possible number of leave periods. In some ways, this is better than all the others, but it also suffers from being very big and unwieldy, and risks losing data if the number of leave periods is unexpectedly high some time.

(Of course, all of these risk losing data if the description exceeds the column limit. Using varchar(8000) is probably enough, but, maybe not. So varchar(max) can come into play but performance can be lower with this.)

5. Create a CLR assembly in .Net that performs concatenation and use it in one of several ways. There are articles on this. It seems cool, but I don't recommend it unless your database is already using a lot of CLR. You wouldn't want to get into it just for one situation like this.

6. Leave everything in rows but make the employee name blank when it's not the first row. This can give the appearance of having everything grouped without actually being that way.

I'm going to show you #3 and #6. #3 is loop-based and so it ultimately sucks, but it works and is easy to show.

-- UDF
GO
CREATE FUNCTION dbo._usp_report_FTE_Employee_LeaveDescription (
   @EmployeeID int,
   @BeginPeriod datetime, -- must be the 1st of the month beginning the period, at exactly midnight
   @EndPeriod datetime -- must be the 1st of the month after the desired period, at exactly midnight
)
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @LeaveDescription varchar(8000)
   SELECT @LeaveDescription =
      Coalesce(@LeaveDescription + '; ', '')
      + L.LeaveTypeID + ': ' + CONVERT(varchar(30), L.LeaveBeginDate, 107)
      + ' to ' + CONVERT(varchar(30), L.LeaveEndDate, 107)
      + ', ' + CASE WHEN L.IsPaidFlag IS NULL THEN 'Unpaid' ELSE 'Paid' END
   FROM EmployeeLeave L
   WHERE
      L.EmployeeID = @EmployeeID
      AND L.LeaveEndDate >= @BeginPeriod -- Leave ends after the desired period begins
      AND L.LeaveBeginDate < @EndPeriod -- Leave begins before the desired period ends
   RETURN @LeaveDescription
END

CREATE PROCEDURE dbo._usp_report_FTE_Leaves
   @BeginPeriod datetime, -- pass in any date in the desired month
   @EndPeriod datetime -- pass in any date in the desired month
AS
SET NOCOUNT ON
SET @BeginPeriod = DateAdd(mm, DateDiff(mm, 0, @BeginPeriod), 0)
SET @EndPeriod = DateAdd(mm, DateDiff(mm, 0, @EndPeriod) + 1, 0)
SELECT
   E.EmployeeID,
   EmployeeName = E.LastName + ', ' + E.FirstName,
   E.Title,
   LeaveDescription = dbo._usp_report_FTE_Employee_LeaveDescription(E.EmployeeID, @BeginPeriod, @EndPeriod)
FROM
   Employees E
WHERE
   EXISTS (
      SELECT 1
      FROM EmployeeLeave L
      WHERE
         E.EmployeeID = L.EmployeeID
         AND L.LeaveEndDate >= @BeginPeriod -- Leave ends after the desired period begins
         AND L.LeaveBeginDate < @EndPeriod -- Leave begins before the desired period ends
   )

And now the "empty cells" version that only makes it LOOK like it's grouped. And you can order by EmployeeID, Selector to make sure the rows come out in the right order.

CREATE PROCEDURE dbo._usp_report_FTE_Leaves
AS
SET NOCOUNT ON -- this should be standard in your SPs
   @BeginPeriod datetime, -- pass in any date in the desired month
   @EndPeriod datetime -- pass in any date in the desired month
AS
SET @BeginPeriod = '20091005'
SET @EndPeriod = '20091105'
SELECT
   Selector = Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate),
   E.EmployeeID,
   EmployeeName = CASE WHEN Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate) = 1 THEN E.LastName + ', ' + E.FirstName ELSE NULL END,
   Title = CASE WHEN Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate) = 1 THEN E.Title ELSE NULL END,
   LeaveDescription = Convert(varchar(11), Row_Number() OVER (PARTITION BY E.EmployeeID ORDER BY L.LeaveBeginDate))
      + '. ' + L.LeaveTypeID + ': ' + CONVERT(varchar(30), L.LeaveBeginDate, 107)
      + ' to ' + CONVERT(varchar(30), L.LeaveEndDate, 107)
      + ', ' + CASE WHEN L.IsPaidFlag IS NULL THEN 'Unpaid' ELSE 'Paid' END
FROM
   Employees E
   INNER JOIN EmployeeLeave L ON E.EmployeeID = L.EmployeeID
WHERE
   L.LeaveEndDate >= DateAdd(mm, DateDiff(mm, 0, @BeginPeriod), 0) -- Leave ends after the desired period begins
   AND L.LeaveBeginDate < DateAdd(mm, DateDiff(mm, 0, @EndPeriod) + 1, 0) -- Leave begins before the desired period end

I have to say, this solution is pretty slick.

I tested these and they work. You'll have to put your db names back in there.

Erik

P.S. You should never use a data type declaration without a length for those data types that accept a length. Specifically, Convert(varchar, expression) is not best practice. It will bite you some day. You must always specify a length as in Convert(varchar(30), expression).

 

[scoobyroo]

Thank you so much for the advice on this one. I have tried the two solutions you gave examples of and both work for me. Thanks again.

 

[Emtucifor]

Then by all means use the second one. The first solution is a loop that we do not want to use unless forced to.

 

[JeffModen]

You can also do this without the overhead or performance hit of a scalar UDF. And, your choice as to whether you use the code 100% inline, as a stored proc, or as an inline function. Here's the code...

--=====================================================================================================
--      Solve the problem using XML concatenation for whole table without using a scalar UDF
--      or any kind of explicit RBAR.  This is one of the places where XML is very fast.
--      This can easily be converted to either a stored proc, a high speed inline table valued 
--      function, or just as a script depending on what your requirements are.
--      You would also need to convert 
--=====================================================================================================
--===== These could be parameters in a high speed inline table valued function or stored proc
DECLARE @BeginPeriod INT,
        @EndPeriod   INT;

--===== Remove these presets for an inline TVF or stored proc
 SELECT @BeginPeriod = 20090615, --Any date in the given month will do
        @EndPeriod   = 20090612; --Any date in the given month will do

WITH
cteFindPeriodData AS
(--==== Find all the data for the given period to make grouping easier.
     -- Be sure to see the note on the INNER JOIN line.
 SELECT e.EmployeeID,
        e.LastName + ', ' + e.FirstName AS [Name],
        e.Title,
        l.LeaveTypeID + ': ' 
      + CONVERT(VARCHAR, l.LeaveBeginDate, 107) + ' to ' 
      + CONVERT(VARCHAR, l.LeaveEndDate  , 107) + ', ' 
      + CASE WHEN l.IsPaidFlag <> 0 --Takes care of NULLs, too.
             THEN 'Paid' 
             ELSE 'Unpaid' 
        END  AS Leave,
        l.LeaveBeginDate
   FROM Timekeeper.dbo.EmployeeLeave l
  INNER JOIN Timekeeper.dbo.Employees e ON l.EmployeeID = e.EmployeeID    --Change to RIGHT JOIN to include "empty cells"
  WHERE l.LeaveBeginDate < DATEADD(mm,DATEDIFF(mm,-1,STR(@EndPeriod)),-1) --Leave begins before period enddate
    AND l.LeaveEndDate  >= DATEADD(mm,DATEDIFF(mm,0,STR(@EndPeriod)),0)   --Leave ends after period startdate
)
 SELECT fpd.EmployeeID,
        fpd.[Name],
        fpd.Title,
        STUFF(--==== "Stuff" gets rid of the first delimiter pair
                (--==== Concatenate using XML in a correlated subquery
                 SELECT '; ' + fpd2.Leave
                   FROM cteFindPeriodData fpd2
                  WHERE fpd2.EmployeeID = fpd.EmployeeID --Correlation here
                  ORDER BY fpd2.LeaveBeginDate --Sorts the concatenated value
                    FOR XML PATH('')
                )
        ,1,2,'') AS Leave
   FROM cteFindPeriodData fpd
  GROUP BY fpd.EmployeeID, fpd.Name, fpd.Title -- without GROUP BY multiple rows are returned
  ORDER BY fpd.Name;

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Jeff,

Did you see my second solution in the post above at all? It works fine for the poster, is totally set-based, and is way simpler than using FOR XML to concatenate.

 

[JeffModen]

Ah... no... I got half way through the post and didn't realize your second proc didn't use a function. I'll give it a try. Thanks.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

Hust a heads up... you must have modified the code after you tested it... I sometimes make the same mistake when I edit the code after testing it...

CREATE PROCEDURE dbo._usp_report_FTE_Leaves
AS
SET NOCOUNT ON -- this should be standard in your SPs
@BeginPeriod datetime, -- pass in any date in the desired month
@EndPeriod datetime -- pass in any date in the desired month
AS
SET @BeginPeriod = '20091005'
SET @EndPeriod = '20091105'
SELECT

One too many "AS" and the SET NOCOUNT ON is in the wrong place.

Anyway, I ran your good code and it appears that you were able to change the OP's mind as to not doing any concatenation. That's a good thing. If the OP can't change the mind of the folks driving him, then the XML code will be helpful because it doesn't use a function.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Yeah... I found my mistake later but forgot to mention it. Sorry about the mistake. I was doing testing without using a stored procedure, and left in pieces that I meant to remove!

 

[JeffModen]

Heh... np. I was actually happy to see that I wasn't the only one to make such a mistake.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row