ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,151 through 6,165 (of 10,143 total)

RE: UPDATE with CASE Statement not updating all Records

Welsh Corgi (6/29/2012)
I do not intend to use a WHERE Clause is ths scenario.
After I got the record COUNT correct I was going to incorporate an INSERT INTO with CASE...
^{“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 29, 2012 at 6:29 am

#1507087
RE: UDF

satishthota (6/29/2012)
I need a query without split function
What is the maximum number of discrete values to be found in the comma-delimited string?
^{“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 29, 2012 at 6:26 am

#1507085
RE: UDF

SELECT Data
INTO #PIDs
FROM dbo.Split('101,103',',')
CREATE CLUSTERED INDEX [xyz] ON #PIDs (Data)
SELECT *
FROM dbo.Product_Test p
INNER JOIN #PIDs d ON d.Data = p.PID
^{“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 29, 2012 at 6:21 am

#1507080
RE: UPDATE with CASE Statement not updating all Records

Welsh Corgi (6/29/2012)
I'm trying to get all the records from the History Table (39,026) but after I join I get only 26,086.
What is wrong with my statement and how 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

June 29, 2012 at 6:16 am

#1507076
RE: is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SQL Server will ignore both the TOP(100 percent) and the ORDER BY. You should reconsider what you are trying to do here.
^{“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 29, 2012 at 4:44 am

#1507035
RE: Ordered group by

_simon_ (6/29/2012)
@chrism-2@Work - that's it, thanks 🙂
Is there any specific reason why you used dense_rank function? I tried with two row_number functions and it still works ok.
I'd be interested 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

June 29, 2012 at 3:03 am

#1507000
RE: Stored Proc or Table Valued Function? Pivot problem

This shouldn't be too difficult.
What does @PivotColumnHeaders look like?
Can you provide DDL for the two tables, with some sample data?
^{“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 29, 2012 at 2:59 am

#1506996
RE: Ordered group by

SELECT Username,
DateFrom = MIN([Date]),
DateTo = MAX([Date]),
CodeFrom = MIN(Code),
CodeTo = MAX(Code)
FROM (
SELECT Username, [Date], Code,
rn = ROW_NUMBER() OVER(ORDER BY Username, Code),
dr = DENSE_RANK() OVER(ORDER BY Code)
FROM @originalTable
)...
^{“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 29, 2012 at 2:34 am

#1506986
RE: Availability calculation - a nice T-SQL problem

dwain.c (6/28/2012)
Jeff Moden (6/28/2012)
Not just the CROSS APPLYs but the fact that the 2nd CA refers to the 1st. Until ChrisM showed me, I haven't even tried that wonderful...
^{“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 29, 2012 at 2:15 am

#1506980
RE: Availability calculation - a nice T-SQL problem

Phil Parkin (6/28/2012)
...ChrisM - think you've got some tuning to do 🙂
...
It's awful isn't it! I don't think it can be improved either.
^{“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 29, 2012 at 2:06 am

#1506978
RE: Stored procedure error

patrickmcginnis59 (6/27/2012)
ChrisM@Work (6/27/2012)
@CLASSIFICACAO is declared as an int, you have to cast it as a string type to concatenate it into the string:
SET @STRSQL = 'INSERT INTO ' + @DB...
^{“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 29, 2012 at 2:03 am

#1506976
RE: Stored procedure error

@CLASSIFICACAO is declared as an int, you have to cast it as a string type to concatenate it into the string:
SET @STRSQL = 'INSERT INTO ' + @DB + '.DBO.SINCRONISMO...
^{“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 27, 2012 at 9:29 am

#1506217
RE: need query help - need to get prior rolling 6 months from today's date

You should be able to figure it out from this:
SELECT x.months, [YYYYMM] = CAST(CONVERT(CHAR(6),Months,112) AS INT)
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) r (n)
CROSS APPLY (SELECT Months = DATEADD(mm,-n,GETDATE())) x
^{“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 27, 2012 at 8:53 am

#1506185
RE: Production issue with varchar(max)

ramadesai108 (6/26/2012)
Also, I forgot to mention that it is a dynamic query and here is the SP:
Create PROCEDURE [dbo].[MySP]
(
@OrgId int,
@DivId int = null,
@EmpId nVarChar(MAX) = NULL,
@year int
)
AS
DECLARE @sql nVARCHAR(MAX)
DECLARE...
^{“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 27, 2012 at 8:43 am

#1506180
RE: Stored procedure error

@River1, here are two useful hints for you:
1. Don't encapsulate a batch of statements into a stored procedure until you've finished debugging them - it's much easier to debug a...
^{“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 27, 2012 at 8:28 am

#1506168

Viewing 15 posts - 6,151 through 6,165 (of 10,143 total)