ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,446 through 5,460 (of 10,143 total)

RE: Need to fill the Gaps with previous value

dwain.c (10/7/2012)
I find it extremely annoying that this does not work:
DECLARE @Weight FLOAT = 0
,@STDate DATETIME
,@EDate DATETIME
SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE
;WITH...
^{“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

October 8, 2012 at 2:35 am

#1546602
RE: Need to fill the Gaps with previous value

dwain.c (10/8/2012)
<<snip>>
Nagaram's is the fastest so far - it's a good 20% faster than mine. I'm having a look to find out why - probably the tally-table generation. Yours does...
^{“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

October 8, 2012 at 2:31 am

#1546599
RE: Need to fill the Gaps with previous value

dwain.c (10/8/2012)
ChrisM@Work (10/8/2012)
Hi Dwain, I came up with this last night but ran out of time to post;
;WITH WholeRange AS (
SELECT datecol = DATEADD(day,n,d.Startdate)
FROM (SELECT Startdate = MIN(datecol), daysSpan =...
^{“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

October 8, 2012 at 1:29 am

#1546580
RE: Need to fill the Gaps with previous value

Hi Dwain, I came up with this last night but ran out of time to post;
;WITH WholeRange AS (
SELECT datecol = DATEADD(day,n,d.Startdate)
FROM (SELECT Startdate = MIN(datecol), daysSpan = 1+DATEDIFF(day,MIN(datecol), MAX(datecol))...
^{“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

October 8, 2012 at 12:41 am

#1546569
RE: SUBSTRING And CHARINDEX to find start/end of string

Eliza (10/7/2012)
Hi All,
Thanks for some of the solutions, I've tried a few.
SELECT SUBSTRING(@var,28+CHARINDEX ('CustomerFornm: Changed from ',@var),CHARINDEX('CustomerDOB',@var)- CHARINDEX ('CustomerFornm: Changed from ',@var)-28) AS 'Is this really what you
want?'
Doesn't work...
^{“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

October 7, 2012 at 1:31 pm

#1546520
RE: Nested queries problem in SQL2008R2

nico 71726 (10/7/2012)
whist experimenting with the query noticed that by removing the TOP1 the query suddenly started running in parallel mode which was A LOT faster. Still needed the TOP1...
^{“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

October 7, 2012 at 12:06 pm

#1546505
RE: A quick query puzzle:

S_Kumar_S (10/5/2012)
This is quite impressive but it doesn't work for some scenarios. e.g. this one :
insert into t1
select 'AAA22222222222222BB5BB 33CCCCCCCCC342234234'
EDIT: I meant it for Chris response:
SELECT
t.name, x.New_Name
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

October 5, 2012 at 8:27 am

#1546122
RE: Merging rows

Sean Lange (10/5/2012)
Still nothing to test with but I think this might be close to what you want...
select A, b, MAX(C), MAX(D), MAX(E), MAX(F), g
from
(
SELECT distinct
BASE1.AssetID AS a
,BASE1.Name As b
,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

October 5, 2012 at 7:36 am

#1546075
RE: Merging rows

prady_1988 (10/5/2012)
Its a pretty big query but here it is:
<<snip>>
The FROM list and WHERE clause of both of the queries is identical:
FROM BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)
ON...
^{“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

October 5, 2012 at 7:24 am

#1546063
RE: Merging rows

prady_1988 (10/5/2012)
Sorry, I may have mislead you.
The values under Relationship is taken from 2 tables.
Table1 -> AssetID, AssetName
Table2 -> Relationship1
Table3 -> Relationship2
Table4 -> Company
Hence for each record I am getting...
^{“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

October 5, 2012 at 6:44 am

#1546036
RE: A quick query puzzle:

S_Kumar_S (10/5/2012)
Hi Chris.
the numbers are not consecutive as you have assumed. And they may be of length greater than 6. I think it is not so straight as it looks.
Ah,...
^{“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

October 5, 2012 at 6:38 am

#1546031
RE: How to use GETDATE() function in sql server to select a column value from table whose date is only 1 year back from today.

kapil190588 (10/5/2012)
Hi maida,
here is the query wch list out all the dates 1 year less than the current date..
Select LTRating ,RHDate from Accounts and If LTRating ='D' then RHDate <...
^{“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

October 5, 2012 at 4:58 am

#1545988
RE: execute a storedprocedure where ever he wants to execute in any database it it possible?

naga.rohitkumar (10/5/2012)
thanks for reply
actually the applications user want to take the data directly from the database instead from application
to which he want to create a stored procedure...
^{“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

October 5, 2012 at 4:42 am

#1545983
RE: A quick query puzzle:

S_Kumar_S (10/5/2012)
This is really good but problem is that my table has multiple rows and this is a table function. So it has to be a scalar function for me...
^{“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

October 5, 2012 at 4:24 am

#1545980
RE: Help on Update

jdelorino (10/5/2012)
Thank you for your reply,your help is greatly appreciated, more power.
Can you please post the solution you are using? It provides feedback to those who have taken time 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

October 5, 2012 at 4:16 am

#1545978

Viewing 15 posts - 5,446 through 5,460 (of 10,143 total)