karthikeyan (4/29/2008)

Thanks a lot for your help!

I have purified one more time.

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

SELECT @DateStart = '07/Apr/2008', @DateEnd = '29/Dec/2008'

SELECT Month = convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart))),

...

If the data is all in one table, there's no need to make it in two separate tables... you can make it appear as if you did that with table...

You both really need to make a trip to Books Online... and, just to be sure, you can do INSERTs, UPDATEs, and DELETEs on Table Variables in a UDF... just...

In this case, I'd say no indexes are necessary on the source table and for speed, the fewer indexes on the target table, the better. If the target table...

This will do it...

--===== Create a table to demo with

DECLARE @DemoTable TABLE (IPAddr VARCHAR(15))

INSERT INTO @DemoTable (IPAddr)

SELECT '192.168.215.1' UNION ALL

SELECT '192.168.23.1' UNION ALL

SELECT '192.168.5.1' UNION ALL

...

Heh... skip the "trees"... try this...

SELECT ABS(YEAR(DATEADD(dd,DATEDIFF(dd,@BirthDate,@ReferenceDate),0)-1)-1900)

So, which columns constitute a dupe? Just the first two or all of them?

GSquared (4/28/2008)

create table dbo.Numbers (

Number int identity (0, 1) primary key,

Junk bit)

go

insert into dbo.Numbers (junk)

select top 10000 0

from sys.all_objects s1

cross join sys.all_objects s2

go

alter table dbo.Numbers

drop column junk

go

select dateadd(day, number, '1/1/1900')

from...

p.s. For future reference, it's almost always going to be a huge performance drain to try to join to aggregated columns like you have. Preaggregation using a temp...

I believe every one has hit all the hot spots... Carl's last post should be a big help, as well. The problem is that the derived table in the...

That covers just about all of it... can't think of anything else to check unless the WHERE clauses have a formula in one and not the other.

Grant is correct. Tell us what needs to be done... not how to do it. There's usually no need for any form of RBAR. Post some data...

karthikeyan (4/29/2008)

I have refined the above code as

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

SELECT @DateStart = '04/Apr/2007', @DateEnd = '29/Apr/2008'

SELECT convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),103),

DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103))

FROM dbo.Tally

where DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103)) <= @DateEnd

I got the below output:

Apr...

I guess I didn't know you were a "bubble head", Grant. I was the lead ping jockey on SSN 604... did a little time on the Parche (637 stretch...

Adrian's code does create and execute "one big update" statement as you've asked... have you tried it on a test table?