SQL - Show if a record is created after another record

Question

I want to be able to check if a specific record (in this case "under" has been created after an initial record has been created and show that in a separate with either "under" or null.

Example Data

Received    Name    Sub
01-Jun      Mike    Over
01-Jun      John    Over
02-Jun      Dave    Between
03-Jun      Pete    Over 
02-Jun      Mike    Under
03-Jun      Dave    Under

Desired Results

Received    Name    Sub     Sub2
01-Jun      Mike    Over    Under
01-Jun      John    Over    Null
02-Jun      Dave    Between Under
03-Jun      Pete    Over    Null

I am working from this code but its so horribly wrong that I cant see the wood for the trees anymore.

DECLARE @TM DATETIME;
SET @TM = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101');

select 
t1.received,
t1.name, 
t1.sub,
t2.sub as sub2

from 
dbo.tblOpen t1
join dbo.tblOpen t2 on t1.name = t2.name

where
t1.closed >= DATEADD(MONTH, -1, @TM)

Can I get a point in the right direction here please.

TIMESTAMPDIFF may be a useful function. – fungusanthrax Aug 08 '17 at 15:21 — fungusanthrax, Aug 08 '17 at 15:21
@fungusanthrax `TIMESTAMPDIFF()` is MySQL... – Aaron Dietz Aug 08 '17 at 15:38 — Aaron Dietz, Aug 08 '17 at 15:38

score 3 · Accepted Answer · answered Aug 08 '17 at 15:38

Lots of ways to approach this one.

A correlated subquery should work:

SELECT t1.received,
       t1.name, 
       t1.sub,
       CASE WHEN EXISTS (SELECT * 
                         FROM dbo.tblOpen t2 
                         WHERE t2.Name = t1.Name 
                         AND t2.received > t1.received 
                         AND t2.sub = 'UNDER') 
            THEN 'UNDER' 
      END AS sub2
FROM dbo.tblOpen t1
WHERE t1.sub <> 'UNDER'

Or a LEFT JOIN:

SELECT t1.received,
       t1.name, 
       t1.sub,
       t2.sub as sub2
FROM dbo.tblOpen t1
LEFT JOIN dbo.tblOpen t2 ON t2.Name = t1.Name 
                        AND t2.received > t1.received 
                        AND t2.sub = 'UNDER'
WHERE t1.sub <> 'UNDER'

Depending on your real data, things may get more complex. For instance, can 'UNDER' occur multiple times for any given name? If it does, do you only want to see it one time, or once per occurrence? Anyway, this is a good starting point given your sample data.

'Under' does appear more than once on some occasions but can be filtered to by 'name' or 'received' so that it just shows relevant data I am looking for. Thank you both examples work perfectly. (also sorry for the late reply) — Michael, Aug 09 '17 at 07:50

SQL - Show if a record is created after another record

1 Answers1