GilaMonster (11/10/2008)

More...

Not really. You posted a big blob of text. Read the post Hodgy tried to point you to (also in my signature) for...

Added in the two extra calculations I had only comments for in my original query as correlated subqueries... one as a CASED correlated subquery.

Temporarily removed the Windowed function and the...

That would just be an update statement with a join:

UPDATE tbl_DEMOG1

SET Name = D2.Name

FROM tbl_DEMOG1

INNER JOIN tblDEMOG2 D2 ON tblDEMOG1.PatID = D2.patID

Please refer to the post in my signature for an example of how you can supply table DDL and sample data. Provide that information, with the result you're looking...

For starters, you don't need to nest your inner joins. It makes them a LOT easier to read and work with when you do something like this (You can...

Ugh. That was actually the first thing I tried... I just didn't join on date in addition to emplID and it wasn't giving me what I wanted. Barry...

Please refer to the link in my signature for a better way to provide sample data in future posts. Try this:

[font="Courier New"]

SELECT DISTINCT

   COALESCE(H1.EmplID,M.EmplID) EmplID,

   COALESCE(H1.SurName,M.SurName) SurName,

  ...

Your friday afternoon stumper is way better than mine :ermm:.

I can tell you it's the order by on a field from a table you're not actually selecting from that allows...

Matt:

Thanks, that makes sense. I thought it might be something like that, but that definitely makes it more clear.

Lynn,

That runs fine, and gives the 12K rows expected. There's no reason I couldn't have done it that way, it was just blowing my mind that the...

@Lowell:

That's what I ended up doing to fix it, but I still don't fully understand why it should be necessary. Could you explain why a single NULL invalidates my...

They're not so much thrown... as launched. Throwing a pork chop just sounds ridiculous... I mean, who does that, pork chops are delicious... you don't want to just throw...

You can make an argument for using it instead of ISNULL, but NULLIF doesn't do the same thing as coalesce.

Group by is normally used for aggregates. To be honest, I don't need it in those queries, I just wrote them in a hurry and had originally planned on...