ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,171 through 4,185 (of 10,143 total)

RE: How to update Flag based on Maximum Amount

Stefan_G (8/12/2013)
avdhut.k (8/12/2013)
Hi,
I mean Now rest of the things are correct in my requirement,
Now, In case where for two records, if ...
^{“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

August 12, 2013 at 6:23 am

#1640535
RE: SQL to do calc on different rows

SELECT
tr.*,
ISNULL(nr.c2 - tr.c2,0)
FROM #t1 tr
OUTER APPLY (
SELECT TOP 1 *
FROM #t1 ti
WHERE ti.c1 > tr.c1
ORDER BY ti.c1 ASC
) nr
^{“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

August 12, 2013 at 5:48 am

#1640518
RE: Is overpunch amenable to cross apply?

SELECT
NDCNumber,
IngredientCost = CAST(CASE
WHEN Trailer IN ('{','A','B','C','D','E','F','G','H','I')
THEN LEFT(ingredientcost,LEN(ingredientcost)-1) + CAST(CHARINDEX('{','{ABCDEFGHI')-1 AS VARCHAR(1))
WHEN Trailer IN ('J','K','L','M','N','O','P','Q','R','}')
THEN '-' + LEFT(ingredientcost,LEN(ingredientcost)-1) + '1'
END AS MONEY)/100
FROM ...
^{“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

August 12, 2013 at 5:42 am

#1640516
RE: Help required for performance tuning a view

Can you post the actual plan please Mike? There may well be scope for improving the index.
^{“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

August 12, 2013 at 5:32 am

#1640513
RE: Need Rank function to filter the records

I'd guess you have used a recursive cte to generate this data. It would help if you were to post the rCTE query and perhaps some sample data to run...
^{“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

August 12, 2013 at 5:12 am

#1640509
RE: select only some rows and then the rest

What do you mean by "in the exact order"? If the first three rows are nominated by a particular order then it would be more efficient to return the remaining...
^{“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

August 12, 2013 at 5:09 am

#1640508
RE: Help required for performance tuning a view

The main query filters on trans_class and posted, the subquery doesn't, which would make using ROW_NUMBER quite tricky.
I'd go for a new (covering) index to support the subquery, 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

August 12, 2013 at 4:58 am

#1640506
RE: How to alternate between two values in a column?

DECLARE @Outstanding DECIMAL(10,2) = 2333.12
;WITH Tens AS (SELECT n = 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t (n)),
iTally AS (SELECT n = 0 FROM Tens a, Tens b, Tens c, Tens...
^{“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

August 12, 2013 at 4:20 am

#1640500
RE: Reason for dropping TEXT, IMAGE data types

T.Ashish (8/12/2013)
declare @image1image
declare @text1text
declare @ntext1ntext
declare @varchar2varchar
declare @nvarchar2nvarchar
declare @varbinary2varbinary
declare @varchar1varchar(max)
declare @nvarchar1nvarchar(max)
declare @varbinary1varbinary(max)
First three variable declarations are definitely going to produce an error.
Yes of course they will. But this won't:
declare @image1varbinary(max)
declare @text1varchar(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

August 12, 2013 at 4:00 am

#1640497
RE: Reason for dropping TEXT, IMAGE data types

T.Ashish (8/11/2013)
Text, Ntext, and Image data types are invalid for local variables.
Gosh, where did you read that?
^{“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

August 12, 2013 at 2:26 am

#1640475
RE: How to update Flag based on Maximum Amount

It might be easier for us to understand if you can describe how the result set from my query is different to what you want.
^{“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

August 12, 2013 at 2:07 am

#1640471
RE: Are the posted questions getting worse?

Jeff Moden (8/10/2013)
Brandie Tarvin (8/2/2013)
How many people (besides Jeff and myself) have written SQL Spackle articles?
How many SQL Spackle articles are there?
Yes, I have a reason for asking. But I...
^{“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

August 12, 2013 at 1:56 am

#1640467
RE: Are the posted questions getting worse?

Stefan Krzywicki (8/9/2013)
L' Eomot Inversé (8/9/2013)
ChrisM@Work (8/9/2013)
dwain.c (8/9/2013)
ChrisM@Work (8/9/2013)
Port Moresby - ranked 139th of 140 cities for liveability, whatever that means. I wonder which city came last? What's the grub...
^{“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

August 12, 2013 at 1:47 am

#1640465
RE: Are the posted questions getting worse?

L' Eomot Inversé (8/9/2013)
ChrisM@Work (8/9/2013)
dwain.c (8/9/2013)
ChrisM@Work (8/9/2013)
Port Moresby - ranked 139th of 140 cities for liveability, whatever that means. I wonder which city came last? What's the grub like?
Food's OK....
^{“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

August 12, 2013 at 1:46 am

#1640464
RE: How to update Flag based on Maximum Amount

I've no idea what you mean - "update the Amount_Flag for same" means do nothing in English. Please provide an example.
^{“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

August 12, 2013 at 1:04 am

#1640454

Viewing 15 posts - 4,171 through 4,185 (of 10,143 total)