Thursday 29 December 2011

Fun with data types

Are protected underwriting terms applicable?  (Never mind what it means).  The answer is either Yes or No – they either are or they aren't.  No maybes possible, no special situations - so the perfect data type would be Boolean - a value of 1 means True, and 0 means False.  Named after 19th century mathematician George Boole, who presumably worked out that the opposite of True was in fact False.  Isn't maths wonderful?



Unfortunately, in SQL Server, there is no Boolean data type.  This might be something of a setback.  Still, the nearest thing that can be used in place of Boolean data is the BIT data type, which is an integer data type that can accept a value of 1, 0 or NULL value only.  And it only uses up one bit so it is very efficient.  

So, problem solved, I hear you say?  Ho, ho, ho; that is hollow laughter echoing round the IT crowd’s squalid basement.   Because 1 is fine for Yes, but 0 won’t do for No.  Oh dear me no - the bulk upload software to which I want to transfer data expects either 1 or blank.  It could be changed of course, but that would mean months of development and testing. 

Hokay, how else can we do this?  Tinyint, Smallint and no doubt Int and Bigint all implicitly convert the desired blank into a 0.  So no joy in that direction. 

A fixed size character string?  So I tried it as char(1), and did a CASE statement to specify ‘1’ for Yes and “ for No.  But that changed ‘’ to ‘ ‘ in other words, a space or ASCII code, er, 32.  Not the same thing at all, as far as a computer is concerned.  I asked for a single character – I get a single character – a space is a character, it just happens to be an invisible character. 

The answer was to use a variable length character string – the varchar datatype is usually used for strings that can vary greatly in length.  Names, for example.  You don’t know who is going to order a widget next – could be Jim Lee, could be Felicity-Jayne Anstruther-Gough-Calthorpe.  Using a varchar allows the length to vary – from 0, which is what we want, to 1, so it’s now varchar(1), which looks bizarre but works!  There’s a length overhead to using a variable character field, but we can live with that. 

I still think it should be Boolean!

Tuesday 13 December 2011

How to Identify Twins

I'm trying to find a list of twins in my source database - reason being, twins share a surname, a date of birth, and a postcode.  Unfortunately, those are exactly the criteria that the destination database uses to identify and reject duplicates <sigh>.  So it's handy to have a list of twins beforehand and do some manual jiggerypokery to make things work as desired. 

(In an ideal world, perhaps our skilled team of Oracle experts would modify the destination database so that it considered the forename as well, and said to itself "Hey, twins!  Maybe I won't reject this guy after all."  But that puts us into a world of development changes and testing and pain - it's easier and quicker to leave the damn thing alone and manually wrestle with the input data.)

So the first step is to identify surnames which occur more than once in the data.  Notice that I'm grouping by Surname and Date of Birth:

      Select mb1.surname
      From   tblbods    mb1
      Group BY    mb1.surname, mb1.dob
      having count(mb1.surname) > 1

Which gives us:

Surname
SMITH
JONES
FORTESCUE-SMYTHE
MURPHY
MCDONALD
PATEL


Actually it gives us an awful lot more than this - there are 200,000 records in the table.  But I thought you would get bored if I listed more, plus I have had to change all the names and dates of birth so as to anonymise the data. 

Second step is to do the same for dates of birth which occur more than once.

      Select mb2.dob
      From   tblbods    mb2
      Group BY    mb2.surname,      mb2.dob
      having count(mb2.DOB) > 1

DoB
10/05/1993
11/06/1996
11/06/2007
25/12/2005
11/07/2010
14/02/2001


And then finally I want to select data where the surname is in the list of surnames from Step 1, and the date of birth is in the list of DoBs from Step 2.


Select
      mb.BodID, mb.Title, mb.Forename, mb.Surname,
      mb.relationship, mb.sex as Gender, mb.DoB
from
      -- Table of individual members
      tblbods     mb
where  mb.surname in
(
      -- Step 1 - surnames which occur more than once
      Select mb1.surname
      From   tblbods    mb1
      Group BY    mb1.surname ,     mb1.dob
      having count(mb1.surname) > 1
)

and mb.dob in
(
      -- Step 2 - Dates of Birth which occur more than once
      Select mb2.dob
      From   tblbods    mb2
      Group BY    mb2.surname,      mb2.dob
      having count(mb2.DOB) > 1
)

And the end result is:

BodID
Title
Forename
Surname
relationship
Gender
DoB
523189
MISS      
MARIA
SMITH
Child
F
11/07/2010
523190
MISS      
ALANA
SMITH
Child
F
11/07/2010
531637
MR        
JASPER
FORTESCUE-SMYTHE
Child
M
14/02/2001
531638
MR        
SIMON
FORTESCUE-SMYTHE
Child
M
14/02/2001
466662
MASTER    
RAHUL
PATEL
Child
M
10/05/1993
466664
MISS      
SAMIRA
PATEL
Child
F
10/05/1993
519007
MASTER    
JOSEPH
MURPHY
Child
M
11/06/2007
519008
MISS      
ANNETTE
MURPHY
Child
F
11/06/2007
339731
MISS      
PATRICIA
JONES
Child
F
11/06/1996
339736
MISS      
IMOGEN
JONES
Child
F
11/06/1996
419768
MASTER    
WINSTON
MCDONALD
Child
M
25/12/2005
419769
MASTER    
DOUGLAS
MCDONALD
Child
M
25/12/2005


Simples!  Hope you find this useful.









Saturday 10 December 2011

Job Hunting - Preparing for the Interview

So you've read my blogs on doing up your Linked In profile, and job hunting - and all this good advice has worked!  (Or at least helped a bit).  You have an interview.  Are you ready for it?

Part of it is getting organised.  If you are looking, there's a fair chance that you will get a few interviews.  If you get them mixed up, this will be a bad thing - so you need to get organised, don't play it by ear.  Start by creating a file for the Employer.  I like to use clear plastic folders, but whatever you find in the cupboard will do.

Then a cover sheet.  Put the Company name in big letters, then the job title.  You've probably come through an agency, so put down the agency name and the bloke's name.  He will want you to ring him after the interview to discuss how things went, so you might as well ring the right bloke.  Then the name of the interviewer and their job title - the agency should give you this in advance.  It's handy to have all this stuff on one sheet of paper, rather than have to search for it.  Let me know if you want a copy and I'll send a copy of mine - this site doesn't seem to allow me to add stuff for you to download.

Add your CV to the file - it got you to the interview, they will probably want to ask you questions about it - you might think you know it off by heart, but take it just in case.  Maybe the recruitment agency has sent an old version, maybe the interviewer has left yours on his desk and brought someone else's.  If you have a copy, you look efficient.  If you don't have one, you might find yourself struggling.

Make a list of any holidays you have booked over the next three months - type it up nicely, hand it to the interviewer or the HR bod if there is one - it shows them that you are efficient and thinking about their problems. I once had a holiday booked, but no-one asked me about holidays and I forgot to mention it - it caused a lot of grief when I told them about my long-planned holidays a couple of weeks after I started work.  If you give them a list up front, they can't quibble about your week on the beach when you start the job, or God forbid!  ask you to cancel.

Put together a list of good questions for you to ask when it's your turn.  They ask you questions, and during the course of the interview you'll probably ask them a few back - but it's worth having a few good questions to ask at the end when you want to leave them with a good impression of you.  Maybe something that shows you have checked out their website, or their competitor's website - "I see your competitor has just brought out X - how is this likely to affect this business?"  A good last question might be "If I was successful, when would you need me to start?"  But build a list of good questions (the recruitment agencies have samples on their websites) and keep adding to it.  Google has 14,000,000 pages of "good questions to ask at interview" - some are better than others, and some are very cheesy indeed.  Prepare a half a dozen that you are comfortable with in advance - that way your mind doesn't go blank.  It's probably OK to refer to your list - again, it proves you have prepared for the interview.  But just ask a couple of good ones - reading 20 questions off your list will not be helpful.

All that stuff above is going to be pretty much the same for every interview you go to.  Now for the stuff that applies to the specific job.  Start with the letter or email inviting you to attend - you might need it.  Then the job spec that the agency sent you.  I like to go through it, ticking the things I know on the Essential and Desirable lists.  Don't panic if they specify something as essential but you can't in honesty tick it.  Maybe it isn't that essential, but most likely the other seven things you have ticked outweigh the one you can't.  Be ready to ask them about it - get your attack in first - "I see you are looking for someone with X, whereas my experience is in Y - is this likely to be a problem?"  The answer's probably not - or they wouldn't be wasting their time talking to you.

You know the name of the interviewer - look them up on Linked In and print it out, stick it in the file.  You get a picture, some background history, current and previous jobs, groups they are members of - chances are you will find some common connection or interest you can mention.  Don't be a creepy stalker though!

Check out the company website.  Check them out on Google as well - maybe they have been in the news recently.  Print out anything that you think is interesting and add it to your file.  Maybe cut and paste stuff onto a one page summary - you are probably not going to remember tons of corporate waffle.  I'm not a fan of Twitter but it doesn't hurt to look and see what people are saying.  Good or bad, it might lead to a good question to ask.

The agency will probably give you advance warning if there is going to be a technical test.  You can study for this - Pinal Dave has a good list of SQL Server Questions, and unlike others he gives you the answers too.  But remember that the test isn't trying to catch you out.  And in the great scheme of things, it doesn't matter very much how many Pages there are in an Extent*.

You are going to have to wear a suit and tie, or the female equivalent, especially if you are female.  It's far better to be overdressed - if the interviewer is in jeans and a tee shirt, you can take off the jacket, maybe loosen the tie.  But if you are in jeans and a tee shirt, you can't suddenly get less casual if you need to.  I was nearly caught out once - I knew the firm had a casual dress culture, so nearly went along in my chinos.  Luckily I stuck to my suit and tie, because when I got there it turned out that interviews were very formal indeed.

Check out where the place is - if you can go the day before and scope out the route and the parking, do so.  In any event, get to the place early on the day and spend a few minutes skimming through your file.  Take a few deep breaths.  And go in there and do your best!


* Eight.  Now you know.