ChrisM@Work

Forum Replies Created

Viewing 15 posts - 31 through 45 (of 10,143 total)

Reply To: display only line which begin with 1 and followed by 2
```
SELECT *
FROM (
SELECT 
classement = row_number() over(partition by NumeroContrat order by NumeroContrat desc), -- *
GroupSize = COUNT(*) OVER(PARTITION BY NumeroContrat),
* 
FROM Gestion_Stats.dbo.produit
) d
WHERE GroupSize > 1 AND...
```
^{“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
June 11, 2020 at 11:10 am

#3762247
Reply To: Invalid length parameter passed to the LEFT or SUBSTRING function.

Make it simple - break up the expression using APPLY blocks:
SELECT *
FROM (SELECT output_response_xml = 'A few random words and then... >Thank you') d
CROSS APPLY (SELECT Startpos = LEN(LEFT(d.output_response_xml, CHARINDEX...
^{“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

May 29, 2020 at 1:12 pm

#3758627
Reply To: CTE left join not giving me correct result
Or use an alternative query:
```
SELECT DISTINCT ec.userID, ec.email 
FROM emailcommunications ec
WHERE ec.email IN ('abc@noemail.com','xyz@noemail.com','wer@noemail.com','lkj@noemail.com','zza@noemail.com','sadd@noemail.com')
AND NOT EXISTS (SELECT 1 FROM tableB b WHERE b.userID = ec.userID)
```
^{“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
May 21, 2020 at 1:50 pm

#3756570
Reply To: Are the posted questions getting worse?

Neil Burton wrote:
What's worse: a problem which starts spontaneously and for which nobody is able to track down the cause, or, the same problem spontaneously stopping over the weekend and nobody...
^{“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

May 19, 2020 at 8:20 am

#3752790
Reply To: Are the posted questions getting worse?

Grant Fritchey wrote:
I went ahead and hit the unspam. Let's see what others think. As I said, I could be wrong. More than willing to entertain the possibility (especially since 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

April 16, 2020 at 3:38 pm

#3743489
Reply To: Getting the biggest spending customers for last 7 days
I think there's an error in your GROUP BY, Frederico.
```
WITH Top5Customers AS (
SELECT TOP(5)
cus_guid, WeekCost = SUM(cost)
FROM job
WHERE duedate >= dateadd(day,-7,getdate()) 
AND duedate < dateadd(day,+1,getdate())
GROUP BY cus_guid
ORDER...
```
^{“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
April 9, 2020 at 11:21 am

#3741722
Reply To: Do I need to update statistics after compressing table?
If you're creating a table from scratch and adding indexes to that table, then you won't have to worry about stats from a compression change point of view because they'll...
- This reply was modified 6 years, 2 months ago by ChrisM@Work. Reason: Added comment about partition-level changes
^{“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
April 8, 2020 at 8:33 am

#3741333
Reply To: Do I need to update statistics after compressing table?

VoldemarG wrote:
I page-compressed a table with one Clustered and 4 non-clustered indexes, 5 million rows. Do I need to do any update statistics , rebuild, etc. ? Before the table...
^{“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

April 7, 2020 at 10:18 am

#3740987
Reply To: Are the posted questions getting worse?

Ed Wagner wrote:
Brandie Tarvin wrote:
x wrote:
Jeff Moden wrote:
MVDBA (Mike Vessey) wrote:
Grant - just reading your wordpress blog - it strikes me that I'm missing some skills in monitoring SSRS and SSIS using XE... might be...
^{“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

March 26, 2020 at 4:37 pm

#3737831
Reply To: Are the posted questions getting worse?

BrainDonor wrote:
Now you've gone and done it - I need to boil my laptop now.

No no! Nurse! I said "Prick his boil"!
^{“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

March 17, 2020 at 11:00 am

#3734934
Reply To: Are the posted questions getting worse?

Brandie Tarvin wrote:
Disneyland closed down. And I think Disney World is following suit.

Yep. Friend of mine arrived at the weekend for the trip of a lifetime having booked 22 months ago....
^{“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

March 13, 2020 at 3:17 pm

#3734095
Reply To: Overlapping Date Ranges causing cross-duplicates

Duplicate of https://www.sqlservercentral.com/forums/topic/overlapping-date-range-query-duplicating-row-in-reverse
^{“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

March 5, 2020 at 3:55 pm

#3731469
Reply To: Overlapping Date Range Query Duplicating row in reverse
```
SELECT *

FROM #DATA t1
INNER JOIN #DATA t2
ON t1.MEMBER_ID = t2.MEMBER_ID

AND t1.SERVICE_CODE = t2.SERVICE_CODE

AND t2.SERVICE_START_DATE <= t1.SERVICE_END_DATE

AND t2.SERVICE_END_DATE >= t1.SERVICE_START_DATE

--AND t2.SERVICE_START_DATE >= t1.SERVICE_START_DATE -- I think this is...
```
^{“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
March 5, 2020 at 3:43 pm

#3731464
Reply To: dbcc traceon(2453,-1)

There are some excellent papers out there discussing the pros and cons of table variables and temp tables. Here's one of my favourites: https://www.sql.kiwi/2012/08/temporary-tables-in-stored-procedures.html

^{“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

March 5, 2020 at 3:00 pm

#3731436
Reply To: How to get datediff between two different columns and rows
```
SELECT 
CwsCalID, StartDateTime, EndDateTime, TimeSlotAvailable,
[ConsecutiveAvailableTimeslots] = COUNT(NULLIF(TimeSlotAvailable,0)) OVER(PARTITION BY Grouper)
FROM (
SELECT *, 
Grouper = ROW_NUMBER() OVER(ORDER BY TimeSlotAvailable, StartDateTime) - ROW_NUMBER() OVER(ORDER BY StartDateTime)
FROM #T
) d
ORDER BY...
```
^{“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
March 5, 2020 at 1:36 pm

#3731406

Viewing 15 posts - 31 through 45 (of 10,143 total)