SQL for Elite Mining the Enterprise Database John
SQL for Elite! Mining the Enterprise Database John Ashley Financial Systems Administrator Moore & Van Allen PLLC
Agenda ♦ Database Structures ♦ Major Tables & Indices ♦ SQL Basics ♦ Joins ♦ Query Examples ♦ Tips & Tricks 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 ♦ 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 – bbatch 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
Joins ♦ 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 7
Methods of Joining ♦ Join in the WHERE clause – SELECT client. clname 1, matter. mdesc 1 FROM client, matter WHERE client. clnum = matter. mclient – Inner Join (=); Left Outer Join (*=), Right Outer Join (=*) ♦ Join in the FROM clause – SELECT client. clname 1, matter. mdesc 1 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 ♦ 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 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
Tips & Tricks ♦ 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 28
Acknowledgements ♦ ILTA ♦ Moore & Van Allen ♦ ILTA Thomson Elite Listserv participants ♦ Elite 29
Contact Info John Ashley Financial Systems Administrator Moore & Van Allen PLLC johnashley@mvalaw. com 704 -331 -3578 30
- Slides: 30