Hey Matt,

Nice solution! A buddy of mine has turned me onto the row_number (), and I've been trying to incorporate it more into my code. I

cdun~ you're welcome......

It is much easier to help if you post code to create and populate a temp table with sample data, and what you are looking for logically from the resultset.

I...

Another way to skin the cat is a HAVING clause (basically filtering your GROUP BY)...

HAVING Count(tc) = (subquery)

How about adding a TOP (1) clause to the SELECT and an ORDER BY COUNT (tc) DESC ?

Thank you sir... I figured it was somewhere easy.

Thanks for taking the time to respond.

Congrats fizzleme!

Good feeling, huh? Whew!

Keep it up...

That seems like another good way to skin the cat, and very lightweight. I think I like that solution better than a continuously running Profiler Trace. However, the...

Have you investigated login triggers and if they will work for your needs? In SQL 2005 you can audit logon events and store that data in a table and...

Hi Nita,

It is a dilemna.

I have had that need too, and am concerned that running a trace against our server would be too resource intensive. We have over 200...

Hi Duran,

I am a pretty big fan of Transcender. Since I started taking tests, it just seems to match my way of thinking. Seems like they cover all...

One question I have, that I haven't been able to figure out... how do you post your code in the little blue box? When I copy and paste right...

Jason... that is brilliant! You have no idea how long I spent trying to substring, charindex... what a mess.

Sorry formatting got messed up... maybe this is easier to read:

SELECT

ROW_NUMBER()OVER(ORDER BY t1.client_id, t1.date1) as stay_no,

t1.client_id,

t1.id as admit_id,

t2.id as discharge_id,

t1.date1 as admit_date,

t2.date1 as discharge_date

...

Try this out... self join to connect client_ids... first table admit, second table discharge, subquery on second to select top date that is bigger than admit date.

SELECT

ROW_NUMBER()OVER(ORDER BY t1.client_id, t1.date1)...

We are using this to create a nice name for our backups, but I'm looking for code to delete files after a certain day too. Maintenance plans always seem...