Here is another interesting way:

select Type2=substring(Type,charindex('-',Type,charindex('-',Type)+1)+1,

  len(Type) - charindex('-',Type,charindex('-',Type)+1))

Frank

I wish you can relate your message to some of my clients -NOT to focus so much on performance....

Frank

I am not sure why, in the case of no NULLs, you are using the MAX() function.   The following CASE statement does the same:

select case when job_id>=job_lvl then job_id

               else job_lvl

        ...

You can create a new table of table 2 unique rows by a simple select statement:

select *

into table3

from table2 t2 left outer join table1 t1

on t2.KeyColumn=t1.KeyColumn

where t1.KeyColumn is null

Ella

What I am trying to find out is whether you have checked the data in SQL Server, if its not corrupt when coming over from FORTRAN.

Ella

How are you migrating the data from FORTRAN?  Were these files checked for errors?

on second thought, a simpler way to do it is:

create function fn_NumOfDaysInMon (@date datetime)

 returns int

as

begin

declare @days int

select @days=datepart(dd,dateadd(dd,-datepart(dd,@date),dateadd(mm,1,@date)))

return @days

end

I use the following function:

create function fn_NumOfDaysInMon (@date datetime)

 returns int

as

begin

declare @begmonth datetime,@endmonth datetime,@days int

select @begmonth=dateadd(month,datediff(month,0,@date),0)

select @endmonth=dateadd(dd,-datepart(dd,@date),dateadd(mm,1,@date))

select @days=datediff(dd,@begmonth,@endmonth)+1

return @days

end

Use it as following:

select dbo.fn_NumOfDaysInMon('04/21/04')

-------

30

The self-join was made to order for such queries, provided that the number of rows were relatively low. 

example:

select  x.record,x.type,x.[in],x.out,x.balance,cum_total=sum(y.balance+y.[in]+y.out)

from  co_test x inner join co_test y

on x.record>=y.record

group by x.record,x.type,x.[in],x.out,x.balance

result:

record      type      ...

Our policy about "dirty reads" (or uncommitted dependencies) is to do it only when necessary, in read only, but notify the user (different color screen) that it is not final.

...

Thanks MORRIJL for your SP. It comes in handy.

Thanks JXFLAGG. The OUTPUT parameter worked well.

The keyword TOP with an ORDER BY will always return the TOP # only AFTER doing the ORDER BY.

For B to C server you can use the OPENROWSET by program although it may be slow. See BOL for OPENROWSET

Mia

this change to your code will make it work:

right(rtrim(t2.email), len(rtrim(t1.domain)))