ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,831 through 4,845 (of 10,143 total)

RE: max function and where clause

Either of these:
SELECT Name, MAX(version) OVER(PARTITION BY Name)
FROM table1
WHERE name = 'Asdf';
SELECT Name, MAX(version)
FROM table1
WHERE name = 'Asdf'
GROUP BY Name;
Avoid doublequotes, they have a special...
^{“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 12, 2013 at 7:49 am

#1605967
RE: Begginer Sub Query problem

;WITH Resolver AS (
SELECT [Level] = 1, CompanyID, CompanyName, Heirarchy, ParentCompanyID,
Hpath = CAST(CompanyID AS VARCHAR(50))
FROM Company
WHERE ParentCompanyID = 0
UNION ALL
SELECT [Level] = r.[Level]+1, c.CompanyID, c.CompanyName, c.Heirarchy, c.ParentCompanyID,
Hpath = CAST(r.Hpath...
^{“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 12, 2013 at 7:38 am

#1605960
RE: Obtaining earliest change for one colum?

Here's an alternative with no CTE or ranking function:
SELECT
s.*,
y.ReasonToKeep
FROM Sampledata s -- CI scan
OUTER APPLY (...
^{“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 12, 2013 at 6:35 am

#1605935
RE: Adding to Sales Quantities

Last years' sales belong in a different table, say SalesHistory - with a column for time period, in your current case - year. This makes both tables narrower and allows...
^{“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 12, 2013 at 5:59 am

#1605918
RE: Adding to Sales Quantities

ByronOne (4/11/2013)
Hi Lynn
Is it possible to adapt this code slightly so that rather than updating the sales figures for a specified product type ie LEISURE. It creates a new product...
^{“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 12, 2013 at 5:53 am

#1605917
RE: Extract Data From A String in SQL Server

sudhirnune (4/12/2013)
declare @string varchar(500)
set @string = '-1 * [ALL_1] + [BAL] - [SAL]'
declare @pos int
declare @piece varchar(500)
declare @EXIS_VALUE VARCHAR(100)
declare @string_new varchar(500)
--Need to tack a delimiter onto the end of 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 12, 2013 at 3:20 am

#1605888
RE: CTE - UNION ALL - GROUP BY

This is an interesting one Arjun! Can you post the actual plan please?
^{“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 12, 2013 at 1:27 am

#1605870
RE: Extract Data From A String in SQL Server

sudhirnune (4/11/2013)
Hi All,
I have a String as in put, I need to Extract the Data in teh String.
Delimiter for the String is "["
String: -1 * [SAL] + [COMM] -...
^{“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 12, 2013 at 1:22 am

#1605869
RE: Very strange behaviour - Multiple Exec plans - Attached is the complete code

Lynn Pettis (4/11/2013)
And the reason you get a separate plan for the execution of the same procedure from different sessions is that the procedure is actually accessing a completely different...
^{“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 11, 2013 at 9:50 am

#1605671
RE: Very strange behaviour - Multiple Exec plans - Attached is the complete code

curious_sqldba (4/11/2013)
ChrisM@Work (4/11/2013)
Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate...
^{“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 11, 2013 at 9:18 am

#1605644
RE: Very strange behaviour - Multiple Exec plans - Attached is the complete code

Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans 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 11, 2013 at 8:52 am

#1605623
RE: Very strange behaviour - Multiple Exec plans - Attached is the complete code

I only see one plan for the conditional select, the same plan whichever parameter I use. It's what I'd expect to see. Am I missing something?
^{“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 11, 2013 at 7:45 am

#1605585
RE: Weekly report through whole year

You're welcome. Thanks loads for posting a sample data script - it doesn't half make a difference 🙂
^{“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 11, 2013 at 7:22 am

#1605565
RE: Weekly report through whole year

-- Query 1 have a gander at the data
SELECT
o.*,
p.Name,
x.CurrWeek
FROM tblOrder o
LEFT JOIN tblProduct p ON p.ProductID = o.ProductID
CROSS APPLY (SELECT CurrWeek = DATEPART(week,o.OrderDate)) x
WHERE o.OrderDate >=...
^{“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 11, 2013 at 6:23 am

#1605542
RE: Cursor help

You don't need dynamic SQL to overcomplicate this, or a cursor to make it run slowly:
DECLARE @CurrentRowCount INT
SELECT @CurrentRowCount = COUNT(*)
FROM [DB_admin1].[dbo].[tblRenewals]
INSERT INTO rept.tblSubscribedSystems
([systemNum]
,[dtuStart]
,[dtuEnd]
,[total])
SELECT
REN.[SystemNum]
,REN.[Date] AS SubStart
,x.SubEnd
,rn = @CurrentRowCount...
^{“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 11, 2013 at 5:59 am

#1605535

Viewing 15 posts - 4,831 through 4,845 (of 10,143 total)