TheSQLGuru (5/14/2010)

---

Unfortunately I don't have the time to work this today, but shouldn't this be doable with simple CASE statements in a single pass? Hopefully someone else can work...

If you define your sour columns as character you might be able to use the following:

SELECT CAST('2010'+'-'+'1'+'-'+'15' AS DATETIME)

The way you've arranged your tables you probably need a RIGHT OUTER JOIN to get all values from Staff.

No, since the column definition is missing. There are no ready to use sample data either...

Please...

I would use ROW_NUMBER() OVER(PARTITION BY names, address, [basic contact info] ORDER BY hopefully_existing_rowid) as RowNmbr to identify the separate rows.

The ROW_NUMBER would be used within a CTE in order...

Would the following help?

I'm using OVER (PARTITION BY) to get the total per idName.

Side note: Since you already using dynamic SQL you might want to have a look at the...

Tara,

did you add any indexes after populating #EmployeeDur and Empgig1?

If not, I guess the following indexes might help:

Empgig1: EmpID,InDt include(EmpCode)

#EmployeeDur: EmpID,StDate,EnDate

Side note: Are you sure you need the DISTINCT clause...

Excellent job, Grant!

The only question that remains open is: Where to draw the line?

But I guess it's almost impossible to answer...

Interview questions, test/homework, almost complete projects, several hundred lines of...

Jeff Moden (5/12/2010)

---

Yes and no. You're certainly on the correct tract with making a table but it should be a permanent table instead of one built on the fly....

Jeff Moden (5/12/2010)

---

...

I'm still believe that the use of the Tally table here would be slower across a million rows than if the codes were stored in a nice...

I would quote the related msdn website.

AFAIK, websites are allowed as references.

e.g. http://msdn.microsoft.com/en-US/library/ms186734(v=SQL.90).aspx as a reference for the ROW_NUMBER() syntax.

I would copy the website together with a date information for...

I second Brandie and Elliot regarding the sandbox db.

Once that person "designed" a query he/she wants to run in production, that person should send the query to one of the...

Please re-read my previous post: I asked for your expected result based on your sample data. I can't find it...

All I see is

ROW # FIELD NAME 1 ... FIELD NAME...

Joe Celko (5/12/2010)

---

Don't use the race codes table; remove the code from the string and see if it has left-overs:

REPLACE('I', '',

REPLACE('N', '',

REPLACE('P', '',

REPLACE('B', '',

REPLACE ( 'W', '', mutli_race_code),

),),),)...

Maybe the reason you didn't get any reply yet is the way you posted your question.

There are some people around (including myself) that prefer to have ready to use sample...