difference between numbers in sequence when ids missing

Question

I'm trying to get the number of months between dates that have a DateOrderID to put them into an order(earliest date is one, second is 2 etc). My problem is that, because the DateOrder ID is entered manually I have some records where there is a missing DateOrderID so my query returns a NULL for that field. My (dummy) code is:

Select a.PersonID
,a.Date
,a.DateOrderID
,case when a.DateOrderID = 1 then 'First Date'
else convert(varchar(5),datediff(month, b.Date, a.Date)) end as Months_Between
from OrderTable a
left join
OrderTable b
on a.PersonID = b.PersonID and a.DateOrderID = b.DateOrderID +1

This currently returns

PersonID    Date     DateOrder  Months_Between
101         20150401    1       First Date
101         20160621    2       14
101         20170301    3       9
102         20110506    1       First Date
102         20140817    3       NULL
102         20160906    4       25

The result I want would replace the NULL for PersonID 102 with something like 'Missing ID'. I've tried a few things in the case statement but no success, any ideas?

Answer 1

SELECT a.PersonID ,
  a.Date ,
  a.DateOrderID ,
  CASE
    WHEN a.DateOrderID = 1
    THEN 'First Date'
    ELSE ISNULL(CONVERT(VARCHAR(5),DATEDIFF(MONTH, b.Date, a.Date)),'Missing Date')
  END AS Months_Between
FROM OrderTable a
LEFT JOIN OrderTable b
ON a.PersonID     = b.PersonID
AND a.DateOrderID = b.DateOrderID +1