ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,301 through 9,315 (of 10,143 total)

RE: SQL help with total percentage

Hi Ishaan
Can you please provide some sample data, and an expected result set, as in the link below?
Cheers
ChrisM
^{“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

December 2, 2008 at 11:11 am

#906402
RE: SQL Help.

Hi 4D
This should start you off.
[font="Courier New"]DROP TABLE #Data
CREATE TABLE #Data ([id] INT, cust_id INT, sales_id CHAR(3), start_date DATETIME, end_date DATETIME)
INSERT INTO #Data ([id], cust_id, sales_id, start_date, end_date)
SELECT 1, 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

December 2, 2008 at 10:58 am

#906387
RE: Are the posted questions getting worse?

Grant Fritchey (11/30/2008)
Well, it was a thrilling moment, I don't mind saying. I'll be picking up one of those integrated face sheild/helmet/ear protection things soon (read ASAP). First time 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

December 2, 2008 at 10:21 am

#906356
RE: Getting lost in Group, Top for Select

Corobori (12/1/2008)
Chris Morris (12/1/2008)
Hi Corobori
This will give you your expected results
ChrisM
I think that ROW_NUMBER is a SQL Server 2005 and in my case I am using a SQL Server 2000...
^{“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

December 2, 2008 at 10:01 am

#906340
RE: Which is better

Thanks for the explanation, Gail.
I use 1 just to make it very clear to me and to anyone reading that there's no column access and the column (if any) mentioned...
^{“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

December 2, 2008 at 9:58 am

#906334
RE: Top n of each value ?

Chris Morris (12/1/2008)
Madhivanan (12/1/2008)
It must be faster. If not compare the execution paln by applying these
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
Only the third method would work - and it's exactly the same as the OP's...
^{“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

December 2, 2008 at 9:44 am

#906314
RE: Query to Calculate percentages

A very similar question was asked and answered here...http://www.sqlservercentral.com/Forums/Topic610366-8-1.aspx#bm610396
Cheers
chrisM
^{“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

December 2, 2008 at 6:45 am

#906191
RE: Table Sub total problem

Here's a long shot if you'd like to try it:
SUM(IIF(Fields!totalmoney.Value>105,Fields!totalmoney.Value-105,0))*0.128
Cheers
ChrisM
^{“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

December 2, 2008 at 5:18 am

#906151
RE: Proc is giving poor performance

GilaMonster (12/1/2008)
KPR (12/1/2008)
sorry to reduce it to one sec.
That may not be possible. You're doing a lot of work in there.
What are these?
dbo.fn_ProfileLegalVechile(@ProfCode,@Branch)
dbo.fn_ProfileWareHouse(@ProfCode,@WHCode)
dbo.fn_ProfileCurrency(@ProfCode,@CCYCode)
These functions all appear to restrict rows...
^{“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

December 2, 2008 at 4:36 am

#906129
RE: Proc is giving poor performance

Hi KPR
Examining the function which returns a warehouse code, dbo.fn_ProfileWareHouse:
the WHERE clause reads AND B.WHCode = M.WHCode where M is the alias for the table returned by the function.
If @WHCode...
^{“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

December 2, 2008 at 4:07 am

#906123
RE: Getting lost in Group, Top for Select

Hi Corobori
This will give you yoyur expected results without randomisation - but I think you should find that part relatively straightforward:
WITH MyCTE AS (SELECT ROW_NUMBER() OVER (PARTITION BY Pr_RealEstateAgent_Id ORDER...
^{“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

December 1, 2008 at 10:20 am

#905754
RE: Which is better

Gail, did you notice in your testing if there was significant benefit in using
Where not exists (Select 1
instead of Where not exists (Select *
in Query 1? I'm...
^{“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

December 1, 2008 at 9:43 am

#905727
RE: Top n of each value ?

Grant Fritchey (12/1/2008)
Yeah, that's basically it. In most of our instances, we're looking for the latest version of a record, so we're going for one value in each set, not...
^{“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

December 1, 2008 at 8:17 am

#905661
RE: better way to write this update

Not sure if you can get all three updates into one statement, but you can certainly speed things up:
[font="Courier New"]UPDATE TABLE1
   SET    CHK_DATE = 1
   WHERE  TRXID IN (SELECT A.TRXID
                     FROM   TABLE1...
^{“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

December 1, 2008 at 8:06 am

#905649
RE: Top n of each value ?

Grant Fritchey (12/1/2008)
I'd suggest using the TOP and an ORDER BY within the sub-query instead. Assuming there is an index for the ORDER BY clause to use, I think you'll...
^{“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

December 1, 2008 at 7:45 am

#905633

Viewing 15 posts - 9,301 through 9,315 (of 10,143 total)