ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,711 through 4,725 (of 10,143 total)

RE: Stuck with a getdate time range?

Can you work it out from this:
SELECT
d.Today,
StartTime = DATEADD(hour,8,d.Today),
EndTime = DATEADD(hour,10,d.Today)
FROM (SELECT Today = CAST(CAST(GETDATE() AS DATE) AS DATETIME)) d
^{“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

April 30, 2013 at 4:32 am

#1611163
RE: VIEW vs. DynamicSQL

Is column [LINE_CODE] correctly populated in all of the tables?
^{“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

April 30, 2013 at 4:28 am

#1611162
RE: Use of rownum function in a select

-- use proper join syntax
-- don't use double-quotes for identifiers unless absolutely necessary
-- write queries using TOP 1...ORDER BY and ROW_NUMBER() and choose quickest of the two
-- CROSS APPLY is...
^{“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

April 30, 2013 at 4:16 am

#1611157
RE: Really Challenging Query: Break out average value across months

-- Expanding the date range out by months rather than days might be 30x cheaper
;WITH OrderedData AS (
SELECT *,
rn = ROW_NUMBER() OVER(PARTITION BY AccountID ORDER BY DataDate DESC)
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

April 29, 2013 at 5:00 am

#1610741
RE: Error converting varchar to numeric only in where clause

Evil Kraig F (4/26/2013)
...Thanks for taking the time to show me in a slightly different way...
Welcome. It's one of those things we take for granted after a while, not really...
^{“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

April 29, 2013 at 1:10 am

#1610685
RE: How to merge two sql select statement results I tried it but it not get please see it in detailed explination with my query

UNION on it own dedupes the result set which is almost certainly not what you want.
Try combining the two queries into one so you're not reading tables twice, unnecessarily:
SELECT
TT.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

April 29, 2013 at 12:59 am

#1610681
RE: Inserts are slow

rcarrier (4/28/2013)
...
One source system transaction may need to have several inserts created in the destination table and its a complicated process to determine this.
...
"Complicated" is a relative word. If you're...
^{“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

April 29, 2013 at 12:42 am

#1610674
RE: Complex many to many relationships

Ganga-236835 (4/26/2013)
ChrisM@Work (4/26/2013)
Those are 'organisations'...
Well, not quite. There could be multiple regional offices for "Organizations", with each one having their own unique set of phone numbers. ...
G.
Those are 'rows' 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

April 26, 2013 at 8:36 am

#1610409
RE: Complex many to many relationships

Ganga-236835 (4/26/2013)
ChrisM@Work (4/26/2013)
Those are 'organisations'...
Well, not quite. There could be multiple regional offices for "Organizations", with each one having their own unique set of phone numbers. Again, for the purposes...
^{“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

April 26, 2013 at 8:25 am

#1610402
RE: Complex many to many relationships

Ganga-236835 (4/26/2013)
patrickmcginnis59 10839 (4/26/2013)
This is an interesting case, so I'm guessing that it really depends on how important the functionality of the foreign key constraint is for your needed functionality....
^{“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

April 26, 2013 at 8:09 am

#1610392
RE: Complex many to many relationships

Ganga-236835 (4/26/2013)
....
Adding these columns to each parent's object just does not make sense. There is no supporting case, at least in this system, where it would make sense to add...
^{“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

April 26, 2013 at 8:07 am

#1610391
RE: Complex many to many relationships

Lynn Pettis (4/26/2013)
If a phone number can only belong to one entity, why are you trying to make it a many to many relationship?
+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

April 26, 2013 at 8:06 am

#1610388
RE: Complex many to many relationships

Ganga-236835 (4/26/2013)
Although I agree in principle that most common scenario regarding phone numbers is that there is one per person, in the system that I am designing that is just...
^{“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

April 26, 2013 at 8:05 am

#1610387
RE: Complex many to many relationships

davidandrews13 (4/26/2013)
is it possible, or even considered OK, to put all your different Members tables (Clients, Staff, Managers) into one table called 'Clients', and have a Foreign Key to 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

April 26, 2013 at 7:51 am

#1610379
RE: Fastforward cursor and updlock

Oh right - ok. In that case, I'll try to track down an excellent article which provides good evidence NOT to use built-in cursor updates but to do it manually...
^{“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

April 26, 2013 at 7:36 am

#1610368

Viewing 15 posts - 4,711 through 4,725 (of 10,143 total)