Agreed. Many ways. Here's another.

DECLARE @TESTTABLE TABLE (VersionId int, EffectiveDate DateTime)

declare @test-2 datetime

SET @test-2 = ISNULL((

SELECT TOP 1 TT.EffectiveDate

FROM @testtable AS TT

WHERE...

Vinu,

You may be right. The OP didn't provide a good visual of expected results.

When I think about transforming data from A --> B (regardless of how many steps there...

Dynamic SQL is my friend for this. 🙂

CREATE TABLE #t (strings VARCHAR(25));

CREATE TABLE #b (bad CHAR(1));

DECLARE @sql NVARCHAR(MAX), @n INT, @i INT;

INSERT INTO #t (strings)

SELECT 'AABA#24$-a.a/a2@'

UNION ALL SELECT...

I am honored to be included in any sentence with a SQL MVP, especially Jeff who has been extremely encouraging...

Still a one liner but not quite as elegant, although I think this time it will work:

DECLARE

@Startdate Datetime,

@Enddate datetime

SELECT @Startdate = '2011-05-20' ,@Enddate = '2011-05-24'

SELECT CAST(DATEDIFF(year, @Startdate, @Enddate) AS...

Ooops. Sorry... what was I thinking!

Hold on a minute while I look at it again.

Maybe somethin' like this'll do you right:

;WITH InData

(SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)

AS (

SELECT 'CORP1','0031','800281','0031','EA','DOM','800281000016')

UNION ALL SELECT 'CORP1','0032','800281','0032','EA','DOM','800281000029'

)

INSERT INTO item_tbl1

SELECT * FROM InData i

INNER JOIN item_tbl2 i2 ON i2.INV_ITEM_ID...

Jeez, Louise! All that work for a one-liner?

DECLARE

@Startdate Datetime,

@Enddate datetime

SELECT @Startdate = '1958-06-18' -- My birthday in case someone wants to send me something

,@Enddate = GETDATE()-- Now

SELECT REPLACE(REPLACE(SUBSTRING(CONVERT(CHAR(21),

DATEADD(second,...

Being the contrarion that I am, I'll offer another solution.

DECLARE @t TABLE (id INT, test VARCHAR(10), status VARCHAR(10))

INSERT INTO @t (id, test, status)

SELECT 1, 'test1', 'Passed' UNION ALL

SELECT 2, 'test2',...

Ugh! So much for upward compatibility!

Gilamonster said:

Semicolons are referred to as statement terminators only because...

Anders put a semi-colon at the end of his CREATE statement.

I like to start the WITH with a semi-colon:

;WITH CTE AS (

...

)

Sometimes its needed but sometimes its not. This...

Gotta bookmark this thread. SSIS is next on my SQL learning expedition!

Jeff,

Your explanation makes some sort of sense but methinks I'll need to try it out to fully understand.

One more question though. Who is Bob and why is he my...

Jeff,

That neat little trick with HRoot that you did in the CTE is just what I needed to get rid of the silly OR that I did on my INNER...