ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,716 through 2,730 (of 10,143 total)

RE: Error in stored procedure

or resolve it in the string:
+ '
FROM employee m
LEFT JOIN shiftdetails b ON m.empno = b.empno
where b.shifttype = ''' + @shifttype +
''' GROUP 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

January 15, 2015 at 4:53 am

#1770400
RE: Today's Random Word!

BWFC (1/15/2015)
richxs (1/15/2015)
Giraffe DBA (1/14/2015)
SQLRNNR (1/14/2015)
Ed Wagner (1/14/2015)
djj (1/14/2015)
BWFC (1/14/2015)
Ed Wagner (1/14/2015)
Stuart Davies (1/14/2015)
TomThomson (1/13/2015)
Ed Wagner (1/13/2015)
SQLRNNR (1/13/2015)
regex
Pattern
Match
Point
Edge
Bono
Sonny
Cher
Babe (i've got you ...)
Ruth
Sultan
Swing
Swap
^{“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 15, 2015 at 2:28 am

#1770357
RE: Are the posted questions getting worse?

Lynn Pettis (1/14/2015)
Steve Jones - SSC Editor (1/14/2015)
Boy, it seems like my questions are getting worse lately. Taking a beating in the QoD.
Don't worry about it. Some people...
^{“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 15, 2015 at 1:59 am

#1770348
RE: Are the posted questions getting worse?

Koen Verbeeck (1/13/2015)
FYI, I received the Author of the Year award at MSSQLTips.com!
Thanks everyone for voting!
Very well done mate, richly deserved.
^{“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 15, 2015 at 1:53 am

#1770347
RE: Restricted LEFT JOIN

Aaron N. Cutshall (1/14/2015)
Yet, not surprising when you consider that the parent table has 1.85M rows, the child table has 7.5M rows, and the validation table has 54K. The...
^{“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 15, 2015 at 1:46 am

#1770344
RE: Restricted LEFT JOIN

Aaron N. Cutshall (1/14/2015)
Chris' solution works well in environments with low to medium data volume, but in the very high data volume environment I have it's too slow. I'm...
^{“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 15, 2015 at 1:43 am

#1770342
RE: Counting values

briankwartler (1/14/2015)
edwardwill (1/14/2015)
Actually, it returns:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.
Perhaps you are missing a comma?
No, it's version-specific. The code runs on 2008+.
^{“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 14, 2015 at 6:14 am

#1770081
RE: Restricted LEFT JOIN

Aaron N. Cutshall (1/13/2015)
ChrisM@Work (1/13/2015)
There are two issues here which might be causing some confusion.
Firstly, the FROM list of your query, which looks like
SELECT ...
FROM parent
LEFT JOIN child to...
^{“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 14, 2015 at 1:30 am

#1770032
RE: Restricted LEFT JOIN

There are two issues here which might be causing some confusion.
Firstly, the FROM list of your query, which looks like
SELECT ...
FROM parent
LEFT JOIN child to parent
INNER JOIN grandchild to...
^{“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 13, 2015 at 2:23 am

#1769730
RE: Multiple Condition CASE Statement or Sub-Queries

SELECT tp.*, s.acct, s.enterDate, s.processDate
FROM temp tp
INNER join staticTable s
ON tp.acct = s.acct
WHERE
s.enterDate NOT IN (@conversiondate, @termDate)
OR -- if s.enterDate IS equal to @conversiondate or @termDate...
^{“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 12, 2015 at 9:30 am

#1769550
RE: How to check effect of insert statement during debugging of stored procedure

SAMDEV (1/12/2015)
Actually i have hard-coded value for @Rec but it gets calculated for each record being inserted from source table into destination.
For this purpose I cannot directly insert values 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 12, 2015 at 7:45 am

#1769505
RE: How to check effect of insert statement during debugging of stored procedure

SAMDEV (1/12/2015)
Actually i have hard-coded value for @Rec but it gets calculated for each record being inserted from source table into destination.
For this purpose I cannot directly insert values 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 12, 2015 at 7:44 am

#1769504
RE: How to check effect of insert statement during debugging of stored procedure

SAMDEV (1/12/2015)
I am debugging a stored procedure. During this I am executing some insert statements on a table which are created from within stored procedure only.
Now, during my debugging...
^{“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 12, 2015 at 7:16 am

#1769492
RE: QUERY HELP minus from max allowed

You might want to read about table aliases - they will make your code much easier to read:
SELECT
12-COUNT(a.PatientID) AS NumberOfPatientsCurrentlyEnrolled,
a.CourseID,
l.LocationDesc,
c.CourseDate
FROM dbo.tblCourses c
INNER JOIN dbo.tblLkup_Location l
ON c.Location_ID...
^{“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 12, 2015 at 6:14 am

#1769470
RE: query to return rows with c2=c3

;WITH Primaries AS (
SELECT *
FROM #Test
WHERE id = 3
)
SELECT *
FROM Primaries
UNION ALL
SELECT t.*
FROM #Test t INNER JOIN Primaries p ON p.c1 = t.c2
^{“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 9, 2015 at 9:53 am

#1769140

Viewing 15 posts - 2,716 through 2,730 (of 10,143 total)