ChrisM@Work

Forum Replies Created

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

RE: SQL Query

Assuming you are referring to the ORDER BY clause, then visualising the result of those CASEs will help:
DROP TABLE #OFFICES
CREATE TABLE #OFFICES (
OFFICEID INT NOT NULL,
OFFICENAME VARCHAR(100),
HEADOFFICEID INT
)
INSERT INTO #OFFICES...
^{“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 30, 2014 at 2:01 am

#1709724
RE: SQL Help requested to Get Daily and Weekly OT

On second thoughts, Thava's query appears to do the trick - the boundary between daily OT and weekly OT isn't total hours, it's regular hours. My query becomes this:
-- Daily_OT...
^{“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 29, 2014 at 6:27 am

#1709410
RE: SQL Help requested to Get Daily and Weekly OT

'01/06/2014', '14.25', 'Monday'-- REG = 8.00, DAILY_OT = 6.25, WEEKLY_OT = 0.00, TOTAL HOURS = 14.25
'01/07/2014', '12.25', 'Tuesday'-- REG = 8.00, DAILY_OT = 4.25, WEEKLY_OT = 0.00, TOTAL HOURS =...
^{“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 29, 2014 at 3:49 am

#1709358
RE: Performance related question

-- SPI CPI - Programscores
SELECT
a.ProgramId, a.Tier4Id, a.[DATE],
SPI= a.Value7,
SPI_H= CASE
WHEN a.[Value7] IS NULL OR ISNULL(m7.[IsGoalOriented],0) = 0 THEN 5 /*OR MBM.fn_CheckGoalsAvailability(A.MetricId) = 0 */
WHEN a.[Value7] BETWEEN...
^{“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 28, 2014 at 5:52 am

#1709125
RE: Performance related question

“Peel one potato at a time” as Jeff says. Firstly, separate out the source query from the MERGE statement. What you notice with this simplification - cutting down only...
^{“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 28, 2014 at 4:43 am

#1709106
RE: Joining where values do not equal one another

jhinch (4/23/2014)
I apologize for not using the proper forum etiquette. This was my first post ever, and I overlooked the etiquette section before posting. I will follow 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

April 24, 2014 at 8:52 am

#1708428
RE: Using CASE Statements

mlwiller2 (4/24/2014)
Yeah CASE was the wrong term to use. Looks like I want something like this:
IF ((SELECT DATEPART(MONTH, GetDate())) = 04)
UPDATE
db1
SET
...
^{“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 24, 2014 at 7:12 am

#1708398
RE: Performance related question

And while you're doing that, don't forget the effect of wrapping functions around columns in your WHERE clause (or joins)
WHERE S.LSKINdicator = 1
AND S.ProjectId IS NOT NULL
/*AP3-820 Check for...
^{“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 24, 2014 at 7:04 am

#1708391
RE: Using CASE Statements

mlwiller2 (4/24/2014)
I'm having some trouble grasping the use of CASE statements (used to using If/Then). I would like to convert this to CASE format:
If DATEPART(Month,Datetime) = 04
...
^{“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 24, 2014 at 6:57 am

#1708387
RE: Inserting multiple records from a single variable

Your sample indicates that table1 is an exact copy of two columns of table2 - is this really the case?
Here's one way to do what you want, if table1 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

April 24, 2014 at 5:19 am

#1708369
RE: Query optimization help

Lynn Pettis (4/23/2014)
I have downloaded and looked at both of the latest sqlplans, both use the same index due to the use of the WITH(INDEX(AI_OperatorCBGEstimate_HAP)) index hint on the query.
File...
^{“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 24, 2014 at 2:07 am

#1708341
RE: Drop Identity column from temp table

The two CROSS APPLY blocks collect the column lists. FOR XML PATH concatenates the lists into a string. You can read about APPLY in the two excellent articles by Paul...
^{“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 23, 2014 at 9:17 am

#1708158
RE: Query optimization help

Why did you create two identical indexes?
How many environments do you have which will run this 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

April 23, 2014 at 6:42 am

#1708067
RE: Drop Identity column from temp table

Can you present the user with a list of relevant tables? If not, you could check the passed-in tablename against a list.
^{“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 23, 2014 at 6:16 am

#1708057
RE: Query optimization help

EasyBoy (4/23/2014)
I have also noted one more thing.
I can't to modify existing index due to business rule. So, i havent used AI_OperatorCBGEstimate_14947. But i have created new index(AI_OperatorCBGEstimate_HAP) with 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

April 23, 2014 at 5:39 am

#1708051

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