ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,236 through 5,250 (of 10,143 total)

RE: Catch Data between symbols in SQL

dwain.c (2/8/2013)
vinu512 (2/7/2013)
...
8 seconds? What are you using? A netbook with an Atom processor?
............
It must have been. That or a wristwatch. It resolved something like 100000 rows in...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 8, 2013 at 1:58 am

#1585796
RE: Finding patterns in rows (date ordered)

greg.bull (2/8/2013)
OK best I can think of then is to use a calendar table to plug holes in the data - processing each staffid one by one in a supervisory...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 8, 2013 at 1:54 am

#1585793
RE: Catch Data between symbols in SQL

vinu512 (2/7/2013)
hi Chris, it definitely runs like a Rocket. It would be really nice if you could explain the following code :
SELECT df = LEFT(b.AllLevels, CHARINDEX('»',b.AllLevels,1)-1)
UNION ALL
SELECT df =...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 8, 2013 at 1:45 am

#1585789
RE: Catch Data between symbols in SQL

Sean Lange (2/7/2013)
How about using the DelimitedSplit8k? This seems to be super fast to me.
Select Id,
MAX(Case When ItemNumber = 1 Then Item Else '' End) As...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 8, 2013 at 1:31 am

#1585785
RE: Nested SQL Query - % of Group Total

mjbkm (2/7/2013)
OK, I was closer.
Also, I applied this to my live query and I got this error:
Divide by zero error encountered.
-- replace this
SELECT Percentage = CAST(q.Amount*1.00/q.GroupTotal AS NUMERIC(5,2))
-- with this...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 9:23 am

#1585520
RE: Nested SQL Query - % of Group Total

Sean Lange (2/7/2013)
ChrisM@Work (2/7/2013)
Actually I misread your post Sean - thought you had used the "aggregate in a derived table" trick, or I wouldn't have posted this!
I am not sure...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 9:12 am

#1585507
RE: Nested SQL Query - % of Group Total

mjbkm (2/7/2013)
Got it!!!!!!!!
...
Not quite...
WITH SampleData ([Group], [Type], Amount) AS (
SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 9:11 am

#1585504
RE: Nested SQL Query - % of Group Total

mjbkm (2/7/2013)
ChrisM@Work (2/7/2013)
Aggregate Amount by Group and type, then feed the result into Sean's original query.
That is where I was stuck before. How would I change this?
SELECT
[Group],
[Type],...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 8:55 am

#1585482
RE: If or case staement in a where?

It can be easier to visualise complex filters by switching them into a CROSS APPLY for testing. Shamelessly nicking Orlando's setup, it might look like this;
USE tempdb;
IF OBJECT_ID(N'tempdb..#CLIENT_IDENTIFIER_TYPE') IS NOT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 8:54 am

#1585481
RE: Nested SQL Query - % of Group Total

Aggregate Amount by Group and type, then feed the result into Sean's original query.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 8:41 am

#1585472
RE: Nested SQL Query - % of Group Total

mjbkm (2/7/2013)
This is working great too. What if I add multiple types per animal in first. How do I group and total first and then get the same...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 8:23 am

#1585460
RE: Nested SQL Query - % of Group Total

Sean Lange (2/7/2013)
ChrisM@Work (2/7/2013)
;WITH SampleData ([Group], [Type], Amount) AS (
SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 8:22 am

#1585458
RE: Nested SQL Query - % of Group Total

;WITH SampleData ([Group], [Type], Amount) AS (
SELECT 'Dog', 'Typea', 1 UNION ALL
SELECT 'Dog', 'Typeb', 2 UNION ALL
SELECT 'Dog', 'Typec', 3 UNION ALL
SELECT 'Cat', 'Typea', 10 UNION ALL
SELECT 'Cat', 'Typeb', 20
)
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 8:08 am

#1585447
RE: Only NUMBERS

carlosaamaral (2/7/2013)
OPS... living and learning ....
changed to level 100 - perfect ..
; WITH THE Strings (
SELECT String = 'AJKY.GSEFD6, 7345535HNB, BBN4343.434'
UNION ALL SELECT '11-908 / *** 88AB; '
)
SELECT
s.String,
x.NewString
FROM Strings...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 7:51 am

#1585437
RE: Only NUMBERS

carlosaamaral (2/7/2013)
Hello Chris,
Sorry - I may be doing something wrong ...

Message 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
;WITH Strings AS (
SELECT String = 'AJKY.GSEFD6,7345535HNB,BBN4343.434'
UNION ALL...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 7, 2013 at 6:28 am

#1585401

Viewing 15 posts - 5,236 through 5,250 (of 10,143 total)