I'm glad I could help and thanks for the feedback.

Dave

Rich

That would work just as well for a single stock item. However, the OP goes onto to say

The said Query is Perfect for Single Stock No. For example as...

Looking in BOL for the OVER clause http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx"> http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx we see

Aggregate Window Functions

<OVER_CLAUSE> :: =

OVER ( [ PARTITION BY value_expression ,...

This is totally untested but may provide a pointer

Select

CT_STOCKNO_P,

CT_STOCKSUMMARY,

CT_QUANTITY,

TR_QUANTITY,

TR_UNITCOST,

TR_TYPECODE,

TR_DATE,

MIN(TR_DATE) OVER (PARTITION BY TR_TYPECODE) AS FIRSTDATE,

MAX(TR_DATE) OVER (PARTITION BY TR_TYPECODE) AS LASTDATE

From ST_COMMODITYTYPE

Inner Join ST_TRANSACTIONS

ON ST_TRANSACTIONS.TR_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P

WHERE CT_STOCKNO_P='01-01-01-010' and (TR_TYPECODE =...

I just tried this on 2008 and the replace works as expected. The -- has been replaced with an empty string, the - is left asis. I don't have access...

Hi

Does the following link help? http://www.ureader.com/msg/11578059.aspx or have I misunderstood the requirement?

Dave

There is a maintenance cleanup task that should do this. See http://msdn.microsoft.com/en-us/library/ms345177.aspx for details.

Dave

I thought I had a Eureka moment. My thinking was what would happen if you replaced the iif clause with

Iif(Parameters!Provider.Value = "KC", " WHERE rss.Provider IN ('XYZ', 'ABC')", "...

Can you test both static queries and post the results of both?

Dave

Everything looks good to me, as Jason suggests.

Is @Provider a single value or is it allowed to contain multiple values?

Dave

Just to check that he two different versions of the query are

SELECT rss.TripDate

,rss.Provider

,rss.[Route]

...

Does the version of the query where you say there would be a lot of duplicated code actually work as expectecd?

Dave

How did you fix the original problem (what is the updated expression)? What type of variable is @Provider?

Dave

I also like this type of questiion. It tests several levels of understanding.

The explanation is also well constructed, however I think it also contains a couple of typos where...

Is there sufficient space available in the backup location?