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

Business Intelligence and Data Warehousing TeleForum Notes

Today I participated in a NOREX member TeleForum on Business Intelligence (BI) and Data Warehousing (DW). I'm including my notes taken here:

• Started off by defining BI and DW. I like the Wikipedia definitions myself. Also discussed what Master Data Management is; i.e., managing data that has become aged and may or may not be needed as much as current data. (The company I currently work for doesn't really think of what I do as BI and DW, but developing Decision Support Systems (DSS) is really at the crux of what I do here.)
• Two polls on where participants are in implementing BI and DW in their organizations. Most are either currently using or evaluating, versus implementing.
  
• Discussed scope of data definition and data modeling that should be done before getting into BI/DW. One caller will be building theirs from scratch versus buying something that was developed for someone else in their industry. (I wonder if different BI solutions have been developed for different industries.) Data cleansing is so vital when doing modeling. (Amen to that!)
• Discussed risks in software selection and implementation. Licensing brought up as part of risk. Making sure that appropriate number of licenses (user vs site) important. How solution builds report cubes (pre-calculated or built at run-time) can affect performance and user experience. sBase(?) mentioned.
• Practical support from vendors also brought up as consideration. Everything works great at the demo, but on the implementation there could be issues. Ask for references. NOREX mentioned that's part of the service they provide for members.
• Discussed tools that are used, and pros and cons of each. One member mentioned Sybase IQ. Others are using Business Objects, SQL Server Reporting Services, and Oracle tools. Crystal Reports also mentioned.
  
• Someone asked for experience with the Obvient strategies tool. No one on the call mentioned having any experience.
• Any local government entities using data mining tools? No one on the call had any comments. Topic to be passed on to the government members outside the call.
  
• SQL Server 2005 Analysis Services - how is it being used, other considerations. Has a lot of capability and interacts well with Visual Studio. Another member, however, saw a demo at Microsoft- some problems with creating a cube and error messages hard to decipher.
• Discussed how to implement SQL Server Reporting Services through Sharepoint. (We use Windows Sharepoint Services as our company's intranet and are looking at how to do this.) No one on the call had any expertise to offer.
  
• Discussed whether anyone uses Excel 2007 as a BI client. Some of the new functionality of Excel lends itself to an entry-level BI implementation. Some accountants in one organization using Excel to manipulate data from their other BI system. No one taking advantage of the BI capability yet. Concerns raised about quality of data, security, and versions.
  
• Poll on whether anyone using SAS to access Star Schema data: dimensional modeling vs entity-relationship modeling. (Not really applicable to us in our organization.)
• Release management best practices from Dev and Test to Production environments sought. How to manage things that are not necessarily files that can be version-controlled? Really speaking about reports and dashboard components.
  
• One member mentioned his process is such that a developer will create a report and work from it at his desktop. Then when it's ready for deployment, he will publish it.
• Other members mentioned a more rigid change management structure with development, QA, and production environments. (I guess it depends on the specific tools and systems being used.)
  

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

Successful online monitoring with Google Alerts and RSS

Are you using Google Alerts and RSS feeds to track your company's presence on the internet? How about competitors?

Since this blog reflects me personally, I choose to omit specific company names. As the disclaimer says, opinions and thoughts expressed here are my own and not of any employer, past or present.

A few months ago I set up some Google Alerts and RSS feeds to track mentions of the company as well as its other web properties, and the names of key management personnel. Also, at the management's request, I set up some additional alerts for another company against whom the company has taken legal action.

Most of the time I get false positives or results I'm already aware of. Yesterday, however, I saw an article (and several subsequent links to it via Google Alerts/RSS feeds) about that other company and forwarded it to the COO.

I received this email a few minutes ago (emphasis added by me):

Dan,

Your article yesterday on [THE OTHER COMPANY] was great. I shared it with [THE OWNER OF OUR COMPANY] and there was information in that article that our attorney did not know about.

Thank you!

[NAME]
Chief Operating Officer

Rich Palmer recently shared some good thoughts on Google Alerts, too:


For more information, check out the NewPR Wiki: What Should Every Company Be Monitoring? page.

-----
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 google+alerts rich+palmer pr public+relations

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

05 Nov 2007 - Utterz from the road

Three topics discussed:

1. Effects of Daylight Savings Time ending one week later this year, especially on the commute home.
2. New Media Cincinnati: http://www.newmediacincinnati.com or http://danieljohnsonjr.com/2007/11/new-media-cincinnati-meetup-november.html
3. Dashboard-types of reporting I've been doing to provide a good pulse on the company. I love doing this kind of marketing-type of analysis.

Hear all my Utterz: http://www.utterz.com/~h-danieljohnsonjr/list.php

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

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