ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,811 through 3,825 (of 10,143 total)

RE: Return random records in a table-valued function?

Jeff Moden (1/2/2014)
You can also just create a view called (for example) ANewID that generates a GUID as a single column, single row result and simply join to it 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

January 3, 2014 at 2:39 am

#1677156
RE: Return random records in a table-valued function?

pietlinden (1/2/2014)
Jeff,
I think that's how I got the random number of records per enrollment so that the distribution is irregular. I noticed that Chris's code returns the same number...
^{“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

January 3, 2014 at 2:37 am

#1677155
RE: sort by serialcode

sayedkhalid99 (1/3/2014)
... after 1.3 , 1.5 shall come and then 1.10....
1.10 usually sorts before 1.3. If this is the case, try casting to decimal:
SELECT * FROM @Result ORDER BY CAST(Serial...
^{“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

January 3, 2014 at 1:54 am

#1677143
RE: Need help with the query.........

dwain.c (1/2/2014)
ChrisM@Work (1/2/2014)
... for now we've got a rusty car wheel, a piece of lime green chalk and a small fish!
No small fish allowed.
Missed the prefix "sail" 😛
^{“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

January 3, 2014 at 1:47 am

#1677140
RE: Need help with the query.........

dwain.c (1/2/2014)
pietlinden (1/2/2014)
But what if it's a Babelfish and I need it to speak T-SQL?
You can use:
EXEC sp_translate @SourceLanguage='gibberish', @TargetLanguage='T-SQL'
This comes in very handy when all you've got for a...
^{“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

January 3, 2014 at 1:46 am

#1677139
RE: Unable to convert character to datetime

That will give you date but not time. The code I posted returns datetime, from which you can extract both date and time
CAST(CAST(CounterDateTime AS CHAR(23)) AS DATETIME)
and it's intuitive too...
^{“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

January 3, 2014 at 1:41 am

#1677138
RE: Unable to convert character to datetime

SELECT
x.CounterDateTime, -- stare & compare
[Date] = CONVERT(VARCHAR,x.CounterDateTime,101),
[Time] = RIGHT(CONVERT(VARCHAR,x.CounterDateTime,0),7)
FROM (-- "Native" CounterDateTime looks similar to this
SELECT CounterDateTime = '2013-12-30 12:09:00.123' + CHAR(10)) d
CROSS APPLY (-- so convert 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

January 2, 2014 at 8:25 am

#1676926
RE: Unable to convert character to datetime

Rando (1/2/2014)
Still searching for a solution. Scott's solution yield's syntax errors and Chris' solution yields the entire date field...
Again, I'm just looking for the results to be the following;
Date: 12/30/2013
Time:...
^{“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

January 2, 2014 at 7:11 am

#1676894
RE: Need help with the query.........

kapil_kk (1/2/2014)
ChrisM@Work (1/2/2014)
FOR XML PATH is used for concatenating values from different rows - the value you are attempting to assemble is a concatenation of values on the same row....
^{“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

January 2, 2014 at 7:09 am

#1676892
RE: Need help with the query.........

FOR XML PATH is used for concatenating values from different rows - the value you are attempting to assemble is a concatenation of values on the same row. Just use...
^{“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

January 2, 2014 at 6:55 am

#1676887
RE: group by, distinct not working as expected

Just a thought...what's this meant to do?
(dbAddDate >= '2013-01-01' OR '2013-01-01' = '')
AND (dbAddDate <= '2013-12-31' OR '2013-12-31' = '')
^{“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

January 2, 2014 at 5:52 am

#1676855
RE: DB server comes to a crawl every two weeks

Grab actual query plans for whatever query is responsible for "Existing orders with 200 lines take 10 minutes to retrieve."
One plan from when response is fast, one plan from...
^{“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

January 2, 2014 at 5:05 am

#1676834
RE: group by, distinct not working as expected

Try this as a test:
SELECT
dbPatID,
dbAddDate,
dbStaffLastName,
RefTypeWord = MAX(RefTypeWord)
FROM EPSReferralKPIs
WHERE
dbAddDate >= '2013-01-01' OR '2013-01-01' = ''
AND dbAddDate <= '2013-12-31' OR '2013-12-31' = ''
AND dbStaffLastName IN ('Swanepoel','Patient','Pelletti','Ray','Qureshi','Grobler','Hedborg','De Kock','Lima','Check...
^{“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

January 2, 2014 at 4:59 am

#1676832
RE: group by, distinct not working as expected

What about the other columns? Can you show a few rows of what you have and what you would like to see? Remember - we can't see what you can,...
^{“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

January 2, 2014 at 4:43 am

#1676830
RE: join 2 tables without duplicate rows

Here's an alternative to Abu Dinas' suggestion;
SELECT
b.dbPatID,
b.dbPatFirstName,
b.dbPatLastName,
x.dbStaffLastName,
b.TotTot
FROM EPSAgedBalances b
CROSS APPLY (
SELECT TOP 1 dbStaffLastName
FROM EPSAllContracts c
WHERE c.dbPatID = b.dbPatID
) x
WHERE b.TotTot < 0
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

January 2, 2014 at 2:30 am

#1676810

Viewing 15 posts - 3,811 through 3,825 (of 10,143 total)