Solved

tsql how to implement automatic renewal for subscription or membership

Posted on 2012-12-22
4
330 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
[X]
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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse this column 6 34
SQL Server syntax 11 41
MSSQL join different row from other table 14 66
Getting "Invalid Operation" Error when opening a Recordset 10 33
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 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