Are you asking for the character immediately following the second ":"? Or are you asking for the last character following the second ":"? And if it is the latter, then...

I'm not sure I understand exactly what the problem is.

Are you saying that, the stored procedure, when called, returns a recordset containing results, as well as a second recordset with...

So your solution was to basically log all activity against the tables / procedures and, upon an acceptable amount of time, deem them to not be used.

There is no way...

Well, unfortunately Partitioned Tables are only supported by SQL Server 2005 Enterprise; we have Standard.

Would Partitioned Views work well for this?

If I partition the table, then does the database intuitively know how to access the right partition, without requiring it to do heavy database accesses?

IE, if I were to partition...

it's hard to answer from just looking at the text, but it looks to me like your field in your query should be e.eecEEID, not e.eedEEID.

I'd have to see more...

Yeah that's the solution we're using for a heirarchy data structure as well. Works pretty well, although it requires you to do some pretty hefty data re-organization.

Given your table constraints, the only solution is to use a recursive query. If you had SQL Server 2008, there is a new data HEIRARCHICAL data type for this kind...

The reason your results are not corresponding to what you would have expected them to be, is that you're not understanding the operations correctly.

EXCEPT and INTERSECT compare the *entire* data...

lmu92 (5/25/2010)

---

I'm confused....

If you can't explain the benefits of the fine tuning we you did, then why there was a need for tuning in the first place? Tuning for the...

I like your solution a lot Jeff, definitely avoids the necessity of adding a new CTE for every column you have. Only thing that needs to be added now is...

Both our solutions are doing N number of queries against the same table, in order to get the most common occurring value for each column.

*nod* this one is pretty similar to the one that I listed.

Basically what I was hoping for was a solution which would not require me to run X repeat queries...

you can also do something like:

with CTE as

(

SELECT rxno, patient, location, filldate, ROW_NUMBER() OVER (PARTITION BY rxno ORDER BY filldate DESC) as rowNum

)

SELECT rxno, patient, location, filldate FROM CTE WHERE...

take a look at the Tally tables. That should solve your problem. http://www.sqlservercentral.com/articles/T-SQL/62867/