ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,166 through 9,180 (of 10,143 total)

RE: average maximum earner?

Sarfaraj Ahmed (12/11/2008)
Create Table Temp(
nameX VARCHAR(10),
moneyX int
);
INSERT INTO temp VALUES('a',10);
INSERT INTO temp VALUES('a',5);
INSERT INTO temp VALUES('a',15);
INSERT INTO temp VALUES('b',5);
INSERT INTO temp VALUES('b',20);
My question is:
How can I calculate the average maximum...
^{“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 11, 2008 at 7:05 am

#911045
RE: Are the posted questions getting worse?

Think I'll walk home across the fields.
^{“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 11, 2008 at 6:31 am

#911021
RE: Multi-Value Parameter

rcr69er (12/11/2008)
Hey
Thanks for that!
How would you do it the first way?
Sorry I'm quite new to SSRS!
Thanks
No problem, here ya go...
[font="Courier New"]DECLARE @ReasonCode VARCHAR(100)
SET @ReasonCode = '1,2,4,8,16'
DROP TABLE #Reasons
CREATE TABLE #Reasons...
^{“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 11, 2008 at 6:26 am

#911015
RE: Need to copy data from table to table with update statement

Jeff Moden (12/11/2008)
Heh... I think it's a bit odd that anyone would write code to make commenting it out easy when they refuse to comment code to make it easy...
^{“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 11, 2008 at 5:44 am

#910984
RE: Multi-Value Parameter

The two obvious choices are:
Resolve the values in @ReasonCode into a table or table variable, or convert the whole lot into dynamic sql.
There are some nifty methods of doing the...
^{“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 11, 2008 at 5:34 am

#910978
RE: Linked Server openquery error

sujiakm (12/11/2008)
select * from OPENQUERY([system2-PC\sqlserver2005],'select getdate()')
&
select * from OPENQUERY([system2-PC\sqlserver2005],'select * from sysobjects')
Both Works fine..
I have create the linked server on the default instance as [system2-PC\sqlserver2005]
and I want to run 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

December 11, 2008 at 4:53 am

#910963
RE: Linked Server openquery error

Start simple...does this work?
select * from OPENQUERY([system2-PC\sqlserver2005],'select getdate()')
Remember, OPENQUERY requires that the linked server is already set up, can you confirm that it is by querying against it directly?
^{“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 11, 2008 at 4:08 am

#910938
RE: ABS Function and Datetime

Reckon this should do it:
[font="Courier New"]--Create table with currency rates:
DROP TABLE #tblRates
CREATE TABLE #tblRates(
        [currency_id] [int] IDENTITY(1,1) NOT NULL,
        [currency_type] [varchar](10) NULL,
        [currency_rate] [decimal](18, 6) NULL,
        [rate_date] [datetime] NULL
)
INSERT INTO #tblRates
SELECT 'GBP',...
^{“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 11, 2008 at 3:55 am

#910930
RE: ABS Function and Datetime

So, you want to get the closest date to your reference date @Date_Reported from table A - then get the closest match to the date found in table A, 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 11, 2008 at 3:04 am

#910910
RE: ABS Function and Datetime

Try
ORDER BY ABS(DATEDIFF(dd, rate_date, @rate_date))
If there are several rows with the same rate_date then you might need the result from DATEDIFF in hours or even minutes.
^{“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 11, 2008 at 2:49 am

#910900
RE: Are the posted questions getting worse?

Lynn Pettis (12/10/2008)
Instictive is the key. I was heavy into Shoto Kan Karate many years ago, and I know that is what saved my life one day. I...
^{“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 10, 2008 at 3:20 pm

#910708
RE: Min Max Quartile

Only one problem there Kelvin - you've restricted it to a single ProductID.
^{“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 10, 2008 at 10:20 am

#910530
RE: Unit Availibility Query

Hi Mark
This requirement isn't unusual, and won't take long to fix. We do need a level playing field though, which you, and any contributors to this thread, can work 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

December 10, 2008 at 10:09 am

#910519
RE: Min Max Quartile

Garadin (12/9/2008)
Garadin (12/9/2008)
Chris,
Although it does seem like it might be more efficient due to the derived tables, the execution plans are virtually identical (once I add the aliases to your...
^{“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 10, 2008 at 10:04 am

#910513
RE: Are the posted questions getting worse?

It looks so risky, rolling over forwards surely makes you feel vulnerable to neck injury? I wouldn't want to try it sober!
What kind of shooting were you doing in that...
^{“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 10, 2008 at 9:21 am

#910471

Viewing 15 posts - 9,166 through 9,180 (of 10,143 total)