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?

For a single result set that you can join to...

--===== Declare your parameters

DECLARE @pYear INT, @pMonth INT

--===== Set the parameters (simulates a proc or udf parameters)

SELECT @pYear = 2008,

...

To get all of the dates in the range all at once, then the following will do it for you...

--===== Here are the two parameters you wanted

DECLARE @DateStart DATETIME

DECLARE @DateEnd...

Well... lemme shift gears here, a bit. You say you want a "text representation" of the matrix of 100,000 columns and 10,000 rows... that's 1,000,000,000 or a Billion "cells"...

Understood... I was agreeing with you...:)