What’s your leash?

Hi all, not a technical post today but more food for thought. I’m going to relate it mainly to career progression but to be honest it applies to all aspects of life (Get me being deep! Smile)

Anyway . . .

So, the other day I was walking back from my local super market, I came to a long stretch of path, quite wide with greenery on either side. Coming up from the other direction was a guy with quite a large dog. The guy looked respectable enough and to be honest big dogs don’t really bother me that much so I thought nothing of it.

As we got a little closer to passing each other I noticed this was a Rottweiler, it was a big strong and proud looking animal and looked quite happy to be plodding along by its owners side. Again I thought nothing of it as I spent a fair few hours around rescue Rottweiler’s as a kid and know first hand that the majority of them and big soppy softies! Smile

Just as we went to pass each other the dog decided it wanted to come and say “hi” to me (There was no malice in its action, I’m pretty sure it was just being friendly), the owner noticed this and simply gave the dogs leash (lead) a gentle tug and said something along the lines of “come on” to the dog and we all went on our way.

As I was walking back to my place I started to think about the dog and its owner and that little scenario I just described. As I thought about it some more I realised was that the reality of the situation was if that dog really wanted to make its way to me, be it to just say hi or to cause me harm, there was very little myself or the owner could have done about it, due to the large and muscular nature of the animal. Yet all the owner had to so was give the littlest of tugs and a gentle word and this strong and proud animal continued on its way.

Again this got me thinking as to how this situation could come about, if this was a “wild” animal on a lead it wouldn’t of happened, so I mentioned it to a friend of mine who trains dogs for a living. He said its all to do with conditioning from when they are pups, basically when they are small, that gentle tug on the lead and the gentle word are much more forceful. He went on to explain that effectively this idea of you being bigger and stronger than them just kind of sticks in there head and this is why the behaviour I saw was possible.

All interesting stuff, you might be thinking, but what has this got to do with me?

Mental Leash

Although we as human being obviously don’t get lead around on leash on a day to day basis, we are however held back by our own mental leashes.

As part of my job, I get to travel around a lot and go into a whole host of companies of differing size and ethos and the one common thing I can say every where I go is that I see is people being lead around by their own mental leashes, being held back by their own insecurities, fears and in some cases even laziness and lack of motivation. People stuck in a rut or stubborn in their ways. All these things have been built around experiences from the past, both positive and negative, just like a puppy is trained that its pointless to pull on its leash or that no means don’t do that.

People that allow themselves to be lead totally around by their leashes, generally speaking, are the ones who’s qualifications are from years ago, that haven’t been on a course or attended a user group / seminar or brought a book on their particular area of study for years. They end up stuck in the same role for the same company for umpteen years and get institutionalised.

All of these things can be a metaphorical leash around your neck being given a gentle tug, or the metaphorical firm word directing you away from what you want.

Now at this stage, so I don’t start to sound like a holier than thou tool, I’d like to point out that I’m far from perfect, I have a lot of my own “mental leashes” and over the last year or so I’ve been slowly trying to remove them, some more successfully than others. I still have quite a few but I try not to let them pull me around quite so much

So, what’s your leash?

My challenge to you all reading this now is to think about your leashes, what are the things that might be holding you back. Is it that little voice inside that says “cant” or maybe that fear you get when something new comes along?

Whatever they are, identifying them is the first step, and then pulling against them / removing them so you can approach that metaphorical hansom stranger and say “hi” without getting that tug around your neck

Final thoughts

Anyway this was just something that’s been on my mind and I wanted to share it, I hope you’ve found it interesting. Please feel free to drop me any comments as I welcome all feedback

Enjoy!

Dave

Advertisements

Running year number across date ranges

Hi guys,

I recently had an interesting sql problem passed to me and I thought I’d share the solution with you all as I couldn’t find anything nice online about it.

Background

So the issue was that we had a set of data that recorded peoples membership dates (lets say it was a gym memberships). The table looked like this (roughly Smile):

  • Person_ID
  • DateFrom
  • DateTo

The requirement was to run a query that would return a year number for each row that indicated how many contiguous years the person had held a membership at that point in time. The additional complexity with this was that memberships that had a gap of less than or equal to 365 days should also be considered contiguous.

I thought about all kinds of approaches to this (and I’m sure there are some I didnt think of) but I eventually I decided that I needed to do the following:

  • Assign every record a unique ID
  • Assign every every record a parent id which will be the above created unique id of the record that comes before this one, where applicable.
  • Use a recursive CTE to work out the year number

The Code

So lets set up the test data:

CREATE TABLE Memberships
(
    MembershipID INT NOT NULL IDENTITY,
    ParentID INT NULL,
    PersonID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL
)

GO

Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '1999-10-02', '2000-10-01')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2000-10-02', '2001-12-28')
Insert into Memberships(PersonID,  StartDate, EndDate)
Values(10154997, '2002-01-10', '2003-02-10')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2003-04-08', '2003-09-15')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2003-11-26', '2004-03-15')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2004-10-05', '2004-10-06')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2005-02-02', '2005-02-09')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2005-02-24', '2005-03-01')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2006-09-28', '2007-01-07')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2008-11-11', '2009-01-12')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1982-08-06', '1983-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1983-08-06', '1984-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1984-08-06', '1985-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1985-08-06', '1986-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1986-08-06', '1987-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1987-08-06', '1988-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1988-08-06', '1989-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1989-08-06', '1990-08-05')

GO

So firstly we need to get the id of the any date records that fall behind each record by no less than 365 days and set the parent id with it:

So as you can see here, we join the table to itself twice, firstly to get a list of all memberships that ended no more than 365 days before this one started and secondly to ensure if more than one record meets that requirement that only the closest one is returned as the “parent”.

UPDATE M
SET M.ParentID = M2.MembershipID 
FROM dbo.Memberships AS M
JOIN dbo.Memberships AS M2 ON 
        M.PersonID = M2.PersonID

        -- Where the enddate is less than 365 days away from my start date
        AND DATEDIFF(dd,M2.EndDate, M.StartDate) <= 365

        -- Stops dates before the start date coming through
        AND DATEDIFF(dd,M2.EndDate, M.StartDate) > 0 

        -- This little sub query max sure that if there are multiple 
        -- start dates within 365 days of my end date that only 
        -- the closest one is returned. 
        AND M2.StartDate =
        (
            SELECT Max(X.StartDate) 
            FROM dbo.Memberships AS X 
            WHERE X.PersonID = M.PersonID
            AND DATEDIFF(dd,X.EndDate, M.StartDate) <= 365
            AND DATEDIFF(dd,X.EndDate, M.StartDate) > 0
         );

Once this bit is done then its simply a matter of a reasonably simple recursive CTE:

WITH DateGroups (ParentID, MembershipID, PersonID, 
    StartDate, EndDate, YearNum)
AS
(
    SELECT  
        M.ParentID,
        M.MembershipID,
        M.PersonID ,
        M.StartDate ,
        M.EndDate ,
        1 AS YearNum
    FROM dbo.Memberships AS M 
    WHERE M.ParentID IS NULL

    UNION ALL 

    SELECT  
        M2.ParentID,
        M2.MembershipID,
        M2.PersonID ,
        M2.StartDate ,
        M2.EndDate ,
        DG.YearNum + 1
    FROM dbo.Memberships AS M2 
        JOIN DateGroups AS DG ON 
        M2.ParentID = DG.MembershipID
)

SELECT  DG.ParentID ,
        DG.MembershipID ,
        DG.PersonID ,
        DG.StartDate ,
        DG.EndDate ,
        DG.YearNum
FROM DateGroups AS DG
ORDER BY DG.PersonID, DG.StartDate, 
    DG.YearNum

and this then gives us the result set:

image

So that’s it really, dates can be a real pain to work with but I found this to be a nice elegant solution and its nice and quick too

A quick mention and thank you to my colleague Richard Thomas (@TR5AD) for passing this original problem my way

Enjoy!

Dave

Smile