ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,506 through 2,520 (of 10,143 total)

RE: Avoiding cursor

Like this?
SELECT
t.*,
x.RollingSum
FROM #team t
OUTER APPLY (
SELECT RollingSum = SUM(expense_amt)
FROM #team ti
WHERE ti.team_id = t.team_id
AND ti.date <= t.date
AND ti.date >= DATEADD(month,-3,t.date)
) x
WHERE t.date >= DATEADD(year, -1, GETDATE())
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

May 18, 2015 at 4:47 am

#1798578
RE: help me i need dynamic sql query for this code to update dynamically every year

Michael L John (5/15/2015)
+ 100.
I did not have enough caffeine today! As soon as I saw your post I hit myself on the head!
There won't be a person on...
^{“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 15, 2015 at 8:41 am

#1798213
RE: help me i need dynamic sql query for this code to update dynamically every year

maheendra.puttareddy (5/15/2015)
UPDATE Tablename
SET Year= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate...
^{“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 15, 2015 at 8:25 am

#1798208
RE: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

cms9651 (5/14/2015)
Hi all, hope in your help.
I have problem to execute query with interval date.
If try this query I don't have problem and the output is correct:
SELECT * FROM dotable
WHERE
dotableDate...
^{“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 14, 2015 at 7:27 am

#1797841
RE: Separating results from 1 column

Have a look at the REPLACE function in Books Online (SQL Server help)
^{“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 13, 2015 at 10:08 am

#1797604
RE: Finding the item filled in prior to this one

There are at least two alternatives which may perform better than your original. How much better depends to a great extent on indexing:
SELECT t.*, x.SUB_TYPE
FROM #TEMP t
OUTER APPLY (
SELECT...
^{“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 13, 2015 at 5:42 am

#1797495
RE: multiple index on same column

andrew_dale (4/30/2015)
many thanks for the replies, they do make sense.
checking the execution plan, if I run a simple query and include one of the index fields in the where clause...
^{“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 12, 2015 at 9:28 am

#1797307
RE: How to convert text to date?

Remove POST_DT from the 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

May 11, 2015 at 9:08 am

#1796976
RE: Tuning query

-- Write the query the simplest way. It doesn't get much simpler than this:
SELECT DISTINCT
created_dttm AS Created_Datetime,
proj_task_id AS Project_Task_Id,
CAST([text] AS VARCHAR(2000)) AS [text]
FROM dbo.P_T
WHERE...
^{“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 11, 2015 at 2:08 am

#1796882
RE: Minimizing Locking on Update statements

Stephen Knott (5/7/2015)
Hi Chris,
I didn't yet - but I will.
I have added COMMITTED SNAPSHOT isolation as it was the least invasive - i.e. didn't need to change any...
^{“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 7, 2015 at 6:13 am

#1796174
RE: Minimizing Locking on Update statements

Stephen Knott (5/4/2015)
...Another suggestion has been to try and use an exists before the update as below
IF EXISTS( SELECT based on above criteria )
( UPDATE 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

May 7, 2015 at 5:24 am

#1796160
RE: Minimizing Locking on Update statements

-- The WHERE clause must be evaluated for each row in the Stock table
-- in order to determine which rows qualify for the UPDATE.
-- The likelyhood of a table lock...
^{“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 5, 2015 at 10:03 am

#1795676
RE: SQL - Where Row_Number = 1 discussion

DROP TABLE #Team
CREATE TABLE #Team
(
[FirstName] [varchar](20) NULL,
[LastName] [varchar] (20) NULL,
[Team] Varchar (20) NULL,
[TransactionID] int,
Price float,
NoofTickets int
) ON [PRIMARY]
INSERT INTO #Team
...
^{“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, 2015 at 8:51 am

#1794720
RE: SQL - Where Row_Number = 1 discussion

Have you tried a bog-standard aggregate?
SELECT ColA, ColB, ColC = MAX(ColC)
FROM …
GROUP BY ColA, ColB
^{“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, 2015 at 6:37 am

#1794643
RE: Time OUT

mandirkumar 18293 (4/29/2015)
the sp is using more than 20 tables and too many functions . It is difficult to provide the scripts
Too vague to be of any assistance. 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

April 30, 2015 at 1:42 am

#1794585

Viewing 15 posts - 2,506 through 2,520 (of 10,143 total)