SQL for Elite!

1. SQL for Elite! Mining the Enterprise Database John AshleyFinancial Systems AdministratorMoore & Van Allen PLLC

2. Agenda • Database Structures • Major Tables & Indices • SQL Basics • Joins • Query Examples • Tips & Tricks

3. 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

4. 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 Major Elite Tables & Indices

5. 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

6. Aliases • Alias a column or table within the SELECT or FROM clause • SELECT matter.mmatter matnum … • SELECT matter.mmatter AS matnum … • Reference the columnsusing the table alias • Helpful when joiningthe same table multipletimes in the same query

7. 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

8. Methods of Joining • Join in the WHERE clause • SELECT client.clname1, matter.mdesc1FROM client, matterWHERE client.clnum = matter.mclient • Inner Join (=); Left Outer Join (*=), Right Outer Join (=*) • Join in the FROM clause • SELECT client.clname1, matter.mdesc1FROM clientINNER JOIN matter ON client.clnum = matter.mclient • Preferred Method • Easier to read • Helps avoid Cartesian joins • ANSI Standard

9. Left Outer Join Sample • Number of open matters by billing attorney for all Senior Partners Inner Join Outer Join (correct)

10. 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

11. Criteria in Outer Joins • Use criteria within the join • Often necessary with outer joins Criteria in WHERE Criteria in FROM 66 records 67 records

12. 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

13. 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

14. 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

15. 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?

16. 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

17. Summary Table Examples • Year-to-date Collections for all matters of a client • Collections will match Matter Inquiry in Elite

18. Summary Table, cont. • Top 10 Clients by worked hours for a timekeeper

19. periodt Table • When querying across multiple periods, I highly recommend using the periodt table. Not using periodt Using periodt

20. 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

21. Transaction Volumes, cont. • Billing volume example – Number, amount and average of invoices processed by billing operator

22. Cost Write-down Sample • Clients with more than $1,000 in cost write-downs for the current period

23. Time Card Sample • Latest time entry and timekeeper by client • Use sub-queries

24. Net Investment Sample • WIP & AR by client and matter

25. AP Voucher Sample • This query will return any vouchers that may be duplicates

26. AP Voucher / Cost Sample • This query will return all unbilled client costs for a specific vendor

27. 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 clientORDER BY clnum • This is especially helpful when determining which columns to pull into result set

28. 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

29. Acknowledgements • ILTA • Moore & Van Allen • ILTA Thomson Elite Listserv participants • Elite

30. Contact Info John Ashley Financial Systems Administrator Moore & Van Allen PLLC johnashley@mvalaw.com 704-331-3578