Fee Discount by Year

This is a quick and dirty look at fee discounts by year for a given client. It utilizes the matter history table.

USE son_db;
GO

DECLARE @client NVARCHAR(10);

SET @client = N'000001';


SELECT
CAST(YEAR(peendt) AS VARCHAR(10)) AS 'Year'
,SUM(mhdowkdb) AS 'Worked'
,SUM(mhdobidb) AS 'Billed'
,SUM(mhdobidb)-SUM(mhdowkdb) AS 'Discount'
,CASE WHEN SUM(mhdowkdb)=0 THEN 0 ELSE (SUM(mhdobidb)-SUM(mhdowkdb))/SUM(mhdowkdb) END AS 'Disc %'
FROM matths
INNER JOIN periodt ON mhper=pe
INNER JOIN matter ON mhmatter=mmatter
WHERE mclient=@client
GROUP BY YEAR(peendt)

UNION

SELECT
'Total' AS 'Year'
,SUM(mhdowkdb) AS 'Worked'
,SUM(mhdobidb) AS 'Billed'
,SUM(mhdobidb)-SUM(mhdowkdb) AS 'Discount'
,CASE WHEN SUM(mhdowkdb)=0 THEN 0 ELSE (SUM(mhdobidb)-SUM(mhdowkdb))/SUM(mhdowkdb) END AS 'Disc %'
FROM matths
INNER JOIN periodt ON mhper=pe
INNER JOIN matter ON mhmatter=mmatter
WHERE mclient=@client

General Ledger Transactions for a Range of Accounts

The following stored procedure will imitate a general ledger transaction report from within Elite, with easy use in Excel. The concept is faily simple with the first SELECT statement pulling Accounts Payable entries and the second SELECT statement pulling non-AP general journal entries. This also uses the GJ entry description user defined function. There are two variables passed to the stored procedure: beginning and ending dates. The GL accounts are hard coded, but could easily be changed to variables.

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'usp_GL_Expenses' 
)
   DROP PROCEDURE dbo.usp_GL_Expenses
GO

CREATE PROCEDURE dbo.usp_GL_Expenses
 @startdate DATETIME = NULL, 
 @enddate DATETIME = NULL
AS

SELECT
CAST(csddt.cknum AS VARCHAR(12)) AS 'Check #/GJ ID'
,csddt.ckline AS 'GJ Line'
,csddt.ckdate AS 'Date'
,MONTH(csddt.ckdate) AS 'Month'
,csddt.apnum AS 'Vendor #'
,csddt.apaddid AS 'Vendor Sub'
,ap.apname AS 'Vendor Name'
,apaddr.appynm AS 'Payee'
,csddt.vo_id AS 'Voucher'
,csddt.glnum AS 'GL Acct'
,csddt.glrc1 AS 'GL Dept'
,gl.gldes AS 'GL Description'
,gl.glnum + ' ' + gl.gldes AS 'GL Acct Description'
,csddt.amt AS 'Amount'
,ap.apct1 AS 'Vendor Type'
,voiddt AS 'Void Date'
,dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS 'Transaction Description'
FROM
csddt
INNER JOIN ap ON csddt.apnum = ap.apnum
INNER JOIN csd ON csddt.cknum = csd.cknum AND csddt.baid = csd.baid
INNER JOIN apaddr ON csddt.apnum = apaddr.apnum AND csddt.apaddid=apaddr.apaddid
INNER JOIN gl ON csddt.glnum = gl.glnum AND csddt.glrc1=gl.glrc1
WHERE
(csddt.glnum BETWEEN '62500' AND '63320'
OR csddt.glnum = '53100'
OR gl.glnum BETWEEN '84100' AND '84199'
OR gl.glnum BETWEEN '84400' AND '84499'
OR csddt.glnum = '93145')
AND csddt.ckdate BETWEEN @startdate AND @enddate

UNION

SELECT
gjid
,gjline
,gjtrdt
,MONTH(gjtrdt)
,NULL
,NULL
,NULL
,NULL
,NULL
,gj.glnum
,gj.glrc1
,gl.gldes
,gl.glnum + ' ' + gl.gldes AS 'glnumdes'
,ISNULL(dbamt,0)-ISNULL(cramt,0)
,NULL
,NULL
,des + ' ' + dbo.udfGJDes(gjid,gjline)
FROM gj
INNER JOIN gl ON gj.glnum=gl.glnum AND gj.glrc1=gl.glrc1
WHERE gjtrdt BETWEEN @startdate AND @enddate
AND (gl.glnum BETWEEN '62500' AND '63320'
OR gl.glnum = '53100'
OR gl.glnum BETWEEN '84100' AND '84199'
OR gl.glnum BETWEEN '84400' AND '84499'
OR gl.glnum = '93145')

GO

To execute the stored procedure, use this statement

EXECUTE dbo.usp_GL_Expenses '1/1/2010','12/31/2010'
GO

Make sure to grant execute to any other users that need access to this stored procedure.

GRANT EXECUTE ON dbo.usp_GL_Expenses TO elitereadonly;
GO

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'

Payments by Invoice Ledger Code

This is a simple little query to see how payments from a particular client have been applied by the original invoice ledger code. For example, you can determine how much of the payments were applied to fees or costs. You may need to tinker with the criteria to match your setups. This uses the matter description udf.

SELECT 
mmatter AS 'Matter #'
,dbo.udfMattDesc(mmatter) AS 'Matter Description'
,SUM(CASE WHEN inv.llcode = 'FEES' THEN pmt.lamount ELSE 0 END) as 'Fees Collected'
,SUM(CASE WHEN inv.llcode <> 'FEES' THEN pmt.lamount ELSE 0 END) AS 'Costs Recovered'
,SUM(pmt.lamount) AS TOTAL
FROM ledger AS pmt
INNER JOIN matter ON pmt.lmatter=mmatter
INNER JOIN ledger AS inv ON pmt.laptoin=inv.lindex
WHERE YEAR(pmt.ltradat)='2009'
AND mclient='014523'
AND pmt.llcode LIKE 'PAY%'
GROUP BY mmatter

Linked Servers

As you know by now, I write quite a bit of SQL for mining data from the Enterprise database. Occasionally, I'll need some data from another database on a different SQL Server to pair with some information from Elite. In SQL Server, you can create a linked server to be able to join across SQL Servers. For example, we use Minisoft's ARCS accounts receivable program, and I've created a linked server to join ARCS notes to real-time Elite data.

• Create a Linked Server to the other SQL Server

• USE master
  GO
  EXEC master.dbo.sp_addlinkedserver
  @server = N'ARCS'
  , @srvproduct=N''
  , @provider=N'SQLOLEDB'
  , @datasrc=N'ARCS-SERVERNAME'
  , @catalog=N'ARCS-DBNAME'
  GO
  

• Add login credentials for the other SQL Server (if you don't use trusted login)

• EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'ARCS'
  , @locallogin = NULL 
  , @useself = N'False'
  , @rmtuser = N'arcs-username'
  , @rmtpassword = N'arcs-password'
  GO
  

• Write Query to access ARCS from the Elite database

• SELECT TOP 100 *
  FROM ARCS.ARCS-DBNAME.dbo.ARCS_HOLDS
  

After setting up the first one of these, I've already contemplated a hundred such uses. It's definitely a handy tool.

Checks for a Vendor with General Ledger Distribution

It is fairly easy to pull a list of checks for a vendor within AP Inquiry in Elite Enterprise. However, sometimes you just need a little more information. The following query will pull a list of checks along with the GL accounts affected by those checks.

A couple things to note:

• Due to the GL distribution piece, there may be more than 1 row per check.
• The cash portion of the GL distribution has been excluded (take out the udtrtp line if you would like that included)
• The glnum and glrc1 are combined into one number
• The user indicated is the user who created the batch. More than one user can post items to a batch.

SELECT ap.apnum AS 'Vendor #'
,ap.apname AS 'Vendor Name'
,csddt.cknum AS 'Check #'
,csddt.ckline AS 'Line'
,csddt.baid AS 'Bank'
,csddt.ckdate AS 'Date'
,csddt.vo_id AS 'Voucher #'
,csddt.glnum+'-'+csddt.glrc1 AS 'GL #'
,gldes AS 'GL Description'
,dbo.udfCsdDes(csddt.baid,csddt.cknum,csddt.ckline) AS 'Description'
,csddt.amt AS 'Amount'
,fmsbatch.opuserid AS 'User'
FROM csddt
INNER JOIN ap ON csddt.apnum=ap.apnum
INNER JOIN fmsbatch ON csddt.cdbtid=fmsbtid AND fmsbatch.trtype='CK'
INNER JOIN gl ON csddt.glnum=gl.glnum AND csddt.glrc1=gl.glrc1
INNER JOIN csd ON csd.baid=csddt.baid AND csd.cknum=csddt.cknum
WHERE csddt.ckdate >= '1/1/2009'
AND ap.apnum = '123456'
AND udtrtp <> 'CA'
AND csd.voiddt IS NULL
ORDER BY csddt.ckdate

Outstanding Trust Checks

In this post, I will be highlighting a common method that I use to automatically pull information from the Elite database directly into MS Excel. I am often asked for a report that is transformed from an ad hoc request into a monthly (or more regularly) deliverable. I try to automate these as much as is possible. As is typical, there's more than one way to accomplish this objective, but here's how I often solve the problem.

1. Write Query to pull the exact information I need. In this case, trust account #606 is required with check dates prior to 1/31/10 (tie to month-end bank statement). This is run after reconciling the account. Therefore, all non-checks should have a presentation flag of 'Y', and are excluded.

SELECT trsttran.trindex AS 'Index #',
matter.mmatter AS 'Matter #',
dbo.udfMattDesc(matter.mmatter) AS 'Matter Description',
dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney',
trsttran.trdate AS 'Date',
trsttran.trdocnum AS 'Check #', 
ap.apname AS 'Vendor',
dbo.udfTrustDesc(trsttran.trindex) AS 'Description',
trsttran.tramount AS 'Amount'
FROM matter
INNER JOIN timekeep ON mbillaty=tkinit
INNER JOIN trsttran ON trmatter = mmatter
LEFT OUTER JOIN trcheck ON trcheck.trindex=trsttran.trindex
LEFT OUTER JOIN ap ON trcheck.trvendor=ap.apnum
WHERE trsttran.tracct = '606'
AND trsttran.trpresflag='N'
AND trsttran.trdate <= '1/31/2010'
AND trsttran.trdocnum Is Not Null
ORDER BY trsttran.trdate

2. At this point, for an adhoc request, I will often copy/paste the report into MS Excel and off we go. However, if I expect to be running this on a frequent basis, I will use the MS Query add-in to MS Excel and paste the SQL into the MS Query. This works like a charm. However, there are often criteria that will need to be changed each time the report is run. In MS Query, you can add parameters by replacing the criteria value with a '?' (i.e. trdate <= ?) This will allow you to either set a prompt to request the value, or reference a specific cell for a value. BUT, this only works if you have a fairly simple query that can be "graphically represented" in MS Query. So if your query can't be "graphically represented", what do you do? Stored Procedure. (it's not as scary as it sounds)

2.A. The code below will create a stored procedure that runs the code above with 2 variables passed to it. First, the bank account and second, the end date for transactions.

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'usp_Trust_OS_Checks' 
)
   DROP PROCEDURE dbo.usp_Trust_OS_Checks
GO

CREATE PROCEDURE dbo.usp_Trust_OS_Checks
 @tracct VARCHAR(20) = NULL, 
 @maxdate DATETIME = NULL
AS

 SELECT trsttran.trindex AS 'Index #',
 matter.mmatter AS 'Matter #',
 dbo.udfMattDesc(matter.mmatter) AS 'Matter Description',
 dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney',
 trsttran.trdate AS 'Date',
 trsttran.trdocnum AS 'Check #', 
 ap.apname AS 'Vendor',
 dbo.udfTrustDesc(trsttran.trindex) AS 'Description',
 trsttran.tramount AS 'Amount'
 FROM matter
 INNER JOIN timekeep ON mbillaty=tkinit
 INNER JOIN trsttran ON trmatter = mmatter
 LEFT OUTER JOIN trcheck ON trcheck.trindex=trsttran.trindex
 LEFT OUTER JOIN ap ON trcheck.trvendor=ap.apnum
 WHERE trsttran.tracct = @tracct
 AND trsttran.trpresflag='N'
 AND trsttran.trdate <= @maxdate
 AND trsttran.trdocnum Is Not Null
 ORDER BY trsttran.trdate

GO

2.B. You can execute the stored procedure as such. This will give you the exact same results as the original query.

EXECUTE dbo.usp_Trust_OS_Checks '606','01/31/2010'
GO

2.C. Next I need to grant permission for our read-only account to execute the stored procedure.

GRANT EXECUTE ON dbo.usp_Trust_OS_Checks TO elitereadonly;
GO

3. Now, how do we get a stored procedure to execute from MS Query within MS Excel?

• Within MS Excel, first choose 2 cells for input for the variables (go ahead and put the data there).
• Choose the Data menu, Import External Data, New Database Query.
• Choose the data source that corresponds to your Elite database.
• After logging in, you should see the query wizard, choose cancel.
• Choose Yes when prompted to continue editing in MS Query.
• Next you should see the "Add Tables" dialog, choose close. You have now arrived at the basic MS Query window (looks remarkably similar to MS Access).
• Under the View menu, choose the SQL option. Use the following code to execute the stored procedure.

• {CALL dbo.usp_Trust_OS_Checks(?,?)}
  

• You will get an alert saying that the SQL cannot be represented graphically, choose OK.
• Next you will be prompted for the two parameters. Enter the data requested (in my case, 606 and 1/31/2010).
• You should now see the data in MS Query.
• From the File menu, choose Return Data to MS Excel.
• You will be prompted with the following dialog box.
• Choose the cell you would like the data to start in, and then choose the Parameters option. Here you have the option to either prompt for the value, use a static value, or get a value from a cell. If you choose the latter, you will have an option to automatically refresh the data when the value changes (if you have more than 1 parameter, I'd recommend not choosing this). When you are done, choose OK and OK and your data should magically appear within MS Excel.

4. Now, anytime someone wants to refresh the information in this report, they can change the parameter values and choose Refresh Data from the data menu (each user must have MS Query installed and the data source defined).