ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,106 through 3,120 (of 10,143 total)

RE: Set datime and convert it

CONVERT uses style codes to control output and input formatting:
declare @ToDate datetime, @FromDate datetime
SELECT -- UK format
@FromDate = CONVERT(DATETIME,'21/06/2014',103),
@ToDate = CONVERT(DATETIME,'21/07/2014',103)
select @FromDate,...
^{“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

July 21, 2014 at 6:20 am

#1730978
RE: SQL Help

wBob (7/21/2014)
I would approach that using TOP and CROSS APPLY, eg:
SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c 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

July 21, 2014 at 6:08 am

#1730971
RE: Suggestions on how to improve this View that is using correlated subqueries

T-SQL beginnger (7/18/2014)
Any help for the 2nd part guys ?
I agree with Luis, it's very difficult to work with filters as complex as this without sample data. However, here's 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

July 21, 2014 at 5:29 am

#1730959
RE: Very Very Complex Query based on Dates

Here's the final solution:
;WITH
SeqID1 AS (
SELECT x.ID, x.RowID, x.Value, MAXReadingdate = x.Readingdate
FROM (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Readingdate DESC)
FROM #tempreadings t
) x
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

July 21, 2014 at 4:52 am

#1730953
RE: Very Very Complex Query based on Dates

ramrajan (7/21/2014)
Yes that is Really Working.
But i need to do additional join with deliveries to get few more records as mentioned in my post.
Yes I know. One step at 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

July 21, 2014 at 3:02 am

#1730934
RE: Very Very Complex Query based on Dates

ramrajan (7/21/2014)
As i mentioned in my post we also need to join #temp
--- We Need to join The above Results Set 3 With #tempdeliveries Based ON ID and We need...
^{“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

July 21, 2014 at 2:49 am

#1730931
RE: Very Very Complex Query based on Dates

"The most recent reading which is more than 24 hours before the max reading date"?
Like this:
;WITH OrderedData AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Readingdate DESC)...
^{“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

July 21, 2014 at 2:30 am

#1730928
RE: Very Very Complex Query based on Dates

ramrajan (7/21/2014)
Please read it as "ReadingDate IS 24 Hours Less Than the max Reading Date "
Appreciate your response
To help resolve this, I've rejigged the sample data to add a row...
^{“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

July 21, 2014 at 2:17 am

#1730924
RE: Very Very Complex Query based on Dates

ramrajan (7/21/2014)
.
.
.
-- Result 2
-- In The same Query I want the ReadingDate For Each ID And the ReadingDate IS 24 Less Than the max Reading Date of Each ID ....
^{“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

July 21, 2014 at 2:08 am

#1730919
RE: Very Very Complex Query based on Dates

See also this thread.
^{“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

July 21, 2014 at 1:53 am

#1730915
RE: Non Temp Temp Tables

Sreepathi1987 (7/21/2014)
Easy One. 🙂
But i think it is rather unusual, tempdb is completely wiped out whenever the SQL Server service restarts. Yes or No?
Yes, which makes tempdb useful 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

July 21, 2014 at 1:44 am

#1730911
RE: Ranking rows on basis of SUM of records

Not really. NTILE(n) splits a set into n partitions.
^{“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

July 21, 2014 at 1:41 am

#1730909
RE: Today's Random Word!

Ed Wagner (7/18/2014)
SQLRNNR (7/18/2014)
manga
Carta
Runnymede
^{“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

July 18, 2014 at 9:49 am

#1730679
RE: calculate the sum of dates minus repetitive dates

pmadhavapeddi22 (7/18/2014)
I am sorry, Chris for not mentioning your name. Thanks for your support
Happy Friday 🙂
^{“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

July 18, 2014 at 9:10 am

#1730664
RE: Partition and order the records

ramrajan (7/18/2014)
If we have multiple readings for a ID we are picking the latest record date as current reading and then we need to show the prevoius reading data which...
^{“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

July 18, 2014 at 8:56 am

#1730655

Viewing 15 posts - 3,106 through 3,120 (of 10,143 total)