There are few ways of doing this, here are two of them. The first uses a combination of GROUP BY and HAVING, the second uses ROW_NUMBER and MIN/MAX Window function.

😎

USE tempdb;

GO

SET NOCOUNT ON;

CREATE TABLE [dbo].[retail](

[subscriber] [int] NULL,

[wan] [int] NULL,

[activation_date] [datetime] NULL

) ON [PRIMARY];

insert into dbo.retail(subscriber,wan,activation_date)

select '1126','1099','2014-10-05 00:00:00.000' UNION ALL

select '1126','1100','2014-10-09 00:00:00.000' UNION ALL

select '1127','1121','2014-10-05 00:00:00.000' UNION ALL

select '1127','1122','2014-10-09 00:00:00.000' UNION ALL

select '1128','1123','2014-10-05 00:00:00.000' UNION ALL

select '1129','1124','2014-10-09 00:00:00.000'

/* GROUP BY and HAVING */

SELECT

MIN(RT.wan) AS [Old dated Wan]

,MAX(RT.wan) AS [New dated Wan]

,RT.subscriber

FROM dbo.retail RT

GROUP BY RT.subscriber

HAVING MIN(RT.wan) <> MAX(RT.wan)

/* ROW NUMBER and WINDOW function*/

;WITH BASE_SET AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY RT.subscriber

ORDER BY (SELECT NULL)

) AS BS_RID

,MIN(RT.wan) OVER

(

PARTITION BY RT.subscriber

) AS [Old dated Wan]

,MAX(RT.wan) OVER

(

PARTITION BY RT.subscriber

) AS [New dated Wan]

,RT.subscriber

FROM dbo.retail RT

)

SELECT

BS.[Old dated Wan]

,BS.[New dated Wan]

,BS.subscriber

FROM BASE_SET BS

WHERE BS.BS_RID = 2;

DROP TABLE dbo.retail;

Results

Old dated Wan New dated Wan subscriber

------------- ------------- -----------

1099 1100 1126

1121 1122 1127