Click here to Skip to main content
12,072,809 members (73,182 online)
Rate this:
 
Please Sign up or sign in to vote.
I have following data in table

UserId -- SubscriptionID -- PackageID -- Balance -- Start Date -- End Date -- IsActive -- IsPaid
 
5      --       16       --     2     -- 300     --  6-6-2014  -- 6-7-2014 --   True   --  True
5      --       18       --     3     -- 200     --  6-5-2014  -- 6-6-2014 --   True   --  True
5      --       20       --     2     -- 300     -- 25-6-2014  -- 25-7-2014 --   True   --  True 
I have userID and input parameter and want to do

get all user subsciption id of @user
then Check for the all subscription where iSactiv = true, If today is Enddate or balance = 0 then set IsActive = false else skip

i have write this query but it says error that multiple rows are getting....

UPDATE TblSubscription 
SET 	IsActive = 0 
WHERE 	 
UserId = @userid 
and  SubscriptionId = (SELECT SubscriptionId FROM  TblSubscription  WHERE TblSubscription.UserId = @userid and TblSubscription.IsActive = 1 and TblSubscription.IsPaid = 1) 
and  GETDATE() > FinishDate
OR   MaxNotification < = 0
OR 	 MaxArticles < = 0
OR 	 MaxVideos 	< = 0
OR 	 MaxEvent  < = 0
Posted 17-Jun-14 22:34pm
Edited 18-Jun-14 1:37am
v3
Comments
Nandakishore G N 18-Jun-14 5:58am
   
Paste what have you done till now

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Create Proc uspCheckUserSubscriptionstatus
@success  bit out,
@userid int
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
UPDATE TblSubscription
SET IsActive = 0 
WHERE 
(FinishDate < GETDATE()
OR ( MaxNotification < = 0 and 	 MaxArticles < = 0 and 	 MaxVideos 	< = 0 and 	 MaxEvent  < = 0 ))
and   IsActive = 1
and UserId = @userid
SET @success = 1
END TRY
BEGIN CATCH
SET @success = 0
END CATCH
END
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 18 Jun 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100