SQL for Elite!

Mining the Enterprise Database John Ashley Financial Systems Administrator Moore & Van Allen PLLC

♦ Database Structures ♦ Major Tables & Indices ♦ SQL Basics ♦ Joins ♦ Query Examples ♦ Tips & Tricks

Agenda

2

Database Structures

♦ Elite Documentation – Heavy SQL users, print a copy out to have handy – See table structures in Query Analyzer or Management Studio’s Object Explorer 3

Major Elite Tables & Indices

♦ matter – mmatter, mclient, mbillaty ♦ client – clnum, crelated ♦ timekeep – tkinit ♦ ledger – lindex, lmatter, llcode, lbatch ♦ timecard – tindex, tmatter, ttk, tbatch ♦ cost – cindex, cmatter, ctk, ccode, cbatch ♦ batch – bbatch ♦ trsttran – trindex, tmatter, tracct, trbatch ♦ prohead – phindex, phmatter, phbatch ♦ udf – udfindex, udjoin ♦ ap – apnum ♦ apaddr – apnum, apaddid ♦ apvo – vo_id, apnum, apaddid ♦ csd – cknum, baid, apnum 4

SQL Basics

♦ SELECT – Identifies the columns in the resulting output ♦ FROM – Identifies the source tables ♦ WHERE – Applies criteria to the output ♦ GROUP BY – Applies grouping when using summary functions ♦ HAVING – Applies criteria based on GROUP BY ♦ ORDER BY – Applies sorting to the output 5

Aliases

♦ Alias a column or table within the SELECT or FROM clause – SELECT matter.mmatter matnum … – SELECT matter.mmatter AS matnum … ♦ Reference the columns using the table alias – Helpful when joining the same table multiple times in the same query 6

♦ INNER JOIN – Returns only records from two tables that match ♦ LEFT OUTER JOIN – Returns all records from left table and any records from the right table that match ♦ RIGHT OUTER JOIN – Returns all records from the right table and any records from the left table that match – Usually best to rewrite as a LOJ

Joins

7

Methods of Joining

♦ Join in the WHERE clause – SELECT client.clname1, matter.mdesc1

FROM client, matter WHERE client.clnum = matter.mclient

– Inner Join (=); Left Outer Join (*=), Right Outer Join (=*) ♦ Join in the FROM clause – SELECT client.clname1, matter.mdesc1

FROM client INNER JOIN matter ON client.clnum = matter.mclient

– Preferred Method ◊ Easier to read ◊ Helps avoid Cartesian joins ◊ ANSI Standard 8

Left Outer Join Sample

♦ Number of open matters by billing attorney for all Senior Partners Inner Join Outer Join (correct) 9

Querying Tip #1

♦ Build queries one table at a time – Start with the specific population ◊ Information on Non-Terminated Associates SELECT * FROM timekeep WHERE tktitle = ‘Associate’ AND tktmdate IS NULL ◊ Note the number of records returned – Add additional tables – Ensure record count remains constant 10

Criteria in Outer Joins

♦ Use criteria within the join – Often necessary with outer joins Criteria in WHERE Criteria in FROM 66 records 67 records 11

Limiting Result Sets

♦ Two options to limit your result set to a specific number of records – Top 10 Members by worked hours for 2009 TOP SET ROWCOUNT 12

♦ UDF fields in Elite can be set up for many different types of records – Each UDF has an index and is tied to a UDF type (i.e. matter, client, timekeep, vendor, etc.) – For validated fields, it is easy to find the index number by pulling up a record in the master file

UDF Fields

13

UDF Fields, cont.

♦ UDF list can be printed from Elite under Setups / Other / User Defined Fields ♦ Alternatively, the code below will give you a list of UDFs sorted by type and line # – Keep handy if you will be querying on UDFs often 14

UDF Fields, cont.

♦ UDF values are stored in the

udf

table ♦ UDF validation descriptions are stored in the

udfval

table ♦ Example Query: – Firms that use Whitehill likely have a matter udf called Total Hours Billing that displays the total hours on an invoice. How many open matters are set to display or not display Total Hours?

15

Summary Tables

♦ Summary tables in Elite are used for Inquiry.

– matths ◊ Summary statistics by matter by period – mattimhs ◊ Summary statistics by matter by working timekeeper by period – timewahs ◊ Summary statistics by working timekeeper by period ♦ Make sure you pick the right column for the exact data that you need 16

Summary Table Examples

♦ Year-to-date Collections for all matters of a client – Collections will match Matter Inquiry in Elite 17

Summary Table, cont.

♦ Top 10 Clients by worked hours for a timekeeper 18

periodt Table

♦ When querying across multiple periods, I highly recommend using the periodt table.

Not using periodt Using periodt 19

Transaction Volume Samples

♦ It is often helpful to see how much data is being processed within Elite over a given date range – Trust Volume Example – Number and absolute dollar value of transactions by period 20

Transaction Volumes, cont.

♦ Billing volume example – Number, amount and average of invoices processed by billing operator 21

Cost Write-down Sample

♦ Clients with more than $1,000 in cost write-downs for the current period 22

Time Card Sample

♦ Latest time entry and timekeeper by client – Use sub-queries 23

Net Investment Sample

♦ WIP & AR by client and matter 24

AP Voucher Sample

♦ This query will return any vouchers that may be duplicates 25

AP Voucher / Cost Sample

♦ This query will return all unbilled client costs for a specific vendor 26

Tips & Tricks

♦ Use a read-only login when using Query Analyzer or SQL Server Management Studio – You don’t want to make any inadvertent changes ♦ Use UPPER-CASE for all SQL specific words – This makes queries much easier to read when revisiting later ♦ Use TOP clause to limit results when testing – SELECT TOP 100 * FROM client ORDER BY clnum – This is especially helpful when determining which columns to pull into result set 27

♦ Comment your code!

– use two dashes to comment out the remainder of a line – use slash star ( /* ) to begin commenting and star slash ( */ ) to close comment

Tips & Tricks

28

Acknowledgements

♦ ILTA ♦ Moore & Van Allen ♦ ILTA Thomson Elite Listserv participants ♦ Elite 29

Contact Info

John Ashley Financial Systems Administrator Moore & Van Allen PLLC [email protected]

704-331-3578 30