300 likes | 985 Vues
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. Database Structures. Elite Documentation
E N D
SQL for Elite! Mining the Enterprise Database John AshleyFinancial Systems AdministratorMoore & Van Allen PLLC
Agenda • Database Structures • Major Tables & Indices • SQL Basics • Joins • Query Examples • Tips & Tricks
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
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
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
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
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
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
Left Outer Join Sample • Number of open matters by billing attorney for all Senior Partners Inner Join Outer Join (correct)
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
Criteria in Outer Joins • Use criteria within the join • Often necessary with outer joins Criteria in WHERE Criteria in FROM 66 records 67 records
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
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
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
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?
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
Summary Table Examples • Year-to-date Collections for all matters of a client • Collections will match Matter Inquiry in Elite
Summary Table, cont. • Top 10 Clients by worked hours for a timekeeper
periodt Table • When querying across multiple periods, I highly recommend using the periodt table. Not using periodt Using periodt
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
Transaction Volumes, cont. • Billing volume example – Number, amount and average of invoices processed by billing operator
Cost Write-down Sample • Clients with more than $1,000 in cost write-downs for the current period
Time Card Sample • Latest time entry and timekeeper by client • Use sub-queries
Net Investment Sample • WIP & AR by client and matter
AP Voucher Sample • This query will return any vouchers that may be duplicates
AP Voucher / Cost Sample • This query will return all unbilled client costs for a specific vendor
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
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
Acknowledgements • ILTA • Moore & Van Allen • ILTA Thomson Elite Listserv participants • Elite
Contact Info John Ashley Financial Systems Administrator Moore & Van Allen PLLC johnashley@mvalaw.com 704-331-3578