Viewing 15 posts - 526 through 540 (of 3,957 total)
Here's a rundown of the various methods to traverse a hierarchy. You might want to try a couple of them to see what performs best for your specific situation.
https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/
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
December 22, 2014 at 5:16 pm
This is similar to Itzik Ben-Gan's Depleting Quantities challenge:
http://sqlmag.com/t-sql/t-sql-challenges-replenishing-and-depleting-quantities
It can be solved with a QU as Alan.B has suggested.
However I should warn you that there are those that are not...
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
December 22, 2014 at 5:10 pm
Perhaps you can't design it but maybe you can redesign it.
DECLARE @MyDims TABLE
(
Dimensions VARCHAR(100)
,[Length] AS(LEFT(Dimensions, CHARINDEX('X', Dimensions)-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
December 22, 2014 at 5:00 pm
Perhaps this?
SELECT CONVERT(VARCHAR(100), CAST('2014-01-01' AS DATETIMEOFFSET), 126)
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
December 21, 2014 at 11:15 pm
Help us help you next time by providing consumable sample data as I have done.
WITH SampleData (EmailID, Email, [Status]) AS(
SELECT 6207,'abbdie@nofavtt.org',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
December 21, 2014 at 11:12 pm
There are many ways to skin a hierarchical cat:
https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/
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
December 18, 2014 at 8:40 pm
Perhaps this is what you mean.
WITH SampleData (date, HourType, hours, HourValue, status) AS(
SELECT 'day1','m',1,1,'y'
UNION ALL SELECT 'day1','m',2,1,'y'
UNION...
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
December 18, 2014 at 8:21 pm
This looks quite a bit like a CSV export I recently did that was to be imported into AccPac.
Is what you want really a CSV file format?
That can be done....
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
December 18, 2014 at 8:10 pm
Or if you want all months within the range (not just the ones where the counts change), you can do this:
WITH SampleData (AccountNo, OpenDate, CloseDate) 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
December 18, 2014 at 7:59 pm
Another option:
SELECT CONVERT(VARCHAR(6), d, 112)
,CONVERT(VARCHAR(7), d, 20) + '-01'
FROM (SELECT DATEADD(month, -1, GETDATE())) a (d);
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
December 18, 2014 at 7:05 pm
There is also the following possibility using Jeff Moden's community-improved, delimited string splitter and a quirky update.
DECLARE @Parameter VARCHAR(8000) = 'this is a very long string just being used to...
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
December 18, 2014 at 6:44 pm
Or this?
WITH YourTable (ID,Client,TxDate,GrossCost) AS
(
SELECT 1,'abc',CAST('11/10/2014' AS DATE),$10.07
UNION ALL SELECT 2,'Dest','11/10/2014',$10.07
UNION ALL SELECT 3,'Dest','11/10/2014',$10.07
UNION...
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
December 18, 2014 at 6:26 pm
Why not CROSS APPLY VALUES instead?
WITH YourTable (PeriodType, PeriodValue, AgentNo, Metric1, Metric2, Metric3) AS
(
SELECT 'Year','2014','EY57627','1833','609.87','0.804878' UNION ALL
SELECT 'Year','2014','EY37480','11784','401.413','0.833333' UNION ALL
SELECT 'Year','2014','EY37715','0','0','0' UNION ALL
SELECT 'Year','2014','6069748','8242','479.842','0.857542' UNION ALL
SELECT 'Year','2014','ex51539','0','0','0' UNION ALL
SELECT 'Year','2014','6071493','4833','594.724','0.7866'...
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
December 18, 2014 at 6:09 pm
Perhaps this?
WITH Combos (Distance, Gender) AS
(
SELECT '500','Man'
UNION ALL SELECT '1000','Man'
UNION ALL SELECT '1500','Man'
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
December 18, 2014 at 6:01 pm
How about if you just stuff it?
WITH LegacyData (LegacyDate) as
(
SELECT '0980412' UNION ALL
SELECT '1100323' UNION ALL
SELECT '940930' UNION ALL
SELECT '941027' UNION ALL
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
December 18, 2014 at 5:47 pm
Viewing 15 posts - 526 through 540 (of 3,957 total)