Can u be more specific. With a left outer join the first result be be null if there is no data match! Can u post samples?

I did post this on...

Could use

select distinct m.cola,m.colb from maintable m

inner join othertable x on x.key = m.key

or

select m.cola,m.colb from maintable m

inner join (select distinct key from othertable) x on x.key = m.key

Insert GO between your statements,as in

set nocount on

if not exists (select column_name from information_schema.columns where column_name='RG_ENTRYDELETED' and table_name='REGISTRATION')

alter table REGISTRATION add RG_ENTRYDELETED bit

GO

if not exists (select column_name from information_schema.columns...

Are you checking for the day (dd) or the whole date (yyyy-mm-dd) in your where clause.

If you want to use the full date then try

Declare @date datetime,@monthend DateTime

Set @date='23 may...

Beware that the date/time format is dependant on several things. NT/2000, version of IIS, whether a user is logged in or not, whether users are impersonated, the system default locale....

create table #zipsum (zip Varchar(5) null,CountPerZip int)

insert into #zipsum values (12345, 5)

insert into #zipsum values (23456, 4)

insert into #zipsum values (23457, 2)

insert into #zipsum values (11111, 1)

insert into #zipsum values...

create table tblTempZips2 (zip Varchar(15) null, id int primary key IDENTITY (1,1) not null )

insert into tblTempZips2 (zip)

select zip+' x '+cast(count(*) as varchar) from tblTempZips group by zip order by...

Create another table from this one with distinct zip values and run the same query on the new table.

declare @c int

select @c = ceiling(count(*) / 3.0) from tblTempZips

select a.zip as zip1,b.zip as zip2,c.zip as zip3

from tblTempZips a

left outer join tblTempZips b on b.id = (a.id + @c)

left outer...

Can u post table structure, small sample of data (non sensitive) and exactly what output you expect.

If the ids are consecutive and want 3 cols then

declare @c int,@i int

select @i = count(*) from #t

set @c = ceiling(@i / 3.0)

select [id] as id1,

(case when [id]+@c > @i...

Bear in mind on SQL7 you 'Cannot group by a bit column', don't know about 2000 though. You would have convert it or use case to do it.

Probably of no help as I have not done this and I am probably barking up someone else's tree but two things spring to mind.

Are the NT Groups in the...

Thanks for that Frank, will bear it in mind. We are still on SQL7 but now looking at future strategy for SQL.

You could use

where datalength(textfield) > 0

p.s. I disagree with 'You cannot use CHARINDEX on textfields', I can on SQL7 SP4.