I'll guess since you didn't post a way for anyone to post tested code.

Use LEAD to access subsequent data in the current row. Then compare AgentExpDate to the LEAD...

Do it in the other order.  Use UPDATE to update the records using the OUTPUT clause to output your required fields.

Drew

Did you try:

Select 'Restored' as source, AppUser.UserId, usergroup.Name, AppUser.UserName
from AppUser
INNER JOIN RestoredDatabase.dbo.AppUser_UserGroup 
    ON AppUser.UserId = AppUser_UserGroup.UserId
INNER JOIN RestoredDatabase.dbo.UserGroup 
   ...

This "check" field, is this something that you create as a field in a table?  Because I've never seen that done.

What is the reasoning behind not using BIT flags? ...

The fact that you're getting duplicates is an issue with your logic.  You haven't given us enough information to troubleshoot the logic, but it's probably do to the fact that...

We definitely need more information, because, as currently written, the second table is completely unnecessary.

Drew

This gets close to what you want.  It uses an ILTV function for the tally table.  It essentially uses a running total modulo 2 to determine whether a certain value...

This is about as close as you can get using SQL.  If you want more control over your formatting, you should use a reporting tool.

SELECT CASE WHEN...

I'm not the OP. The UDF (dbo.fnTally) is a well know one by Jeff Moden and the OP is using this in his original post.

The depth...

Not sure exactly what you want, but maybe something like this?

Create some data:

-- Creating the table
CREATE TABLE DrillingData (
    PROJECT NVARCHAR(50),
  ...

Many people are hesitant to open random files from the Internet.  Here is a link on Forum Etiquette: How to post data/code on a forum to get the best...

Admittedly your description is a bit vague, but it sounds like you are trying to force SQL into an object-oriented paradigm.  For instance you mention "84 data values representing his...

I've always thought you should only use inner join if you want to return columns from the other table, but this link suggests join is preferable if there is...

• This reply was modified 8 months, 3 weeks ago by  drew.allen.
• This reply was modified 8 months, 3 weeks ago by  drew.allen.

This produces the exact same results as Jonathan's query with far fewer reads.

WITH CTE AS
(
    SELECT xp.RBDTYR
       ...

The problem is that you chose to create a DENORMALIZED names table.  It sounds like you're early enough to change this to NORMALIZED.  That will fix your problems.

Drew