Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

tsql how to implement automatic renewal for subscription or membership

Posted on 2012-12-22
4
329 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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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