Rob Farley - Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and runs training courses around the world in SQL Server and BI topics.

A T-SQL Tip: Working calculations

T-SQL Tuesday again and this month is on T-SQL Tips (thanks Allen!). In some ways it’s a tough topic, because there are things I don’t really consider tips that other people do, and vice-versa. This one’s legitimate though: Using CROSS APPLY for working columns in calculations.

The idea is quite straight forward – you just count the number of days from their most recent birthday. So let’s have a think.

Counting the number of years between the startdate and the enddate is a nice place to start. I could count the number of days, but that might give me rounding error based on leap years. But counting the years simply compares the year component, such as 1987 and 2003, and the player might not have had his birthday this year yet. In that case, we’d want to go with the year before. Anyway – once we’ve figured out how old the player is, we can figure out when their last birthday was and count the days since then. Easy.

But so easy to make a mistake somewhere.

And this is where CROSS APPLY can come into its own, by allowing us to use working columns.

Let’s start with a few players. Some young ones, and Thierry Henry, just because. I’m using the VALUES method, but you could have a Players table just as easily.

First, I want to make it clear which column is my startdate and which is my enddate. It’s too easy to be thinking “startdate” and pick up “Debut” here, because this sounds very much like the same thing. There’s the startdate of their playing career, and the startdate of the calculation. To avoid confusion, I’m going to do some simple column-renaming. This gives me more reusable code, and APPLY even means I never have to worry about whether I already have these column names referring to something else, because they’re going to get their own table alias too.

select * from ( values ('Cesc Fàbregas', cast('4 May 1987' as date), cast('28 October 2003' as date)) ,('Theo Walcott', cast('16 March 1989' as date), cast('19 August 2006' as date)) ,('Jack Wilshere', cast('1 January 1992' as date), cast('13 September 2008' as date)) ,('Thierry Henry', cast('17 August 1977' as date), cast('7 August 1999' as date)) ) p (Name, DoB, Debut) CROSS APPLY (select p.DoB as startdate, p.Debut as enddate) as working

Might seem like a bit of a waste to you, but it means so much to me. Really.

Now I want to count how many years there are between my startdate and enddate, and work out when the startdate is this year. I could do this in one step, sure, but I want to be able to check my working really thoroughly.

The great thing about this is that I can be very confident of my working, being able to check each step of the calculation along the way. I can even turn it into a simple TVF, whilst keeping the calculations just as verifiable:

Thanks for the great post! I enjoyed the way you built it up from previous versions leaving the reader to see each building block. It's how I try to do things at work too so it's nice for me to confirm I'm on the right track!