ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,551 through 5,565 (of 10,143 total)

RE: Query to find out latest data of each student

;WITH CTE AS (
SELECT
Name,
[Month],
[Year],
Mark,
rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY <<Something>>)
FROM tblStudent
)
SELECT
Name,
[Month],
[Year],
Mark
FROM CTE
WHERE rn = 1
^{“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

September 27, 2012 at 5:38 am

#1542802
RE: Remove Decimals Without Rounding

deyvisonsouto (9/27/2012)
it's just use convert(int, <<value>>)
eq:
select convert(int,2.5)
result:
2
Nope! Miles off. Did you read the spec?
^{“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

September 27, 2012 at 5:23 am

#1542797
RE: Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong

Evil Kraig F (9/26/2012)
...OPENQUERY is another way I'd forgotten to enforce this, though it's not one I'd typically prefer to use... but I forget why I came to that decision.....
Craig,...
^{“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

September 27, 2012 at 1:47 am

#1542731
RE: Case in where clause doesn't get validated

halifaxdal (9/26/2012)
My bad! I hit another sp, it works, amazingly like what I expect. Thanks lots.
Any time. There's a great set of articles by Paul White covering APPLY, links in...
^{“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

September 26, 2012 at 9:50 am

#1542511
RE: Case in where clause doesn't get validated

Post the whole query, mate...
^{“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

September 26, 2012 at 9:44 am

#1542506
RE: Case in where clause doesn't get validated

(CROSS) APPLY is ideal for this:
CROSS APPLY (
SELECT DueStatus =
case
When NextReviewDate < getDate() then 'Over Due'
When NextReviewDate >= getDate() And NextReviewDate < getDate() + 30 then 'Due In...
^{“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

September 26, 2012 at 9:23 am

#1542493
RE: Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong

antonio.estima 4150 (9/26/2012)
...In SQL Server 2K or 2K5 the WHERE clause where SUBSTRING(nomeint,1,1) = 'P' is run first in the subquery and then it does the join. However, in SQL...
^{“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

September 26, 2012 at 8:26 am

#1542450
RE: Date conversion issue

tim_harkin (9/26/2012)
Not the most efficient solution....
DECLARE @currentTime DATETIME
SET @currentTime = GETDATE()
SELECT CAST(DATEPART(year,@currentTime) AS CHAR(4))
+ CASE WHEN (DATEPART(month,@currentTime)) > 10 then (CAST(DATEPART(month,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(month,@currentTime) AS CHAR(1))...
^{“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

September 26, 2012 at 7:32 am

#1542395
RE: Query result in SQL Server 2000 return correct and SQL Server 2008 R2 SP2 return wrong

antonio.estima 4150 (9/25/2012)
... Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" ...
This doesn't make sense.
SUBSTRING (nomeint,...
^{“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

September 26, 2012 at 7:20 am

#1542385
RE: What is the difference?

Charmer (9/26/2012)
Charmer (9/26/2012)
Hi Friends,
I have a doubt regarding joining two tables, so i came here 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

September 26, 2012 at 7:05 am

#1542370
RE: FK referencing multiple tables

PiMané (9/20/2012)
Hi,
Is it possible to have a column on table A that references table B or table C depending on another column on table A?
For example:
EntityAddresses (EntityAddressId, EntityType, EntityID, Address)
Customers...
^{“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

September 26, 2012 at 4:51 am

#1542305
RE: Date conversion issue

saidapurs (9/26/2012)
Hi All,
Using below conversion i am getting date only like 20120926 but my requirement is 201209261015 like hour & time
convert(nvarchar(50),(GetDate()),112))
Please 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

September 26, 2012 at 3:39 am

#1542276
RE: Consolidating records - TSQL problem

Abu Dina (9/26/2012)
Your solution works great but unfortunately the rCTE isn't scaling well enough. With 150000 rows it takes 7 seconds to process. With 160000 records it takes 17 seconds....
^{“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

September 26, 2012 at 3:06 am

#1542261
RE: Consolidating records - TSQL problem

Lynn Pettis (9/25/2012)
Here is my code, reformatted the way I like to it.
with rCTE as (
select
t1.retained,
t1.dropped,
t1.retained as TopLevel
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

September 26, 2012 at 2:21 am

#1542254
RE: Understanding the difference between Join and Where filters - SQL 2008R2

Steven Willis (9/25/2012)
Here's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where 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

September 26, 2012 at 1:43 am

#1542244

Viewing 15 posts - 5,551 through 5,565 (of 10,143 total)