Money Laundering and Other Use Cases.

By balebond | Published: May 18, 2009
money laundering

My idea of money laundering

I helped develop an app to screen annuity buyers as potential money launderers back in the early days of the US Patriot Act enactment. The main web app was a sales tool and had been in production for some time but we needed a quick way to comply and so we put something together.

What we neglected to adequately consider was the primary use case. I wasn’t responsible for the front-end development but I also didn’t put two and two together on a flaw in our thinking. Basically, sales folks would visit would be securities buyers at their residence or place of business and would sell them while seated on a comfy couch over some tea/coffee. Then the salesperson would attempt to close by qualifying them on the spot, trusty laptop at their side. But imagine the uneasy feeling, nay, the “I almost crapped my pants” fear you’d feel, if after getting all their personal info input a big red alert pops up decrying that you’re sitting next to a pernicious villain who’s now just been outed? How would you smooth talk your way out of that one with a cool head… would you be keeping your head at all? Good thing there were a lot of misidentifications back then.* After a few average joes were offended that the US considered them less than model citizens, we immediately saw the flaw and corrected it. A nice note that underwriting would be completed shortly totally sufficed. No harm no foul. Now, no matter what my part is in the development cycle… I always want to know what the use cases are.

What was one of the poorest design choices that you’ve been a part of?

Happy SQL-ing.

*At least back then if you shared an infamous name (i.e. if your name included “bin Laden” or “Hussein”) it was going to be a hit. I wonder if Obama has fixed that issue? :)

Posted in Uncategorized | Tagged , , | Leave a comment

Find your stuff with this stored procedure called.. um.. find.

By balebond | Published: April 28, 2009

OK… this is not going to find your car keys… or the third cell phone you’ve lost in as many years… but it will find all the SQL code with the keywords you offer it. This is part three of the series on procs you should add to your shortcut menu. Find is Ctrl-4 in my hot key arsenal. View this post to learn how to add custom stored procedures to your shortcut options.

SAMPLE CALLS

  • Returns any code with the text ‘asset_id’ and any tables with exact column name matches:
?View Code TSQL
1

find 'asset_id'
  • Locate any code with the text ‘asset_id’ and any tables with exact column name matches but code with ‘update’ will be eliminated:
?View Code TSQL
1

find 'asset_id', @does_not_contain = 'update'
  • Special case. Only returns user tables with an exact column name match on ‘term_id’ and no code search. Fastest response time:
?View Code TSQL
1

find 'asset_id',@obj_type = 'Table'

Code after the jump…

Read More »

Posted in SQL Server, SQL Snippets, SQL Tips | Tagged , , , , | Leave a comment

Tips for Using SELECT’s GROUP BY Clause

By balebond | Published: April 14, 2009

Just a quick entry on a SQL strategy useful for beginners. Today we had a pretty serious production issue and we had to make some quick analysis to determine what the extent of the issue was. One of the goals of the detective work was to determine the spread of affected items over a range of days. In other words, how many records are there per day. The table however doesn’t have a “DAY” column, but rather a DATETIME column which is typical. So how do you get that ad hoc report? The concept is actually pretty simple. You may be familiar with the standard “GROUP BY” clause and have a sense that you could use this. But how do you utilize it to group by day… a column we don’t have? Well, by casting your applicable DATETIME column in such a way as to keep the calendar day, but drop the time.

Here’s an example from AdventureWorks:

?View Code TSQL
1
2
3
4
5
6

SELECT	CONVERT(char,StartDate,101) AS SDate,
		COUNT(BillOfMaterialsID) AS BOMCount
FROM Production.BillOfMaterials
WHERE BOMLevel = 1
GROUP BY CONVERT(char,StartDate,101)  WITH ROLLUP
ORDER BY CONVERT(char,StartDate,101)  DESC

ROLLUP adds a summary column at the end. The subtlety of this tip is the fact that you have to GROUP BY the actual result set data column which is CONVERT(char,StartDate,101). SQL Server won’t allow aliases in this context.

Expected Results:

4-14-group-by-resultset-1

Of course this concept can be applied in such a way to slice data in lots of interesting ways… averages, absolute values, SUBSTRING for say… I don’t know… sorting results by first letter of customers last name. It’s worth some experimentation. I may just add additional examples in the near future.

Happy SQLing

Posted in Beginner, SQL, SQL Tips | Tagged , , , , | Leave a comment

Spying on Your Spids: It’s sp_who But Better

By balebond | Published: April 13, 2009

This is another productivity tidbit that saves some time when time is likely not an asset at your disposal. I’ve decided to turn the post about sp_helptext as a hot key into a three parter. So this is Part Deux. Just like my namesake has proven… folks dig sequels. Hmmm… that’s almost a pun.. SQL sequels. Anyway, when the crap hits the fan in SQL Server world, it’s typically up to the DBA/Developer to quickly ascertain what is going on in the system that’s causing your testers.. or even more sweat-inducing, your users to cry foul. When that happens we often will turn to our pals sp_Who and sp_Who2 which gives us a window into what SQL Server is doing at the moment. With other tools like Data Management Views and Functions (DMV,DMF), DBCC commands like INPUTBUFFER, third party tools, and even possible the ugly step child of tuning, SQL Profiler, DBA’s are expected to thwart the sinking ships. It appeared to me one fine day that with the data that was available to us in SQL Server 2005 via DMVs there had to be a more comprehensive way to view system processes. spWhoBetter is that better version of sp_Who and sp_Who2.
Read More »

Posted in Uncategorized | Tagged , , , , , , | Leave a comment

sp_helptext as a hot key (shortcut) sql trick

By balebond | Published: April 8, 2009

Ok, this is a quick SQL Server serendipity that has saved me lots of little increments of time that add up over the long haul… call it productivity if you will. I stumbled on this little ditty a few years ago. FYI, if you don’t already know, sp_helptext is a standard system stored procedure that returns the text of functions, views, and procs*. Typically you call it from a query window like so:

?View Code SQL
1

sp_helptext 'vwSomeRandomView';

It’s a very helpful tool. How often are you pouring over code and have to dig into some function or proc to see what it’s doing? Sure, you could go to the SSMS object explorer… expand your database… then Programmabilty… (is that a word?), then Stored Procedures (yawn), then right click on it after you’ve found it in a list of likely hundreds, then finally navigate a popup menu of multiple options and have it written out to a new window. What is that? Like 5 minutes of work? Well, seriously… it’s a lot of clicking for such a common task for any DBA or developer. So if you’re a little more senior you’ve already taken on the time to learn what tools are available to you in T-SQL land. sp_helptext is a fundamental part of that Batman-esque utility belt.

Although it’s great to know it and use it, I still have to open a window and type all that out. It’s not as bad as the previously mentioned work flow but wouldn’t it be better to have a hot key where I can just double click a proc name or function or view once found in my trusty query window and just have that text come to papa? That’s how I thought it should work if I could have it Burger King style. Well, it’s your luck day. This my friend is a reality.

Read More »

Posted in SQL Server, SQL Tricks, SSMS | Tagged , , , , | Leave a comment

Hello world- of SQL Programmers!

By balebond | Published: April 5, 2009

After more than 10 years writing SQL code, primarily of the MSSQL T-SQL variety, you pick up a few tricks and build up a decent tool chest. I’ve decided to stop being greedy with those hard won nuggets of inspiration and decided to start sharing it with my fellow programmers. I hope to be considered a “SQL Smarty” but I’m by no means a SQL “Know-it-all” . A former boss of mine would always ask interviewees to rate themselves 1 – 10 on their mastery of SQL Server in general… if you said anything over 8 you would never get hired because you were obviously either too full of yourself to take direction or too foolish to realize the scope of the creature that is MSSQL. So, I hope to learn from you and hope to inspire others. Nothing would make me happier than to help the occasional desperate developer googling frantically in the 11th hour to solve his persnickety issue. Hopefully with an elegant set-based solution. :) I know I’ve been there and was glad to know someone took the time to share.

Oh, the blog name… my real name is James Bond so that should make it self-explanatory.

Posted in Uncategorized | Tagged , , | Leave a comment

  • Meta