ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,101 through 5,115 (of 10,143 total)

RE: Grouping records by time interval

b ghanekar (3/6/2013)
I think the window functions should be of help here.
Please repost the data I am not able to get the data you have posted.
The fourth post on this...
^{“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

March 6, 2013 at 1:36 am

#1593816
RE: Grouping records by time interval

geert.de.vylder (3/5/2013)
... if the rows are null they are repeated several times....
There will be many many rows in the matrix table which don't have a matching ticket, hence null values...
^{“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

March 6, 2013 at 1:32 am

#1593814
RE: SELECT list block with CASE?

jshahan (3/5/2013)
DECLARE @X TINYINT = 0
;WITH SampleTable AS (
SELECT SOMECOLUMNS = 'SOMECOLUMNS', COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3',
COLUMN4 = 'COLUMN4', COLUMN5 = 'COLUMN5', COLUMN6 = 'COLUMN6'
)
SELECT SOMECOLUMNS,...
^{“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

March 6, 2013 at 1:04 am

#1593805
RE: Conditional join update

Deepthy (3/5/2013)
Thanks for the update.
But joins in the 3 update statements are different.
Not sure of a way to bind them into one statement.
Deepthy
You got me.
Try this:
UPDATE dbo.DatasiteConfigBMPANConfiguration SET
EnergisationStatus =...
^{“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

March 5, 2013 at 8:55 am

#1593516
RE: Table Value Function VS ORs

PiMané (3/5/2013)
Is SQL Sentry Plan more accurate than the Execution Plan from SSMS?!
I ask this cause in SSMS the TVF is better but in Sentry Plan iTVF is much better...
Thanks,
Pedro
You...
^{“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

March 5, 2013 at 8:21 am

#1593506
RE: Conditional join update

UPDATE d SET
EnergisationStatus = CASE
WHEN c.DataSiteConfigATableName = 'DataSiteConfigAEnergisationStatus' THEN b.EnergisationStatus
ELSE EnergisationStatus END,
MeasurementClassRef = CASE
WHEN c.DataSiteConfigATableName = 'DataSiteConfigAMeasurementClass' THEN b.measurementClassRef
ELSE MeasurementClassRef END,
ProfileClass = CASE
WHEN c.DataSiteConfigATableName = 'DataSiteConfigAProfileClass' THEN b.ProfileClass
ELSE...
^{“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

March 5, 2013 at 8:19 am

#1593503
RE: Table Value Function VS ORs

PiMané (3/5/2013)
ChrisM@Work (3/5/2013)
You should avoid this syntax:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00
FROM dbo.GetSubContasTVF('111') t
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
AND...
^{“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

March 5, 2013 at 8:00 am

#1593493
RE: Table Value Function VS ORs

You should avoid this syntax:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00
FROM dbo.GetSubContasTVF('111') t
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
AND Conta =...
^{“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

March 5, 2013 at 7:48 am

#1593483
RE: Table Value Function VS ORs

You can't compare queries like this - you have to time them using a realistically-sized data set. "% of the batch" is 100% unreliable.
If a function contains a BEGIN/END block...
^{“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

March 5, 2013 at 7:09 am

#1593464
RE: MERGE problem -> when I need to reenter an entry which is already in the history

chingarova (3/5/2013)
ok, lets say that the script look like this:
It's an improvement in that you now have a "key" (not ideal - not everybody has an email address, many folks...
^{“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

March 5, 2013 at 6:18 am

#1593437
RE: Table Value Function VS ORs

Try using a proper iTVF, the performance will be far better:
ALTER FUNCTION [dbo].[GetSubContasTVF](@Conta NVARCHAR(20))
RETURNS TABLE
AS
RETURN SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) -...
^{“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

March 5, 2013 at 5:59 am

#1593433
RE: Query to get rows if value contains result of another query

winmansoft (3/5/2013)
ChrisM@Work (3/5/2013)
This is the simplest way to write it. If it generates dupes, then we'll change one side to a derived table. Can you use something a little more...
^{“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

March 5, 2013 at 5:11 am

#1593419
RE: Extract only the last three days

cms9651 (3/5/2013)
Extract only the last three days
Anything from saturday+sunday+monday, or anything since 72 hours ago from now?
^{“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

March 5, 2013 at 4:49 am

#1593413
RE: Query to get rows if value contains result of another query

Gazareth (3/5/2013)
You need to implement Full-Text Indexing before freetext or contains will work.
I think Chris's solution is the way to go unless you find it's not what you need or...
^{“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

March 5, 2013 at 4:14 am

#1593395
RE: How to uncommit the transaction and execute the next statement in the cursor?

prakashr.r7 (3/5/2013)
You can solve your immeditate problem by using the solution I proposed above - generate a table of all 36 values (or whatever) randomised relative to an incrementing numeric...
^{“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

March 5, 2013 at 4:07 am

#1593387

Viewing 15 posts - 5,101 through 5,115 (of 10,143 total)