Cross-post, answered here:

http://www.sqlservercentral.com/Forums/Topic615978-338-1.aspx?Update=1

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...

Well done, many thanks for the feedback. I guess since this is the root of the problem, the whole chain is fixed now?

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...

Sorry, no idea. This is the SQL Server 2005 (T-SQL) section of the forum.

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...

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...

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...

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...

Column 9 here:

0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBPGB00B1XZS820

Open the flat file using...

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 >...

What's the name of your PK column in Test_HistoricalData?

Had a part in this did Yoda? 😀

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)

Thanks for the feedback!

As a matter of interest, did you run both methods? Did you measure / notice a difference in performance?