Daniel Johnson, Jr.

I initially started this blog as more of a portfolio of my work. I’ve described specific projects in narrative form, but I see an opportunity to share more code.

Sanity Check Fail – You’re Doing IT Wrong: Coding Tests.

When I saw this link – on Twitter, no less – I knew I had to check it out. I don’t know if I understand it enough yet to try it out, but it’s definitely something worth looking into.

Access twitter from SQL Server 2005 and above using the Tweet-SQL CLR Stored Procedures

I’m going to have to look further at the Twitter API. I bet there are some great opportunities for putting together something really interesting.  What other interesting tools for using Twitter have you found?

This post originates from http://danieljohnsonjr.com

Related articles by Zemanta

• Mr. Tweet Recommends Friends to Follow

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:

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

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

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