Brilliant! 

Thank you very much.

jmatt

Thank you very much.  Sometimes I miss the obvious!

jmatt

I also now see the light - no fuction required (but I still like them!).  Thanks all very much for the help.

jmatt

Thanks!  The user defined function route was just what I needed.  They really are handy things!

jmatt

There always will be 3 accounts per fund and NULLS are not allowed for the accounts' IDs.

Each of these 3 accounts are for some default behavior for a particular fund...

My mistake!  Let me complete the post ...

Fund_ID   Fund1   Acct1_ID   Acct2_ID   Acct3_ID

Fund_ID   Fund2   Acct4_ID   Acct5_ID   Acct6_ID

and so on.

Accounts looks like this:

Acct_ID   Acct_Number1  

Acct_ID   Acct_Number2  

and so on.

How would I write...

I apologize for the delay in responding.

Thanks to all that responded!

Farrell, I used a variation of your suggestion ... the identity field in the temp table was the key for...

Thanks all for your replys.

I need to be more explicit ...

This is a one-time update, tbl1 is imported into my SQL databse from another system and will be used to...

Thanks again Frank.  If only life were simple ... but then again we wouldn't have a job.

OK I am going to push my luck here knowing the following is a...

Thanks a million for your help Matt and Frank!

Matt, the {0:c} formating propoerty is exactly what I needed!

Frank, your point is well taken.  The reasons for decimal you point make...

My goodness, I never thought I would have the chance to speak to the actual Master.

Thank you for your guidance;  I need time to ponder, as it is quite deep.

jmatt

Great!  Used your first suggestion and consolidated in the temp table. Things are working perfectly now.

Thanks for your help!

jmatt

I think I am half way there ...

Here is my code

CREATE TABLE #temp

(

 Fiscal_Yr_Ending int,

 Fiscal_Period int,

 Trans_Amount money,

 Acct_ID int

)

INSERT #temp

(

 Fiscal_Yr_Ending,

 Fiscal_Period,

 Trans_Amount,

 Acct_ID

)

SELECT

 t.Fiscal_Yr_Ending,

 t.Fiscal_Period,

 CASE WHEN tl.Trans_Type = 'D' THEN (tl.Trans_Amount * -1) ELSE tl.Trans_Amount...

That's brilliant!

I will give it a try and psot the final code.

Thank you!

jmatt

Thanks Steve!  That's exactly what I needed!

Jonathan