engstevo (7/3/2013)
it's my test for BI position ,It's a real problem
It's SQL Server 101, and I'm surprised that the tests for a BI position can be this simple. If you cannot work at this level of simplicity, then you may wish to consider studying for a little longer before applying again. Real world BI problems and their solutions are unlikely to be within your capabilities at this time.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden