Avatar of AFIF JABBADO
AFIF JABBADO
Flag for United States of America asked on

tsql how to implement automatic renewal for subscription or membership

the automatic should be using sql agent
i have a table subscription i store in it :
userid channelid start end subscriptionstatus

how i can implement auto renewal? in this table ..
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
AFIF JABBADO

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

without knowing exactly what your data should look like before/after, it's either a UPDATE (of the end column) or a insert with a new set of start/end.
please clarify with sample data...
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
AFIF JABBADO

ASKER
i have a services and subscribers for this services ..
and all subscription is stored in the table subscription


i have run this in SQL agent and it's work !
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

Open in new window



i have add a field name Auto to prevent create many pending..

after add the all new records for new subscription
i will charge the user for the new subscription than i will update the pending status with Active ???

and now the problem i have 2 Active subscription for each users?
how i can solve it ?
AFIF JABBADO

ASKER
dear angel your add on in my old question
https://www.experts-exchange.com/questions/27976041/auto-subscription-before-2-days-from-the-expiraton-date.html?anchorAnswerId=38715755#a38715755

is good more better from my add one..
but still we have a problem when i add the pending status i have another application will charge the user by sending to him an SMs with the mobile provider

after get deilived of my sms i will change the status to Active and i get two active status

???
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck