?
Solved

tsql how to implement automatic renewal for subscription or membership

Posted on 2012-12-22
4
Medium Priority
?
338 Views
Last Modified: 2013-01-09
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 ..
0
Comment
Question by:afifosh
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38715740
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38715741
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
 
LVL 1

Author Comment

by:afifosh
ID: 38715751
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
 
LVL 1

Author Comment

by:afifosh
ID: 38715764
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question