Viewing 15 posts - 3,796 through 3,810 (of 3,957 total)
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 TT.VersionID = 2167), GETDATE())
select...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 12, 2012 at 1:33 am
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...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 12, 2012 at 12:41 am
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 'AAcc/24)2('
UNION ALL...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 12, 2012 at 12:18 am
Awesome! Two outspoken anti-RBAR-advocates in one thread threatening to learn SSIS
I am honored to be included in any sentence with a SQL MVP, especially Jeff who has been extremely encouraging...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 11:19 pm
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...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 10:16 pm
Ooops. Sorry... what was I thinking!
Hold on a minute while I look at it again.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 9:43 pm
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...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 9:03 pm
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,...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 8:49 pm
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',...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 8:22 pm
Well, the way things are going with SQL Server, don't be surprised if the use of statement terminators becomes mandatory with all statements.
Ugh! So much for upward compatibility!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 7:40 pm
Gilamonster said:
Semicolons are statement terminators, as such they should terminate statements, not start them. We don't start sentences in English with fullstops.
Semicolons are referred to as statement terminators only because...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 6:16 pm
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...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 11, 2012 at 7:14 am
Gotta bookmark this thread. SSIS is next on my SQL learning expedition!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 10, 2012 at 10:46 pm
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...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 9, 2012 at 11:38 pm
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...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 9, 2012 at 11:26 pm
Viewing 15 posts - 3,796 through 3,810 (of 3,957 total)