2009-02-12

'TBD'

SQL work makes up a large portion of my day, but in various forms. Much of my work has taken place using MS Access, building queries, equivalent to SQL views, to generate management reporting. When needed, which is often, I open and edit the view as SQL as need, or reuse SQL in ways to speed development by reuse of elements. The work for Access encompasses the basic commands of SQL, SELECT, DISTINCT, TOP, AS, DELETE, INSERT INTO, UNION/UNION ALL, GROUP BY (MAX, MIN, COUNT, SUM), ORDER BY, INNER/OUTER JOIN, WHERE, keys, functions, etc. The Access reporting I was hired to build as a temporary solution is after two years being migrated to SQL Server with little modification, encompassing hundreds of tables, and hundreds of views, as well as specialized scalar functions for age and value grouping. I think this speaks to my foresight, planning, and general understanding of good design.

More complex work is typically done using SQL Server, or any DBMS, writing stored procedures/functions/triggers and designing tables to maintain relationships and data integrity. I've provided samples in XLS files that show my basic work, and the following details work done exclusively by myself, and as it specifically applies to SQL:

1. For a Deloitte forensic accounting group, I converted an Excel workbook that used SAS file-based data to use SQL Server. The application was designed to make the accounting data drillable, in that forms would provide server data from which users could select fields and parameters, passing the selections to the stored procedures, and returning the recordset data to pivot charts. In that capacity, I created SQL procedures for loading data (Bulk Insert, Create/Alter Table), and providing interactivity via parameterized stored procedures (#TEMP TABLES, Dynamic SQL, Transactions, transaction audit/logging).

2. For my current client, Transaction Management Group, I was recently asked to build a SQL Server-based application for management, as a prototype of a system they would like to build, to enhance reporting for operational losses, which is named dbIRS Viewer. Although the front-ends are done in Access, the data is normalized on SQL Server. All the data is accessed and modified via stored procedures and views. Some of the SQL is fairly basic, e.g., parameterized SELECT, but a few are more complicated to meet the need for relational data presented in Excel; for that I've used cursors and temp tables to return multiple cells into one. Additionally, I've created functions to provide text value grouping, as well as triggers to update time stamp columns, that I use to prevent users overwriting each other's updates (Optimistic Locking).

As an aside, about 6 months ago, I interviewed for a hedge fund that tested my skills via PreVisor, but before the test I prepared and tested myself via Brainbench. At the time, I was only working in Access, and hadn't done hardcore SQL for about 2 years, but even then, I scored fairly well on a test of SQL (ANSI) Fundamentals. According to Brainbench, I "scored higher than 81% of all previous test takers. Demonstrates a clear understanding of many advanced concepts within this topic. Appears capable of mentoring others on most projects in this area." As for style, although the provided samples don't represent how my code is normally structured - they are downloads into Access tables - I code SQL as cleanly as I code VBA. Other developers are impressed and pleased with the readability of my VBA code, since it is structured, modular, well named, and if the code is long and multi-segmented, commented.