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 ..
LVL 1
AFIF JABADOAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
hmm, I actually presume this is a duplicate of this question:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27976041.html
you should delete this question ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
AFIF JABADOAuthor Commented:
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 ?
0
 
AFIF JABADOAuthor Commented:
dear angel your add on in my old question
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27976041.html#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

???
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.