Viewing 15 posts - 1,606 through 1,620 (of 3,957 total)
Many ways to skin this proverbial cat:
WITH Buyers (buyer) AS (
SELECT '3, RRRRR'
UNION ALL SELECT '4, SSSSS'
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
August 12, 2013 at 10:44 pm
To get exactly the expected results you specified, a very minor modification to Koen's query is in order:
DECLARE @minDate DATE = '2010-01-01';
DECLARE @maxDate DATE = '2013-04-01';
SELECT DATEADD(mm,6 * number,@mindate)...
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
August 12, 2013 at 10:35 pm
Alan.B (8/12/2013)
dwain.c (8/12/2013)
Alan.B (8/12/2013)
Instead of the case statement you can also do this:
SELECT ISNULL(thing1.name,(ISNULL(thing2.name,thing3.name))) AS nameAnd why not:
SELECT COALESCE(thing1.name,thing2.name,thing3.name) AS name
?
Nice. That is cleaner and easier to read.
+1
I confess...
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
August 12, 2013 at 7:41 pm
Is it too late for me to join the party?
WITH SampleData (A, B) AS (
SELECT *
FROM (VALUES
('0002137}','0004000{')
,('0002137J','0004000A')
,('0002137K','0004000B')
,('0002137L','0004000C')
,('0002137M','0004000D')
,('0002137N','0004000E')
,('0002137O','0004000F')
,('0002137P','0004000G')
,('0002137Q','0004000H')
,('0002137R','0004000I')
,('0002137{','0004000}')
,('0002137A','0004000J')
,('0002137B','0004000K')
,('0002137C','0004000L')
,('0002137D','0004000M')
,('0002137E','0004000N')
,('0002137F','0004000O')
,('0002137G','0004000P')
,('0002137H','0004000Q')
,('0002137I','0004000R')
) a(A,B))
SELECT OldA=A, A=STUFF(A, LEN(A), 1, (A1-1)%10)*POWER(-1, CASE WHEN A1 > 9 THEN...
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
August 12, 2013 at 7:24 pm
Alan.B (8/12/2013)
Instead of the case statement you can also do this:
SELECT ISNULL(thing1.name,(ISNULL(thing2.name,thing3.name))) AS name
And why not:
SELECT COALESCE(thing1.name,thing2.name,thing3.name) AS name
?
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
August 12, 2013 at 7:06 pm
Sounds like you're looking for a way to build a catch all query in the SP.
Take a look at this article by Gail Shaw: SQL-in-the-Wild: Catch-all queries[/url]
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
August 12, 2013 at 6:42 pm
I'm not sure about your question on the joins but using the sample data provided by Geoff, here's my solution (with the "instance" column):
with
raw_data (id, customer_id, product, [date]) as
(select 1,...
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
August 12, 2013 at 6:36 pm
ChrisM@Work (8/12/2013)
Jeff Moden (8/10/2013)
Brandie Tarvin (8/2/2013)
How many SQL Spackle articles are there?
Yes, I have a reason for asking. But...
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
August 12, 2013 at 6:18 pm
Jeff Moden (8/10/2013)
Brandie Tarvin (8/2/2013)
How many SQL Spackle articles are there?
Yes, I have a reason for asking. But I...
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
August 10, 2013 at 7:48 pm
ChrisM@Work (8/9/2013)
dwain.c (8/8/2013)
ChrisM@Work (8/8/2013)
Howard - the name of the guy made me chuckle, given the pidgin English 😀
I've been hearing a lot of Tok Pisin for the last week....
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
August 9, 2013 at 6:11 am
ChrisM@Work (8/8/2013)
Howard - the name of the guy made me chuckle, given the pidgin English 😀
I've been hearing a lot of Tok Pisin for the last week. Two...
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
August 8, 2013 at 1:17 pm
How about this?
WITH SampleData AS (
SELECT Yr=2012, Mon=7, DesiredDate=CAST('07/31/2012' AS DATETIME)
UNION ALL SELECT 2014, 6, CAST('06/30/2014' AS DATETIME))
SELECT Yr, Mon, DesiredDate
...
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
August 7, 2013 at 12:06 am
ck9663 (8/6/2013)
How about something with no CTE nor subquery......
~~ CKK
?? Mine has neither. The CTE is only there to put the sample data someplace.
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
August 6, 2013 at 11:03 pm
Stefan Krzywicki (8/6/2013)
... but don't know if MySQL has any reasons to avoid it like the plague.Any opinions?
Perhaps because it's owned by Oracle?
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
August 6, 2013 at 8:31 pm
Still would like to see some test data with expected results.
This sounds very similar to a problem I worked out not too long ago. And for that I had...
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
August 1, 2013 at 8:28 pm
Viewing 15 posts - 1,606 through 1,620 (of 3,957 total)