General Journal Entry Description User Defined Function

Below is a user defined function to concatenate all lines of a GJ entry description. Unlike most other tables in Elite, the GJ description table only includes records for the second through x lines. The first line of the description is stored in the gj table itself. So, to fully utilize this udf, it will need to be concatenated to the first line from the gj table.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfGJDes] (@gjid VARCHAR(12), @gjline INT)  
RETURNS VARCHAR(1000) AS  
BEGIN 
 DECLARE @description VARCHAR(1000)
 
 SELECT @description = ''
 
 SELECT @description = @description + ' ' + des FROM gjdes WHERE gjid = @gjid AND gjline = @gjline ORDER BY gjline 
 
 RETURN LTRIM(RTRIM(@description))
END

Here's an example of how to use it properly

SELECT
gjid
,gjline
,gjtrdt
,MONTH(gjtrdt)
,gj.glnum
,gj.glrc1
,gl.gldes
,gl.glnum + ' ' + gl.gldes AS 'glnumdes'
,ISNULL(dbamt,0)-ISNULL(cramt,0)
,des + ' ' + dbo.udfGJDes(gjid,gjline)
FROM gj
INNER JOIN gl ON gj.glnum=gl.glnum AND gj.glrc1=gl.glrc1
WHERE gjtrdt BETWEEN '3/1/10' AND '3/31/10'
AND gl.glnum = '20236'

First and Last Day of Month User Defined Functions

I often find myself in a situation where I need to return the first or last day of the month in various queries. Despite this being a seemingly simple process (that I'm sure EVERYONE needs at some point), Microsoft's method of obtaining this data is pointlessly cumbersome. The following piece of code will return the last day of today's month

SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+1, 0))

To me, this is simply ridiculous. First of all, there's so many nested functions in this query that I can't keep them all straight. Next, there's NO way you can remember it all to plug in to a query at any given moment (was this a +1 or a -1 at this spot??). So, below is some code to create two user defined functions that will return either the first day of the month or the last day of the month of any given date passed to the function.

First Day of Month:

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfFirstDayOfMonth]
(
@startDate DATETIME
)
RETURNS DATETIME
AS
BEGIN

RETURN DATEADD(mm, DATEDIFF(mm,0,@startDate), 0)

END

Last Day of Month:

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfLastDayOfMonth]
(
@startDate DATETIME
)
RETURNS DATETIME
AS
BEGIN

RETURN DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@startDate)+1, 0))

END

To use the user defined functions, simply pass a date to the function as a variable in your SELECT statement.

SELECT dbo.udfFirstDayOfMonth(GETDATE()) --First date of this month
SELECT dbo.udfFirstDayOfMonth(GETDATE()-5) --First date of the month 5 days prior to today

This should make using beginning and ending dates of months a bit easier for you.

Check Line Description User Defined Function

Ok, by now you've probably figured out that I'm a huge fan of user defined functions. Well, I am. Just this past week I had the need for yet another concatenation of rows thanks to Elite's bizarre handling of description fields. This one relates to check descriptions. Each GL line of a check has a description attached to it that can span multiple lines in the csddes table. So, the following udf will combine all the description lines for each bank / check / check line combination.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfCsdDes]
(
@baid VARCHAR(8),
@cknum VARCHAR(8),
@ckline INT
)
RETURNS VARCHAR(4800) AS
BEGIN
 DECLARE @description VARCHAR(4800)

 SELECT @description = ''

 SELECT @description = @description + ' ' + des FROM csddes WHERE baid = @baid AND cknum = @cknum AND ckline = @ckline ORDER BY dsline

 RETURN LTRIM(RTRIM(@description))
END

You can call this udf in any query by passing the bankid, check number and check line variable, typically from the csddt table. Example will follow in the next post.

Timekeeper Name User Defined Function

This is similar in nature to the matter description udf described in the last post. The timekeeper name udf will return the last name, first name of the tkinit that is passed in the function.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfTKFullName]
(
@tkinit VARCHAR(5)
)
RETURNS VARCHAR(85)
AS
BEGIN

DECLARE @fullname VARCHAR(85)

SELECT @fullname = tklast + (CASE WHEN tkfirst IS NULL THEN '' ELSE ', ' END) + ISNULL(tkfirst,'')
FROM timekeep
WHERE tkinit = @tkinit

RETURN @fullname

END

To use the user defined function, simply invoke it in the SELECT statement and pass it a value to execute on.

SELECT dbo.udfTKFullName(tkinit)
FROM timekeep
WHERE tkinit = '1234'

Using a UDF here will help you avoid having to CONCATENATE or COALESCE the parts of the name in each query.

Matter Description User Defined Function

In Enterprise, the matter description is split among three fields; mdesc1, mdesc2 and mdesc3. While the mdesc1 field is often enough, many times we need the entire matter description in a report. The following user defined function (udf) automates the combination of the three fields.

Code to create the udf:

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfMattDesc]
(
    @mmatter VARCHAR(15)
)
RETURNS VARCHAR(185)
AS
BEGIN
  DECLARE @mattdesc VARCHAR(185)

   SELECT @mattdesc = mdesc1 + ' ' + ISNULL(mdesc2,'') + ' ' + ISNULL(mdesc3,'')
   FROM matter
   WHERE matter.mmatter = @mmatter

   RETURN RTRIM(@mattdesc)

END

This creates a udf named udfMattDesc that requires a value (mmatter) be passed to it and will return the full matter description of the specified matter. See the following example query to use the udf.

SELECT
matter.mmatter,
client.clname1,
dbo.udfMattDesc(matter.mmatter)
FROM matter
INNER JOIN client ON matter.mclient=client.clnum
WHERE matter.mclient='123456'