I'm sure you can clean this up but it will do what you want.

; with cte

as

(select d.idxmrn, d.dischargedate, a.apptdt2, DATEDIFF(d,d.dischargedate, a.apptdt2) daysSince, ROW_NUMBER() over (PARTITION by d.idxmrn order by...

SCOPE_IDENTITY() returns the identity value generated in the table in the current session.

ie., I would assume that each time you call the procedure it would be a new session and...

This might be a good place to start. It's server specific but it might give you some ideas about what to look at.

http://technet.microsoft.com/en-us/library/ff898403(v=sql.105).aspx

Maybe consider vertical partitioning and recreate in a view?

I've done the same thing using the more generic Tally or Numbers table.

Here's a good article by Jeff about it.

http://www.sqlservercentral.com/articles/T-SQL/62867/

Was this by chance originally a SQL 2000 database?

What was the trick, just in case one of us accidentally does the same thing?

Wrap the whole thing in another select and get your min and max values.

Why does this look like homework?

*bump* Really? Has no one ever tried to do this?

*bump*

Nevermind. I found the solution was to put it in a list box.

Hey Antonio, have a look at this article by Greg Larson.

http://www.simple-talk.com/sql/learn-sql-server/building-my-first-sql-server-2005-clr/

Perhaps something like this would put you on ther right track?

select sum(calls) calls, sum(answered) answered, sum(missed) missed

from

(

select case when call = 'anon' then 0 else 1 end as calls

, case...

Looks like you need to add @fiscalstart datetime to your procedure parameter block