Need help creating complex T-SQL SELECT statement.

Question

I am trying to make SELECT statement for following situation and need help to make this SELECT statement. It's SQL Server 2005.

When the select statement is run, it should return rows which have SentDate as NULL assuming that there are no duplicate PersonID in table. It will return result set with Status as 'Initial Record' (as the same person id was not sent already). Once data is exported, SentDate will have today's date.

Now assume that two new row have been added and PersonID is duplicate. So when statement is run, it should return only newly added two records but status as 'Follow up Record' because same Person was exported in previous export job.

Here is the example of what is expected:

The initial table state would be:

RecordId  PersonId  SentDate  CreatedDate
1  1  NULL  8/8/2010
2  2  NULL  8/8/2010
3  3  NULL  8/8/2010

When first time run, it should return following resultset:

RecordId  PersonId  RecordStatus
1  1  'Initial Record'
2  2  'Initial Record'
3  3  'Initial Record'

Now the state of the table will be:

RecordId  PersonId  SentDate  CreatedDate
1  1  8/9/2010  8/8/2010
2  2  8/9/2010  8/8/2010
3  3  8/9/2010  8/8/2010

Suppose later a new has been added in table and so table values will be:

RecordId  PersonId  SentDate  CreatedDate
1  1  8/9/2010  8/8/2010
2  2  8/9/2010  8/8/2010
3  3  8/9/2010  8/8/2010
4  2  NULL  8/8/2010
5  2  NULL  8/8/2010

Now this query should return

RecordId  PersonId  RecordStatus
4  2  'Follow up Record'
5  2  'Follow up Record'

Answer 1

Assuming your table is named Contacts:

SELECT RecordId, C1.PersonId, 'Initial Record' AS RecordStatus
FROM Contacts C1
INNER JOIN (SELECT PersonId, COUNT(PersonId) as PersonCount 
            FROM Contacts 
            GROUP BY PersonId) C2 ON
 C1.PersonId = C2.PersonId
WHERE SentDate IS NULL AND PersonCount = 1

UNION

SELECT RecordId, C1.PersonId, 'Follow Up Record' AS RecordStatus
FROM Contacts C1
INNER JOIN (SELECT PersonId, COUNT(PersonId) as PersonCount 
            FROM Contacts 
            GROUP BY PersonId) C2 ON
 C1.PersonId = C2.PersonId
WHERE SentDate IS NULL AND PersonCount > 1

ORDER BY RecordId

The query works by assuming that if a PersonId only exists once in the table, it's an initial record; otherwise, it is a Follow Up Record.

EDIT

By popular demand, now with 100% less UNION!

SELECT RecordId, C1.PersonId, 
 CASE WHEN PersonCount > 1 THEN 'Follow Up Record' 
 WHEN PersonCount = 1 THEN 'Initial Record' END
 AS RecordStatus
FROM Contacts C1
INNER JOIN (SELECT PersonId, COUNT(PersonId) as PersonCount 
            FROM Contacts
            GROUP BY PersonId) C2 ON
    C1.PersonId = C2.PersonId
WHERE SentDate IS NULL

EDIT 2

It's probably overkill, but here's my first shot at a CTE version:

With PersonCountCTE (PersonId, PersonCount) AS 
(
    SELECT PersonId, COUNT(PersonId) AS PersonCount
    FROM Contacts 
    GROUP BY PersonId
)
SELECT RecordId, C1.PersonId, 
    CASE WHEN PersonCount > 1 THEN 'Follow Up Record' 
    WHEN PersonCount = 1 THEN 'Initial Record' END
    AS RecordStatus
FROM Contacts C1 
INNER JOIN PersonCountCTE C2 ON C1.PersonId = C2.PersonId
WHERE SentDate IS NULL 

Answer 2

Is it possible by using "WITH Common table expression"? I am asking because the select statement is already too long to use union.

Answer 3

With Data As
    (
    Select RecordId, PersonId, SentDate, CreatedDate
        , Row_Number() Over ( Partition By PersonId 
                              Order By CreatedDate Desc, RecordId Desc ) As Seq
    From #Test
    )
Select RecordId, PersonId
    , Case 
        When Seq = 1 Then 'Initial Record'
        Else 'Follow up Record'
        End As RecordStatus
From Data
Where SentDate Is Null