Special Date Functions in SQL, Part 1

For our internal dashboard work, we're looking at metrics for the previous month. While I could create a SQL Server stored procedure to pass these dates from a user form, I really want to create a SQL view that will show last month's information. That way, whenever the view is opened, it always shows data for last month.

I know in VB that there are FirstDayOfMonth and LastDayOfMonth functions, but I've been unable to find similar functions in SQL. In my research, I've found "Date and Time Manipulation in SQL Server 2000" helpful. I came up with this SQL:

SELECT
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AS smalldatetime) AS StartDate,
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101) AS smalldatetime) AS EndDate,
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(DATEADD(mm,-1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),101) AS smalldatetime) AS PrevMonthStartDate,
CAST(CONVERT(CHAR(10), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),GETDATE()),101) AS smalldatetime) AS PrevMonthEndDate

It returns these results:

StartDate	EndDate	PrevMonthStartDate	PrevMonthEndDate
2008-08-01 00:00:00	2008-08-31 00:00:00	2008-07-01 00:00:00	2008-07-31 00:00:00

The SQL seems a bit convoluted, especially since I'm going to be using the start and end dates as criteria in a WHERE clause. So, I'm going to create a User-Defined Function (UDF) for each. I'll share scripts for those in a follow-up post.

-----
Check out my other blogs:
Daniel Johnson, Jr.
Get That Job!
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
QuotesBlog
Twitter.com/danieljohnsonjr
Connect on LinkedIn
Interesting Things I've Read

Related tags: daniel+johnson+jr sql+server

Troubleshooting with SQL Profiler

One of the Payroll Specialists called us today, wondering if her session in Darwin was hung up and needed to be killed. Darwin, as I may have mentioned before, is a Microsoft Great Plains Dynamics product customized for the Professional Employer Organization (PEO) industry. We have SQL Server as the back-end.

The IT Manager asked me to put a trace on her session in SQL Profiler so that we could ascertain whether her session was indeed hung or not. As I saw the actions scrolling on the screen, I called the Payroll Specialist and told her whatever she was doing was still processing.

I began examining the items in the trace to see what was going on. For those that are unfamiliar, GP Dynamics hasn't named their tables, views, and stored procedures in a very intuitive way. Nevertheless, I have been able to deduce from their stored procedure naming convention the base tables involved.


In addition, this exercise has helped me learn and understand SQL programming better. I usually open the procedure as a script in a Create New SQL window. Inside these procedures that were being executed in the trace, I saw nothing more than a serious of SELECT statements. It looks as though Darwin (or really the code behind it) is getting information from the table in groups of 25 at a time.

This particular recordset has close to 5000 rows. Getting 25 rows at a time means that it will execute this stored procedure 200 times. If the program takes 5 seconds to execute each batch, then it will take about 17 minutes to go through the entire recordset. I guess it's no wonder that the process seems hung to the user.

I'm guessing that something is going on behind the scenes as Darwin is preparing and and posting a payroll. Unfortunately, I don't have access to the code behind it. This may be something we'll want to bring up with Thinkware, the vendor who provides this software to us.



-----
Check out my other blogs:
Daniel Johnson, Jr.
Get That Job!
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
QuotesBlog
Twitter.com/danieljohnsonjr
Connect on LinkedIn
Interesting Things I've Read

Related tags: daniel+johnson+jr microsoft+great+plains sql+server sql+profiler peo

Finding Tables for Employee Census Report

This is one of the posts where I describe the more technical aspects of a project I've been working on. The contents here are meant mainly to trigger my own memory when needed in the future.

The HR department has requested a census report so that the company can shop around for benefits with other vendors. In typical fashion, the information needs to be laid out in such a way that none of the "canned" reports from the business system will suffice.

I know where to get the demographic information (employee ID, client ID, city, state, zip, salary, etc.), but this is the first time I've had to track down information such as premium rates, employer contribution, employee contribution, and coverage tier.

I sat with the HR director to find out where she's able to see this information. Back at my desk, I launched a SQL Server Profiler Trace, filtering to my login ID and session ID. Then I navigated to those same screens to help identify which objects in SQL Server I need to look at.

From there I've been able to target the specific tables I need for my SQL scripts.

-----
Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!
QuotesBlog
Twitter.com/danieljohnsonjr

Related tags: daniel+johnson+jr

Reorganizing clients in Darwin provides opportunity for SQL Server clean-up

This is a post where I share more of the technical aspects of my job as a 'conscientious programmer/analyst'.

The company I work for is reorganizing clients into new databases in the Darwin business system (a customized version of Microsft Great Plains Dynamics), and the business sees this as a great opportunity to clean up a lot of things. This view is especially shared by us who work in the IT department.

The 12 current databases are, for the most part, the same in their structure; i.e., same tables, views, stored procedures, and so forth. The company has used these multiple SQL Server databases to for specific types of clients, based on their industry classifications, etc.

Street Sweeper,
originally uploaded by itsray.

One bit similarity is in the paycodes that are used. Paycodes, for the purposes of what I refer to in this and other posts on this blog, refer to specific codes that are used to signify specific payroll transactions. The company has paycodes set up for regular and overtime hours, commissions, bonuses, mileage reimbursements, and other types of income. Additionally, there are codes for deductions, such as cash advances, uniforms, payments made by the employee for benefits and 401(k). Moreover , there are codes set up for benefits, which include an employer's contributions to health care and 401(k), and the like. Finally, there are a separate set of codes for both state taxes and local taxes.

It may be easy to imagine, then, over time, and as clients come and go, that the databases would have lots of various codes. Mirror that across 12 databases, and it becomes more complicated. Furthermore, I've discovered that the code descriptions are not consistent from database to database. That the business has a need to reorganize clients into new databases presents a great opportunity to clean things up, as a result.

Yesterday, the Director of IT and the Director of Special Projects asked for a list of active codes for active employees, across all 12 databases. I am the guy they turn to in order to get this done quickly. Because of my experience with how the databases have been set up, I usually know pretty quickly which tables to use in my SQL scripts.

In this particular case, I was interested in the Transaction History table, since it contains the three most important elements my internal customers needed: check date, transaction type, and paycode.

I initially set up the script to pull all paycodes, but I found close to 10,000 codes in use since the business started using Darwin in 2005. I checked with the Director of Special Projects, and she asked me to limit to just those codes in use since October 2007. Thankfully, that narrowed the list to just under 2000. I also included, at her request, the name of the database in which the codes were used. This proved especially helpful, since not all codes are in use in all the databases.

On my way home last night, I called into Jott to remind myself to set this up as a stored procedure.

Just another way I'm able to help keep the business engine going.

-----
Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!
QuotesBlog
Twitter.com/danieljohnsonjr
Utterz by danieljohnsonjr

Related tags: daniel+johnson+jr, sql+server, programming, business+intelligence, microsoft+great+plains, payroll, peo, darwin

Keeping upper management knowledgeable and salespeople paid

This is another post where I share technical details about a project I have been working on.

SITUATION

Sales executives within the company receive monthly commission checks based on active client employee counts and gross payroll, for clients that they have brought on. In addition, upper management needs to see high-level numbers such as active clients, active employee counts, and gross payrolls - dashboard-type information.

A easy-to-use tool to generate this information did not exist at a user level. Previously, upper management relied on IT or the Controller to generate this information and send it to them.

Through some personnel reorganization, the process for generating this report fell through the cracks. Salespeople were waiting for their commission checks for the previous month, so the project was both urgent and important.

As usual, this information needs to come from the multiple SQL Server databases the company uses to manage client information through the Darwin PEO System, a customized, version of Microsoft Great Plains for the Professional Employer Organization (PEO) industry.

TASK

I was asked to develop a tool that upper management can use to generate information themselves. Some of the application requirements and thoughts that guided the development:

• Let users pick the date range, click a button, and have the system produce a report.
• Develop the application quickly to meet the immediate needs of the organization, yet with the ability to be reused whenever upper management so desires.
• Since upper management is most comfortable with Microsoft Excel and will want the data in a workbook anyway, use Excel Visual Basic for Applications (VBA)) and ActiveX Data Objects (ADO) within a single Excel workbook to produce the results.
• Choose Excel over Access because the application overhead is low (i.e., no need for tables, forms, reports, etc.).
• Since the company doesn't mark employees and clients as inactive in the system immediately when they are terminated, define an active employee during a date range as a paid employee.
• In addition to a paid employee count, obtain a total check count and gross payroll amount for each client during the date range.
• If an employee received a check and it wasn't voided, it counts.
• Take advantage of server-side processing to achieve the best performance.

ACTIONS TAKEN

I first developed the SQL statement to unite data across the twelve SQL databases, based on prior knowledge of where to find information. Then I wrapped the SQL statement up in a stored procedure, with start and end dates as parameters.

After testing the procedure with different date ranges to make sure the information was accurate and made sense, I moved on to the Excel piece. I wrote code in Excel VBA and ADO to execute the stored procedure and output the results to a worksheet in the workbook.

Once I had tweaked the completed application to make sure everything ran smoothly, I e-mailed it to the director who requested it.

RESULTS

Within a few minutes I received a phone call from her, telling me how awesome I am. She also sent the application to the owner of the company so that he can run the report as often as he wants.

Now they are able to generate the information in a matter of seconds themselves, versus waiting for the Controller or someone else in IT to generate it for them; or, even worse, spend hours compiling the information themselves.

-----
Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!
QuotesBlog
Twitter.com/danieljohnsonjr

Related tags: daniel+johnson+jr business+intelligence sql+programming microsoft+great+plains data+mining

Server-side processing rocks the house!

I've spent the last couple of weeks learning how to optimize the search tool we built. The short story is that we're now using a pass-through query in Access to pass parameters to stored procedures on SQL Server. Processing time has gone from about 2 minutes to less than 2 seconds.

Now that I know how to work with stored procedures, I'm thinking of how else to implement them with other types of applications.

BACKGROUND

The company has clients spread across multiple databases for various reasons; namely specific business rules (e.g., worker's comp rate, state tax rate, unemployment insurance, etc.) apply across an entire database.

Quite often someone in the company receives paperwork from an agency or client with nothing but the client employee's name or their SSN. Searching for this employee has been trial and error, going through each database at a time. Also, in many cases the person in our company just needs to see some basic information about the employee. For example, during tax season we just needed to verify a client employee's address and phone number. Hence the reason for such a tool.

We had initially designed the tool to have individual pass-through SELECT queries, uniting and applying criteria to them in Access. It was the uniting and applying criteria that has been slowing things down.

OPTIMIZING

So, I created two stored procedures with parameters: one that searches for an SSN one one that searches on a name. Each stored procedure unites the individual SELECT queries on a database separate from the other 12 that were being searched.

In VBA code, based on which criteria the user enters, we determine which stored procedure to run. We create a pass-through query to EXECUTE the stored procedure. This pass-through query then contains the search results, which is what we had with the earlier version of the tool.

PREVENTING SQL INJECTION

While reading the literature, I came across the concept of SQL injection and the need to prevent it. Since users are passing text to a SQL statment, we need to prevent the intentional or unintentional insertion of code that would prematurely terminate a text string and append a new command.

I wrote one procedure that scans what users enter in the first and last name fields for "--", "CREATE", and other such items. My coworker gave me some code to prevent users from entering other illegal characters. This double-barreled approach will prevent any SQL injection from occurring.

COMMUNICATING WITH THE MASSES

I enjoyed sharing the news of the updated tool with the rest of the company. I knew many had not even heard of it. I constructed the email the way I would a friendly blog post, including pictures.

One user immediately replied: "Awesome! Works very fast. Good job." I was contacted by two people in Accounting and Payroll who asked that I put the tool on their machines as well.

Much joy all around, I tell you. And IT looks good.

----------------
Now playing: Tim Hawkins - KidsRock
via FoxyTunes

Helping company take advantage of a tax credit

I found out last year from the Accounting department at my current company that employers with tipped employees can get a tax credit based on how employees have reported their tips. I guess it's the IRS's way to encourage employers to get their employees to report tips so that they can get the tax from it.

The PEO model is one of co-employment: the client employs someone to do the work while the PEO employs the worker from an HR perspective. If the client doesn't take the tipped employee tax credit (or whatever it's actually called), then the PEO can take it.

For me it has meant developing yet another report from the SQL Server database. The business, of course, wants the report as soon as they can get it, and I and my boss help them understand that it cannot always happen overnight or even in a week.

The report, as many of these I develop lately, involves looking at the transaction history on employees' paychecks to identify reported tips. It sounds simple, and it really is, as long as you know where to look.

Over the past year or so, I've gotten better as I've come to understand where the types of information are stored - the database and the front-end are a modified version of Microsoft Great Plains, and the tables are not always intuitively-named.

I probably should push this application out to the Accounting department, since all I did was run it for last year and the current year. The upper management was certainly surprised at fast I'd turned their request around.

Expanding the 401(k) reporting tool

As the company grows, the need to be able to report on client's using the 401(k) plan does as well. We are adding clients to additional databases, and that has meant that the 401(k) reporting tool needed to be updated so that the user can identify which clients are participating in the company plan.

Using Microsoft SQL Server Management Studio, I created the additional tables: one for each additional database. Then I added ODBC links to these tables in the 401(k) reporting tool, the desktop application created using Microsoft Access 2003. I have to remind myself that, for each computer using this tool, I have to create the ODBC connection to each database and table. Then I updated the VBA code so that it can recognize and use the new tables as it compiles the information for the reports.

After testing out the changes, everything appeared to be working fine. I contacted the user and updated her version of the tool, refreshing the ODBC links where needed.

The company is now more ready to begin managing the 401(k) program for new clients.

Learning .NET

I'm a little jazzed to say that I'm finally starting to learn .NET, since that's what recruiters tell me all the cool kids know. I'm going to focus on VB.NET, since that's the most easily transferable skill from what I already know.

It looks like I'm not alone: LinkedIn: Answers: What Microsoft Visual Basic and SQL Query Language Resources would you recommend?