I'm Looking for the last transaction after the BLL before the next BLL if there is one. So if there is no transaction after the BLL it should be null...

Try this instead. The 3 billed don't actually exist. I was just showing what I would want it to look like.

CREATE TABLE [#ClaimTransaction](

[ClaimTransactionID] [int] NOT NULL,

[ClaimTransactionTypeCode] [char](3) NOT...

Sure

PracticeIDCTIDClaimIDFinalTransactionPostingDate

210380821427853RJT2012-06-12 00:00:00.000

210387292427884RJT2012-06-03 00:00:00.000

210387296427884RAS2012-06-08 00:00:00.000

210387299427884EDI2012-06-12 00:00:00.000

210387293427885EDI2012-06-12 00:00:00.000

210379221427890EDI2012-06-08 00:00:00.000

210379222427892EDI2012-06-08 00:00:00.000

210373004428001EDI2012-06-11 00:00:00.000

210373006428002EDI2012-06-11 00:00:00.000

210373008428003EDI2012-06-11 00:00:00.000

210373010428004EDI2012-06-11 00:00:00.000

210379224428014EDI2012-06-08 00:00:00.000

210379225428015EDI2012-06-08 00:00:00.000

210380823428061RJT2012-06-12 00:00:00.000

210380825428065RJT2012-06-12 00:00:00.000

210380827433566RJT2012-06-12 00:00:00.000

210387447437805EDI2012-06-12 00:00:00.000

So for Claim 472884 There were say 3 billed transactions so I see...

So I thought I had it figured out but it doesn't seem to be working.

I created the following function and then the constraint:

Alter FUNCTION FN_IsLastAssigned(@ClaimId INT)

RETURNS INT

AS

BEGIN

DECLARE @LastAssignment Int

SET...

Thanks. I'll see if I can figure something out with a table level constraint

So I have gone away from the idea of using open source but I am now trying to determine the best way to get the data from the oltp databases...

Ok Thanks. I do appreciate all your help. And that view is the best way to monitor indexes right?

I'm glad you like my questions. I have never worked in an environment like this before so I am trying to gather as much information as I can so...

Msg 50000, Level 16, State 1, Procedure tr_Insert_Email, Line 71

profile name is not valid

Msg 3616, Level 16, State 1, Line 1

Transaction doomed in trigger. Batch has been aborted.

Yes. I had removed the insert. When I did that the error message changed and I got the It Failed along with the Transaction is doomed message.

I understand what you're saying, but is there any way to do a try catch in the trigger to allow for the originating insert to occur? In case of...

So I'm thinking I wasn't completely clear in my issue. The dbmail works fine. I am trying to do a try catch in case somebody goes in and...

It turns out it was parameter sniffing. Still not sure why the With Recompile didn't fix the problem. Thanks everyone for your suggestions.

So i thought it might be parameter sniffing. So I added a with Recompile to help with that. Also the isnull isn't really a changeable item unless I make...

If it were the indexes on the PracticeID wouldn't it be an issue in the query as well as the stored procedure? I eliminated the practiceId parameter all together...