I’ll get into this eventually…

It’s been over a month, for which I can only apologise…

In that time I finally got the cold – boy, that was a nasty one – and had laser eye surgery. So far life without specs or contact lenses is good. Certainly worth an afternoon of exquisite agony and a sizable chunk of the bank account. That sounds wrong in a few ways.

Anyway, I’ve been trying desperately to come up with a good scenario for a discussion of recursive queries – it will happen, just not tonight.

I thought I’d share a team leading tip that I picked up 18 months or so ago from a colleague known locally as ‘The Mouth’. He may yak but there’s some gems in there.

Now, in the organisation I worked in there are Quarterly ratings and Half and End-Year assessments (A-D). These are ‘levelled’ by all the team leaders getting together for an hour or so and discussing the relative merits or otherwise of their team members. It can be very painful. Consciously or not, the format always ends up with one guy slagging off an individual and that person’s line manager defending them. Here’s a little political trick that works… pretty much always. Particularly in larger organisations.

Tell your team members to ask for feedback every time they interact with any other team leader. What was good, what wasn’t, how that person’s work could improve etc.. Do it by email so there’s a trail.

Now, team leaders are either a) insanely busy or b) maintain an ‘insanely busy’ aura to avoid doing any work. They will not respond to these feedback requests. I confess I doubted this bit, but it’s true, trust me. The upshot of this is they will not respond to these emails

So now, in the ‘levelling’ meetings when Team Leader A starts laying into one of your people, simply produce the email (printed) and use the following line: “When asked for your opinion of their performance, you had no comment. Surely you cannot be dissatisfied with them if you haven’t responded.”. There is no answer. All your peopple will get the grades you want them to (regardless of whether they deserve it). The world of pain that is annual reviews becomes orders of magnitude easier.

I promise I will visit recursion soon.

Rant Zero – Playing with Teradata Dates

Not many things start at 1. You’re not 1 until you’ve passed the anniversary of your birth, you never START with 1 apple, you’re always given one or buy one. There’s never one passenger on the bus before the first stop. Luton and Mansfield on Wednesday night even went so far as to END with 0. That’s commitment. The Mansfield keeper must have felt justified in his choice of kit; he looked like he was wearing a full-body condom. Safe as houses. Luton’s very own Pilkington made two fabulous goal line clearances as well. That’s George ‘Crystal’ Pilkington, the centre half – not Kevin ‘determined to be dropped’ Pilkington the goalkeeper. Still – Mike joined me and he seemed to enjoy himself.

Anyway, why do dates start at 1? The First of January seems a reasonable description, but January 1 sounds like it’s done and dusted, so surely it should be January 0 until midnight? Did those Arabs come up with the number meaning “nothing” for, er, no reason? Hmmm… counter argument.

I actually enjoy playing with dates in Teradata. Slightly odd, I’m aware, but that’s just me. I just wish dates started at zero – you’ll see what I mean, I hope.

First – a quick precis of the DATE data type as employed by Teradata.

Teradata dates are stored as an INTEGER with the following layout: CYYMMDD.
C is the ‘century indicator’ – 0 for 1900s, 1 for 2000, 2 for 2100, etc.
YYMMDD are your standard pre-Y2K year, month and day values.

You can perform maths against a DATE value just as if it were an INTEGER but DATE conversions are applied which gets, er, interesting. By this I mean:

DateVal = 1120131 /* Set up DateVal with 31st January 2012 */

If we now add 1 to DateVal, it has a value of 1120201 (1st February 2012), not 1120132 (32nd January 2012? – obviously an invalid date).
You see what I mean, I’m sure…

The bit I really like is writing derivations from the date that give us what we want. There are functions such as EXTRACT (YEAR / MONTH / DAY FROM DATE) etc.. These are all described in detail in the Teradata Manuals, but they’re no fun and no challenge.

To get the year, I use the following:

1900 + (DATE / 10000)

DATE is an INTEGER, 10000 is an INTEGER, so you get an INTEGER back.

For Day, it’s simpler:

DATE MOD 100

(NB: MOD gives you the remainder after dividing by the value supplied).

Month is a little trickier:

(DATE / 100) MOD 100

So far so straightforward. Now let’s say you want the last day of last month (a frequent requirement in Financial institutions):
We need to take off the number of days we are into the existing month:

DATE - (DATE MOD 100)

This will always give you the last day of last month.

So to a problem I’ve had to solve recently. I confess to enlisting Jon over a coffee to discuss it as we tend to clear each other’s minds when ‘there must be an easier way’ keeps rattling through my head.

The Problem

For any given Maturity Date (in a DATE column named Maturity), we want to derive the first day of the Quarter in which that date falls.
Quarters are simply defined as Jan to Mar, Apr to Jun, Jul to Sep, Oct to Dec.
Getting the Year is easy – as shown above.
The Day will always be 1 as we want the first Day of the Quarter.
The tricky bit is getting the first month of the quarter we’re in. We need 1 for 1 ,2 ,3; 4 for 4, 5, 6; 7 for 7, 8, 9; 10 for 10, 11, 12.

We’ll assume that MatMonth is derived, as above, by:

((Maturity / 100) MOD 100)

The Solution(s)

Jon’s:

(((INT((MatMonth - 1) / 3) + 1) * 3) - 2)

Mine:

MatMonth - ( ( MatMonth - 1) MOD 3)

They both work and will be similarly efficient. I tend to prefer Jon’s method as we’re only deriving the month once. Jon apparently prefers mine. It’s just mutual appreciation, I think…

But in both of them you have to juggle the month up and/or down by 1. Grrr. If dates started at 0, this wouldn’t be necessary. It’s just messy.

Finally, here’s the full script, with the items broken down:

SELECT
 (Maturity / 10000)         AS MatYear          /* Maturity Year                   */
,((Maturity / 100) MOD 100) AS MatMonth         /* Maturity Month                  */
,(Maturity MOD 100)         AS MatDay           /* Maturity Day                    */
,MatMonth - ((MatMonth - 1) MOD 3) AS MatQStart /* Start Month of Maturity Quarter */
,  (MatYear * 10000) /* Shuffle year left 4 places                  */
 + (MatQStart * 100) /* Shuffle our Quarter Start Month up 2 places */
 + 1                 /* Just Day 0 - oops - I mean 1, of course     */
 AS Calc_QStart                                /* Our Calculated Quarter Start as an INTEGER */
,CAST (Calc_QStart AS DATE)                    /* Belt and braces to ensure we have a DATE   */
FROM TableA
;

Obviously you could put this all in one output column by combining the factors in one statement.

I’m going to leave it there for now. If you ‘d like an explanation of how these work, I’ll post as a comment. For info, both Jon and I resort to the demon e(X)cel* to build these things step-by-step.

Si M…

* Yes, that is a Xanth reference. More on that another time.

2012 #sniff#

I am trying desperately to catch the cold my wife & mother-in-law have had so I have a good excuse to work from home and not commute to London. It just doesn’t seem to be taking hold. A theory proposed by a PM on site is that the constant low-level malaise of the Underground immunises people against poor health. Good grief – that’s what I’m trying to avoid. Ah well… #sniff#

Throughout my career as a contractor and (for a couple of years) as a permie, I’ve kept a folder simply called ‘Useful Stuff’. It’s usually blocks of SQL / Unix scripts / Excel workbooks and the like that I’ve thought could be used elsewhere.

This blog is intended to document some of these items and make them available to anyone who could find them useful.

My ‘specialism’ is Teradata SQL – I’ve primarily used this from a business perspective in Retail and Finance environments. I’ve dabbled in MySQL, Oracle etc. and extensively used VBA to interact with the database machines as well as a certain amount of home-brew PHP and Javascript. I do not proffer any expertise in Unix – I pretty much just build run scripts, but I’ve picked up a few useful techniques that might be informative for a beginner.

As well as this, I led a small team at the technical end of a marketing department for a year and a half. A combination of training, experience and Jayne’s seemingly unrivalled man management ability (you should hear her reviews) means I’ve picked up some handy team leadership / office politics tips that will make, I hope, an interesting occasional distraction.

I’ll compose and post my first ‘real’ blog in the next few days and see what (if any) reaction it produces.

I will make frequent references to my good friends Jon, Mike & Simon – they’re complete whizzbangs in their chosen fields. The wife Jayne will appear occasionally as well. There are various other colleagues I’ll mention no doubt as time progresses.

I also make no apology for the fact I am a Luton Town season ticket holder of 10 years, so please expect my mood (and style) to reflect the fluctuating fortunes of my home town club.

Er… “Hello World”… always a great cliche to start with…