what would be the best way to delete a row from a table based on expiry time

Shahzaib khan 6 Reputation points
2020-09-08T04:36:38.427+00:00

i have a table in which i have two columns named BuyDate and ExpiryDate.
now i need a suggestion what might be the best way to calculate expiry time from the above two columns?
should i create a job to search regularly on every record whether expirydate is reached or passed the current date meaning the package is expired?
or should i be making a logic in asp.net mvc controller action method to check everytime user logins whether the current date and expirydate are same or not and then delete a record if expiry date is reached?
or is there any other better approach to do this.
Basically the need is to delete a record from a table automatically if expirydate has reached or passed.
if i user controller actionmethod then record will not not be deleted until user logs in and that might happen even after expirydate has surpassed
but with sql job each record will be checked on daily basis and will delete a record as soon as expiry date reaches.
waiting for suggestions

EDIT:-
i think buy date doesn't matter in this case..all i need is to check current date and time and compare it with expiry date if current date has passed expiry date then delete the row.
this is the table

USE [TempInvestManage]  
GO  
  
/****** Object:  Table [dbo].[UserPackages]    Script Date: 9/8/2020 9:26:25 PM ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[UserPackages](  
	[Id] [int] IDENTITY(1,1) NOT NULL,  
	[U_ID] [int] NULL,  
	[P_ID] [int] NULL,  
	[PackageStatus] [bit] NULL,  
	[BuyDate] [datetime] NULL,  
	[ExpiryDate] [datetime] NULL,  
 CONSTRAINT [PK_UserPackages] PRIMARY KEY CLUSTERED   
(  
	[Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  
  
ALTER TABLE [dbo].[UserPackages] ADD  CONSTRAINT [DF_UserPackages_PackageStatus]  DEFAULT (NULL) FOR [PackageStatus]  
GO  
  
ALTER TABLE [dbo].[UserPackages]  WITH CHECK ADD  CONSTRAINT [FK_UserPackage_Packages] FOREIGN KEY([P_ID])  
REFERENCES [dbo].[Packages] ([PID])  
ON DELETE CASCADE  
GO  
  
ALTER TABLE [dbo].[UserPackages] CHECK CONSTRAINT [FK_UserPackage_Packages]  
GO  
  
ALTER TABLE [dbo].[UserPackages]  WITH CHECK ADD  CONSTRAINT [FK_UserPackage_Users] FOREIGN KEY([U_ID])  
REFERENCES [dbo].[Users] ([ID])  
ON DELETE CASCADE  
GO  
  
ALTER TABLE [dbo].[UserPackages] CHECK CONSTRAINT [FK_UserPackage_Users]  
GO  
  
  
  

and here's the sample pic of table
23248-up2.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,585 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Uri Dimant 206 Reputation points
    2020-09-08T06:20:44.417+00:00

    What is the logic to delete the rows, if day difference between Buy and Expire dates more than 20 days then delete the row?
    if so
    DELETE FROM tbl WHERE DATEDIFF ( day,BuyDate ,ExpiryDate) >20


  2. Jeffrey Williams 1,876 Reputation points
    2020-09-08T20:13:14.723+00:00

    Add an index on the ExpiryDate column - and use this:

    DELETE FROM dbo.UserPackages WHERE ExpiryDate < getdate();


  3. EchoLiu-MSFT 14,416 Reputation points
    2020-09-15T11:22:22.33+00:00

    Hi @Shahzaib khan ,

    In sql server, if you want to perform an operation automatically, creating a job is a better choice. You can add a column of data to record the user's login status in the UserPackages table (the update of the login status data may require the help of developers), and then you You can use

    delete from dbo.UserPackages where ExpiryDate <getdate() and login status='Yes'  
    

    to set the job, so that the expiration date can be deleted after the user logs in. Of course, this is just a suggestion, I can't actually test it, there may be difficulties in obtaining user status data. If obtaining user login status data cannot be achieved, then all I can think of is to create a job

    delete from dbo.UserPackages where ExpiryDate <getdate()  
    

    so that the expired data will be deleted immediately after expiration,of course, it will be deleted after the job is executed.

    In addition, when you ask questions, it is best to publish all the tables and data involved, which will help the experts to do some tests and quickly solve your problems.
    Also, after you post the question, if you want to update the question, it is best to update in the comment, so as not to disrupt the discussion thread, which is also not conducive to the solution of the problem.

    Best Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments