ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,406 through 6,420 (of 10,143 total)

RE: need sql query to convert a column of a table into meaningful data

You can certainly do this with TSQL - using the splitter Gianluca mentioned - but it's an expensive long-haul trip:
DECLARE @Message VARCHAR(8000)
SET @Message = 'Type=0 8=111 9=HSBC 35=d 49=DAC 56=ALA...
^{“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

December 7, 2011 at 3:03 am

#1417887
RE: Advnaced Grouping Help Needed

If you're looking for a fixed percentage of random rows from each partition (salesperson), have you considered using NTILE?
SELECT
*
FROM (
SELECT *, NTILE(10) OVER(PARTITION BY Salesman ORDER BY NEWID()) 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

December 7, 2011 at 2:37 am

#1417877
RE: Are the posted questions getting worse?

Ninja's_RGR'us (12/6/2011)
LutzM (12/6/2011)
Changing subject: Does anyone know one of the DBAinSpace finalists?
There's no name I recognize as one of the Threadizens....
There's noone I recognize from this site, period. :crying:
Wasn't...
^{“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

December 6, 2011 at 10:07 am

#1417513
RE: SQL Code Help

drew.allen (12/6/2011)
ChrisM@Work (12/6/2011)
Now imagine you're writing a query to output cleaned title, firstname, lastname from a table of appallingly bad data. You've set up a shedload of detection and cleaning...
^{“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

December 6, 2011 at 8:18 am

#1417407
RE: SQL Code Help

drew.allen (12/6/2011)
ChrisM@Work (12/6/2011)
drew.allen (12/5/2011)
ChrisM@Work (12/5/2011)
You might get one or two ideas from the following. It's proving very usable for data cleansing, which is similar to your scenario - you want...
^{“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

December 6, 2011 at 7:33 am

#1417365
RE: Select the time part of a date.

Cool, thanks for the feedback. I learned the trick from an article Lowell did years ago 😀 Thanks, Lowell.
^{“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

December 6, 2011 at 7:13 am

#1417345
RE: Select the time part of a date.

Plus 30 mins to round up:
SELECT CAST(DATEADD(hour,DATEDIFF(hour,0,DATEADD(minute,30,GETDATE())),0) AS TIME(0))
^{“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

December 6, 2011 at 7:05 am

#1417333
RE: how could i change the procedure???

Duplicate post, work in progress here.
^{“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

December 6, 2011 at 5:13 am

#1417248
RE: Storeprocedure fetching data only for one condition..

Duplicate post, work in progress here.
^{“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

December 6, 2011 at 5:13 am

#1417247
RE: storeprocedure not showing any data

Perhaps usage is supposed to be like this:
DECLARE @streamid VARCHAR(5)
SET @streamid = '519'
SELECT @streamid
FROM (
SELECT streamgroupid = '%,519%' UNION ALL
SELECT '%,519,%' UNION ALL
SELECT '%519,%' UNION ALL
SELECT '%519%'
) d
WHERE @streamid...
^{“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

December 6, 2011 at 4:58 am

#1417234
RE: storeprocedure not showing any data

pallavi.unde (12/6/2011)
Below is the query in my store procedure..
SELECT .....
where b.coursetypeid=@CourseId and b.degreeid=@DegreeId
and b.streamgroupid LIKE '''%,'+@streamid+',%'''
and b.streamgroupid LIKE +@streamid+',%'+''
and b.streamgroupid LIKE ''+'%,''%'+@streamid+''
and b.streamgroupid LIKE +@streamid+'%'
end
query is running...
^{“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

December 6, 2011 at 4:45 am

#1417221
RE: TempDB grows when using temporary table within insert sproc

Put an index on your temp table. SQL Server will jump through fewer hoops creating the INSERT source.
CREATE --UNIQUE
CLUSTERED INDEX [CXscn] ON #glxRDCount (scn ASC)
^{“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

December 6, 2011 at 1:51 am

#1417141
RE: SQL Code Help

drew.allen (12/5/2011)
ChrisM@Work (12/5/2011)
You might get one or two ideas from the following. It's proving very usable for data cleansing, which is similar to your scenario - you want to identify...
^{“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

December 6, 2011 at 1:39 am

#1417132
RE: SQL Code Help

You might get one or two ideas from the following. It's proving very usable for data cleansing, which is similar to your scenario - you want to identify a case...
^{“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

December 5, 2011 at 10:30 am

#1416858
RE: Update with Inner Join

No worries Martin.
Here's a wee hint - before you write a query, find out as much useful information as you can from the tables which will be referenced by it....
^{“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

December 5, 2011 at 8:42 am

#1416738

Viewing 15 posts - 6,406 through 6,420 (of 10,143 total)