Removing duplicates from one filed where the rest of the row isn't duplicated

Question

I have a problem where the data set is sometimes returning 2 rows for a serial number. This occurs when a serial number has been removed and has one row where the removal date is NULL and one where its populated

I've managed to get a query where the NULLS are removed by using a min() and group by clause but this is also removing the NULLs where the meter hasn't been removed.

SELECT  [MeterSerialNumber]
      ,[EquipmentType]
      ,[InstallDate]
      ,min ([Removaldate] ) as REM_DATE
      ,round (DATEDIFF(DAY,InstallDate,case when Removaldate IS null then convert (date,GETDATE()) else Removaldate end)/30.42,0) as Age_M


  FROM [DOCDPT].[main].[Tbl_Device_ISU]

  where EquipmentType in ('S1','NS','NSS') or EquipmentType like ('%S2%')


  Group by MeterSerialNumber,EquipmentType,InstallDate,Removaldate having COUNT(distinct removaldate) =1

order by MeterSerialNumber
  ,Removaldate desc

These are the results prior to adding in the min() and group by clause. I would like to remove row 2 as the meter has been removed but leave the bottom 2 rows. The code above seems to just remove all the NULLS. I only want to remove the NULLs where the meterserialnumber appears more than once.

MeterSerialNumber I EquipmentType I InstallDate I  Removaldate  I Age_M
000009501794462   I S1            I 2017-06-18  I  2018-01-22   I  7.000000
000009501794462   I S1            I 2017-06-18  I  NULL         I 23.000000
000009999203079   I S1            I 2017-06-18  I  NULL         I 23.000000
000009995553079   I S1            I 2017-06-18  I  NULL         I 23.000000

I presume the issue is with the count not counting NULL

Answer 1

If I understand this correctly, I think you just need to remove [Removaldate] from the GROUP BY, get rid of the HAVING and use MIN([Removaldate]) in the calculation of Age_M and the ORDER BY like this:

SELECT  
    [MeterSerialNumber]
    ,[EquipmentType]
    ,[InstallDate]
    ,MIN([Removaldate]) as REM_DATE
    ,ROUND(DATEDIFF(DAY, InstallDate, case when MIN(Removaldate) IS null then CONVERT (date,GETDATE()) else MIN(Removaldate) end)/30.42,0) as Age_M
FROM 
    Tbl_Device_ISU
WHERE 
    EquipmentType in ('S1','NS','NSS') or EquipmentType like ('%S2%')
GROUP BY 
    MeterSerialNumber,
    EquipmentType,
    InstallDate
ORDER BY 
    MeterSerialNumber,
    REM_DATE desc