ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,016 through 3,030 (of 10,143 total)

RE: Parameter Sniffing

Here are several suggestions -
--SELECT @From = COALESCE(@From, Min(QuotationDate)),
-- @To = COALESCE(@To , Max(QuotationDate))
--FROM dbo.Quotation;
--DECLARE @F_Loc DateTime = @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

August 22, 2014 at 2:29 am

#1740101
RE: LEFT JOIN without predicate , still works but what is that?

Here's something I wrote some time ago (inspired by something Jack Corbett wrote years ago) which explains why you have two joins followed by two ON clauses:
The order of 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

August 22, 2014 at 1:52 am

#1740095
RE: Scalar UDF versus Inline UDF

ScottPletcher (8/21/2014)
Tracing scalar UDFs adds enormous time to its execution. That is, just trying to compute the time vastly increases the time. Thus, scalar UDFs generally are performing...
^{“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

August 22, 2014 at 1:41 am

#1740094
RE: Arithmetic overflow error converting expression to data type datetime.

-------------------------------------------------------------------------
-- Your first query has lots of scope for simplification:
-------------------------------------------------------------------------
SELECT 0 AS SIRA
,TBLCARI.IND
,(
SELECT SUM(BORC - ALACAK)
FROM F0102D0001TBLCARIHAREKETLERI AS CARHAR
WHERE CARHAR.FIRMANO = TBLCARI.IND
) AS BAKIYE
,TCG.ISLEMIND
,TBLCARI.SOYADI
,TBLCARI.ADI
,TBLCARI.SOYADI + ' ' + TBLCARI.ADI 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

August 21, 2014 at 9:34 am

#1739955
RE: Bronze Age Development

Speaking of bronze-age development, this is one of the very few threads I can post to. Attempting to reply to other threads (which look kinda "flat") raises an error. 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

August 21, 2014 at 6:44 am

#1739863
RE: Query Help

jrusso (8/20/2014)
I figured out a way to do it. Thank you
Does it look something like this?
SELECT
[AmountGiven] = ISNULL(SUM(a.AmountGiven), 0),
a.YearToDate,
a.EndDate
FROM ( -- a
SELECT
[AmountGiven] = MAX(x.[AmountGiven]),
[EndDate] = MAX(y.[EndDate]),
YearToDate...
^{“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

August 21, 2014 at 2:21 am

#1739796
RE: Help with query !!!

You're welcome.
Try this:
DROP TABLE #TEMP
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int,CreatedDate date)
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100,'2014-01-12') --Date is in YYYY/MM/DD format
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',50,'2014-01-13')
INSERT INTO #TEMP 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

August 20, 2014 at 6:35 am

#1739545
RE: Display ALL rows even if no data exists

Paul, if you wouldn't mind running through your sample script and correcting the errors which Steve kindly pointed out, I'll take a look later today.
^{“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

August 20, 2014 at 1:45 am

#1739485
RE: Are the posted questions getting worse?

TomThomson (8/19/2014)
...that my wife hid (or, in wife-ese, "tidied up") ...
Gaaah! Don't get me started. Every time I start work on a job in the extension, all my tools are...
^{“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

August 20, 2014 at 1:43 am

#1739483
RE: Display ALL rows even if no data exists

J Livingston SQL (8/18/2014)
pwalter83 (8/18/2014)
sgmunson (8/18/2014)
Okay, so now the excuse for not explaining EVERYTHING is that some of the columns aren't in use??? Really? Sorry, but...
^{“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

August 18, 2014 at 10:28 am

#1738950
RE: ROW_NUMBER in a WHILE loop

It looks like you are trying to run through sys.tables or whatever and do something to each table encountered, although as Gail points out, it's bl00dy hard to tell 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

August 18, 2014 at 6:32 am

#1738831
RE: Display ALL rows even if no data exists

I know what you are attempting to do here and it wouldn't take long with access to your tables, however scripts for all of the tables along with sample data...
^{“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

August 18, 2014 at 6:24 am

#1738822
RE: Display ALL rows even if no data exists

Hi Paul
Can you provide the following please?
CREATE TABLE for MG_COUNTRY with INSERTs to populate a few sample rows
Do you wish to include every row from this table in the report?...
^{“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

August 18, 2014 at 3:07 am

#1738748
RE: Need help with query anyone ???

SELECT
[Id Number],
[Family Name],
[Given Name],
Title,
[Timesheet Date] = MAX([Timesheet Date]),
[Start Date],
[End Date]
FROM YourVacationTable
WHERE [Start Date] <= @EndDate
AND [End Date] >= @StartDate
GROUP BY [Id Number],[Family Name],[Given Name],Title,[Start Date],[End Date]
^{“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

August 15, 2014 at 7:03 am

#1738385
RE: Assign Row number to the result set without using Row_Number

Koen Verbeeck (8/15/2014)
GilaMonster (8/15/2014)
Eirikur Eiriksson (8/14/2014)
You can try out these methods as previously suggested
However OP said
I really don't like this temp table creation
So, no row number, no temp 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

August 15, 2014 at 4:29 am

#1738331

Viewing 15 posts - 3,016 through 3,030 (of 10,143 total)