# Answering a SQL Query Question from a Recruiter

By Bill Graziano on 28 March 2001 | 7 Comments | Tags: Queries

Earlier we published a question about a SQL query that a recruiter asked a job candidate. I presented a couple of different options but didn't really think it could be done . . . until Alexander Netrebchenko sent me the solution. Here's his email and the script . . . and a job if I had one to give him :)

You can download his script and take a look at it. His email follows.

Dear graz,

Here is one possible solution for the problem described in the article 'SQL Query asked by a recruiter'. It seemed challenging, so I decided to put some time and brain energy on it.

Basically, the solution is based on the "second easiest way", though the task is accomplished in single SELECT without any tables and/or stored procedures.

The idea behind this is quite simple:

1. Generate the sequence of dates for the given period
2. Join this sequence with BUGS table and aggregate to get the count

The trick is how to generate the sequence of dates in SELECT statement. To accomplish this, I first generate the sequence of integers from 0 to N using Cartesian product (see the source code), and then use DATEADD function to generate dates.

The source is the complete example on how to do this. Well, the query looks pretty cumbersome, but I hope the recruiter would be happy with that... :)

Last point. The source may not be fully optimized (well, it's just a funny homework), so I would be just happy if you or your readers would recommend some improvements there.

Sincerely,

Alexander Netrebchenko.

Wow! One of the tricks that Alexander used is called derived tables. You can replace a table name in a SELECT query with another complete SELECT statement. An example from Books Online looks like this:

```SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,

(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA

WHERE ST.stor_id = SA.stor_id```

Alexander used a derived table to build a "table" of possible dates on the fly. We recently linked to this article about derived tables. Neat stuff.

He also used a cartesian product or CROSS JOIN to generate those dates. In this SELECT statement

```SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC```

it will return each all the publishers joined to the first author, then all the publishers joined to the second author, etc. This is a great way to generate test data in a hurry.

Thanks Alexander, for the great script and solution and letting me publish it.

 Discuss this article: 7 Comments so far. Print this Article. If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time. Email Address:

## Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

## Other Recent Forum Posts

Report model security (1 Reply)

More data extract - expert opinion (1 Reply)

Ask a query to make reports (0 Replies)

Search 2 Dates between Date Range (0 Replies)

SQL group by clause issue (0 Replies)

## Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -