ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,226 through 9,240 (of 10,143 total)

RE: dynamic column generation

ani112001 (12/9/2008)
Hi,
I have a query like given…Is there any way to give the RoomName dynamically. I have several roomnames in a table. So its not possible to write case for...
^{“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 9, 2008 at 2:37 am

#909508
RE: Column delimiter - Merging Columns

Well done, many thanks for the feedback. I guess since this is the root of the problem, the whole chain is fixed 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

December 9, 2008 at 2:15 am

#909502
RE: SQL count across two tables in a one to many relationship

Either of these would do it. My preference is for the second style because it's easier to test.
UPDATE s SET ColleagueLimit = (SELECT Count(c.colleagueID) AS numOfColleagues
FROM SEND_COLLEAGUE_CONTACTS c
WHERE...
^{“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 9, 2008 at 12:22 am

#909487
RE: DAteDifference problem in SQL

Sirish (12/8/2008)
How to rewrite this block in Oracle?.
Sorry, no idea. This is the SQL Server 2005 (T-SQL) section of the forum.
^{“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 8, 2008 at 1:20 pm

#909334
RE: Column delimiter - Merging Columns

LOOKUP_BI (12/8/2008)
Since the column delimeter is "," you should have total 10 columns coming in from your flat file and mapping to a table with 10 columns as well.
You should...
^{“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 8, 2008 at 10:23 am

#909258
RE: Moving Average Over a Data Table Requiring Group

You're welcome, thanks for the feedback. As this is an unusual requirement - well I think it is - would you mind posting your final solution here perhaps with an...
^{“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 8, 2008 at 10:09 am

#909253
RE: Column delimiter - Merging Columns

A Little Help Please (12/8/2008)
Answer to the above ttow questions:
This is exactually how it is in a notepad file.
0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBP,GB00B1XZS820
No delimiters i.e. ""
Just commas between each column
When the file 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

December 8, 2008 at 9:42 am

#909230
RE: Moving Average Over a Data Table Requiring Group

Or better still:
UPDATE a SET Avg1WkVol = (SELECT AVG(Volume) from Test_HistoricalData b where b.Symbol = a.Symbol AND b.MarketDate a.MarketDate-8)
FROM Test_HistoricalData a
Although the previous version is much easier 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 8, 2008 at 9:39 am

#909227
RE: Column delimiter - Merging Columns

A Little Help Please (12/8/2008)
Attched is a screen shot of the FlatFile Connection Manager.
Shows a preview of the column which is incorrect.
Column 8
Column 9 here:
0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBPGB00B1XZS820
Open the flat file using...
^{“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 8, 2008 at 9:24 am

#909216
RE: Moving Average Over a Data Table Requiring Group

Try this:
UPDATE h SET Avg1WkVol = d.Avg1WkVol
FROM Test_HistoricalData h
INNER JOIN (SELECT a.RowID, Avg1WkVol = (SELECT AVG(Volume)
FROM Test_HistoricalData b
WHERE b.Symbol = a.Symbol
AND b.MarketDate < a.MarketDate
AND b.MarketDate >...
^{“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 8, 2008 at 9:20 am

#909211
RE: Moving Average Over a Data Table Requiring Group

What's the name of your PK column in Test_HistoricalData?
^{“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 8, 2008 at 9:05 am

#909197
RE: Should be simple and quick but cannot find answer

Had a part in this did Yoda? 😀
^{“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 8, 2008 at 9:01 am

#909193
RE: DAteDifference problem in SQL

DECLARE @OriginalDate DATETIME
SET @OriginalDate = dateadd(dd,datediff(dd, 0, getdate()), 0)
IF EXISTS (SELECT 1 FROM dbo.HolidayTable WHERE HolidayDate = @OriginalDate)
SET @OriginalDate = DATEADD(dd, -1, @OriginalDate)
^{“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 8, 2008 at 8:33 am

#909172
RE: getting next and previous id

Thanks for the feedback!
As a matter of interest, did you run both methods? Did you measure / notice a difference in performance?
^{“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 8, 2008 at 8:21 am

#909165
RE: getting next and previous id

Select A.pic_id, A.Album_Type_Id, (Select MAX(B.pic_id) From Album B Where B.pic_id < A.pic_id) as Next_Id
From ALbum A
Where A.pic_id = @pic_id
And A.Album_Type_Id = @Album_Type_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

December 8, 2008 at 8:02 am

#909153

Viewing 15 posts - 9,226 through 9,240 (of 10,143 total)