>Hi Naomi. Thanks for replying with a solution.
>... I have not used a few of the constructs you have suggested so will need to read up/ experiment to understand exactly whats going on.
>
>regards,
>Gerard
>
Denis Gobo published a great series of blogs explaining many of the new features of SQL Server. Take a look at the summary blog here
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-advent-2011-recap

And you can look at Day 5 and Day 6 in this blog to understand Common Table Expressions and Window functions I used in my sample.