INSERT INTO [Peacock].[dbo].[Subscriptions]
([ChannelID]
,[MobileSubscriberNumber]
,[StartDate]
,[ReferenceID])
select t.ChannelID,t.MobileSubscriberNumber,t.EndDate,'SYS'
from [Peacock].[dbo].[Subscriptions] t
where t.EndDate =
( SELECT MAX(SU.EndDate)
from [Peacock].[dbo].[Subscriptions] SU
where SU.MobileSubscriberNumber = t.MobileSubscriberNumber
AND SU.ChannelID = t.ChannelID
AND datediff(d, getdate(), SU.EndDate) = 2
AND su.SubscriptionStatus = 'Active'
AND SU.UnSubscriptionDate IS NULL
AND SU.Auto = 0
)
Update t2
set Auto = '1'
from [Peacock].[dbo].[Subscriptions] t2
join ( select t.SubscriptionID
from [Peacock].[dbo].[Subscriptions] t
where t.EndDate =
( SELECT MAX(SU.EndDate)
from [Peacock].[dbo].[Subscriptions] SU
where SU.MobileSubscriberNumber = t.MobileSubscriberNumber
AND SU.ChannelID = t.ChannelID
AND datediff(d, getdate(), SU.EndDate) = 2
AND su.SubscriptionStatus = 'Active'
AND SU.UnSubscriptionDate IS NULL
AND SU.Auto = 0
) ) t1 on t1.SubscriptionID = t2.SubscriptionID
please clarify with sample data...