ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,516 through 4,530 (of 10,143 total)

RE: using case in where clause having other conditions

ssurekha2000 (6/12/2013)
not fully understood
using the above i am not getting any records
but in query analyser i get 100 records with date condition & id condition
Please provide more detail. Your 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

June 12, 2013 at 4:23 am

#1623265
RE: using case in where clause having other conditions

-- use APPLY to evaluate your filters
SELECT *
FROM table1
CROSS APPLY (
SELECT
Filter1 = CASE
WHEN @con = 0 AND YEARMONTH >= REPLACE(@YearMonth,'-','') THEN 1
WHEN YEARMONTH <>...
^{“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 12, 2013 at 2:00 am

#1623229
RE: Simplifying The Stored Procedure

Substitute each reference to the temp table #records with the actual query used to generate the temp table, then eliminate unnecessary table references. This takes you back to a "raw"...
^{“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, 2013 at 2:14 am

#1622750
RE: Odd Conversion Error

STUFF() works nicely too:
SELECT
PhoneNumber,
REPLACE(PhoneNumber,LEFT(PhoneNumber,5),''),
STUFF(PhoneNumber,1,5,'')
FROM (
SELECT PhoneNumber = CAST('(999)4746641' AS VARCHAR(15)) UNION ALL
SELECT '(718)4746641'
) d
WHERE LEFT(PhoneNumber,5) = '(999)'
^{“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 10, 2013 at 5:31 am

#1622388
RE: Update Performance

Alexander Suprun (6/7/2013)
ChrisM@Work (6/7/2013)
Something like this can help, and it's easy to check;
UPDATE D SET
Track_ID = P.Track_ID,
Territory_ID = P.Territory_ID,
Major = P.Major
FROM Staging.Track_Play D
INNER JOIN Staging.Track_Mapping P
ON P.ISRC =...
^{“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 10, 2013 at 1:20 am

#1622325
RE: Update Performance

Something like this can help, and it's easy to check;
UPDATE D SET
Track_ID = P.Track_ID,
Territory_ID = P.Territory_ID,
Major = P.Major
FROM Staging.Track_Play D
INNER JOIN Staging.Track_Mapping P
ON P.ISRC = D.ISRC_Code
AND P.Event_Date =...
^{“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 7, 2013 at 7:25 am

#1622058
RE: prevent simultaneous update

You might consider something like this too:
DECLARE @TaskID INT
UPDATE TaskTable SET
TaskStatus = 2,
TaskOwner = @user-id,
Assigned = 1, -- don't forget
@TaskID = TaskID -- collect TaskID for client
WHERE TaskID =...
^{“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 7, 2013 at 6:52 am

#1622049
RE: Inline Table Valued Function

Sean Lange (6/6/2013)
... In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don'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

June 7, 2013 at 1:05 am

#1621985
RE: APPLY versus ROW_NUMBER versus double JOIN to retrieve most recent record from a foreign key table

SELECT
sh.ServicerHistoryId,
shrc.CommissionRate
FROM dbo.ServicerHistory sh
OUTER APPLY (
SELECT
d.CommissionRate
FROM (
SELECT
ishrc.CommissionRate,
RowNum = ROW_NUMBER() OVER (PARTITION BY ServicerHistoryID ORDER BY EffectiveDate DESC)
FROM dbo.ServicerHistoryCommissionRate ishrc
WHERE ishrc = ServicerHistoryID = sh.ServicerHistoryID
) d
WHERE d.Rownum = 1
)...
^{“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 6, 2013 at 9:31 am

#1621799
RE: APPLY versus ROW_NUMBER versus double JOIN to retrieve most recent record from a foreign key table

Matthew Darwin (6/6/2013)
...
Any tips, pointers or observations are most welcome!
Have you tried using the row_number method with the outer apply so you have an even playing field?
^{“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 6, 2013 at 9:24 am

#1621795
RE: Issue while joining two tables with no unique columns

A long time ago in a galaxy far, far away a bunch of folks were arguing about an exotic and rarely-mentioned internal feature of SQL Server for Pangaians 238857. 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

June 6, 2013 at 9:02 am

#1621782
RE: Brain lock...

jconaty54 (6/6/2013)
I have a table with schema that looks like this
Year, Jan, Feb, Mar, Apr, May,June, July, Aug, etc...
The report calls for the month selected plus 3 months back. 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

June 6, 2013 at 8:30 am

#1621765
RE: Are the posted questions getting worse?

rodjkidd (6/6/2013)
Anybody from the thread heading to SQL Saturday Edinburgh?
I'm flying up tonight. Looks like the weather will be good - even though I'll be indoors most of the 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

June 6, 2013 at 7:28 am

#1621730
RE: Building a new server

Are you the adminstrator of the SQL server instance, Mike?
^{“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 6, 2013 at 6:36 am

#1621707
RE: Issue while joining two tables with no unique columns

Mark-101232 (6/6/2013)
... and identical solutions too!
Must be correct then!
^{“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 6, 2013 at 6:33 am

#1621706

Viewing 15 posts - 4,516 through 4,530 (of 10,143 total)