Jcrawf02,

Your comment,

Check out Gail Shaw's Dirty Dozen presentation from her blog, SQL In The Wild.

You'll see that using a date function in the WHERE like that will actually...

Jeff,

I have listed the results of running your query against existing Claims data containing

35923 records, below. I have added a WHERE clause to restrict the values to 2006...

Hi all,

Jeff, thanks for your patronizing complements... Just kidding. I sincerely thank you. I received some helpful replies particularly Nigel's.

Nigel, this is an excellent solution....

Nigel,

I tried your second solution on 35923 records but I added the

HAVING clause to restrict the rows to 2006 and 2007 with this:

HAVING YEAR(theDay) > 2005 AND YEAR(theDay) < 2008...

Jeff,

Ok, Jeff here is some data and the code I have used to accomplish the result I need. For 10

records this is no problem, but I found out yesterday...

Hello all,

I have come up with a solution that works, but I don't like it. 🙂

What I have done is use the above tally solution and have used...

Nigel,

Thanks for your response. I have read Jeff's article before. I hadn't started down the tally table road for this problem. You're right on with using...

As it turns out this is a bug in landscape reports. I removed all the elements on my page and left only a table in the body and a...

Well, I solved my own problem. I needed to make the @sort not allow blanks. Once I did this, it worked as expected!

So, I'm interested to hear the conclusion of this matter. Peter seems to have picked up on a way to extract more out of the tally table method. ...

Say it ain't so. This is the first I've heard a set based solution losing to RBAR.

I don't have the desire to check right now, but what about using a...

Jack,

Thanks for your input. I have gone ahead and created parameters to store my hard coded values. So, I have a lot of parameters, it's not that big...

Ok, I figured out the problem. The issue was I have more than one dataset created by the same stored procedure using the same parameter name @myParam. The...

See my above post, I had edited it instead of creating a new post by mistake.

I've never used profiler to look at a report, but I could try.

Jack,

There's no error, but the dataset simply doesn't populate. The stored procedure I'm using creates a dataset which I am trying to use as a dropdown for the...