Hey, those variables don't grow on trees, y'know!

This company has customers around the world, and a key IT function is collecting and processing data files from them, reports a pilot fish on the inside.

"The files have a common naming structure that includes the file-creation date," fish says. "The filenames are stored in a SQL Server table, along with information such as how many rows of data the file has and when it was processed.

"Because of our workload, our manager hired a SQL consultant to 'streamline' some jobs. I'm not going to knock the guy -- he did have some talent and good ideas -- but if he was half as smart as he made himself out to be, he would be twice as smart as he really was."

One day in a group meeting, the boss has a suggestion: Add a column to the files table that will store the file-creation date, after it's been extracted from the file's name.

The consultant immediately jumps in, arguing that it won't be as simple as it appears. He launches into a long-winded explanation of all the work he'll have to do to modify the SQL Server Integration Services job in order to make it work.

Then fish speaks up: Why not just make a function to pass over the filename that's already in the table, gather the date and time using substring commands and pass the date back with a CONVERT function? Then the new column will just hold a computed value, and no change to the SSIS job will be needed.

"The consultant immediately protested that there was no way it could be done," says fish. "And at that point we broke for lunch.

"So during lunch, I made the function."

And it's almost exactly what fish described in the meeting: He uses a substring function to extract the date and time from the filename and store them into two variables, then concatenates the two variables and uses a SQL CAST function to send back a date-time stamp.

When the meeting reconvenes after lunch, fish demonstrates his change. Consultant immediately fires back that he sees some logic problems with the function, and he'll change it so that it won't eat up so much valuable processing time.

Sure enough, the consultant later sends out an email with his improved version.

"His big change? Instead of breaking date and time into two variables, he set up a single variable called @DATETIME," fish says. "Then he copied my substrings to send date-plus-time to this variable, and used a CAST function to send it back.

"He saved one variable. Brilliant."

You don't have to be brilliant to send Sharky your story. Just email your true tale of IT life to me at sharky@computerworld.com. You'll get a smart Shark shirt if I use it. Comment on today's tale at Sharky's Google+ community, and read thousands of great old tales in the Sharkives.

Get your daily dose of out-takes from the IT Theater of the Absurd delivered directly to your Inbox. Subscribe now to the Daily Shark Newsletter.

Copyright © 2017 IDG Communications, Inc.

It’s time to break the ChatGPT habit
Shop Tech Products at Amazon