Select all records whose EndDate is greater than Current Date in SQL Server

pandeygo...

SOLVED CLOSED ACTIVE ANSWERED
User: pandeygolu4200
Posted: on May 26, 2022 01:33 AM
Forum: SQL Server
Answer: 1
Views: 1350

Hi,

Get list of expiry date from table with same id for multiple entries.

I have table [tblSubscriptionTable] which have some data related to plan start date and expiry date. A user can have multiple entries in same table with same id.

I need to list all those id which have EndDate > Getutcdate() for last entry of id.

SELECT [id] ,tbs.[PlanID] ,tp.[PlanName] ,[StartDate] ,[EndDate] -- ,[patientId] as PatientId ,PatientId= (select top (1) [patientId] FROM [PatientManagementDB].[dbo].[tblSubscriptionTable] tbs where patientId=tbs.patientId and DATEDIFF(DAY, Convert(date,GETUTCDATE()),Convert(date,enddate)) <1 order by id desc)

FROM [dbo].[tblSubscriptionTable] tbs inner join [dbo].[tblPlans] tp on tbs.PlanID=tp.PlanId where DATEDIFF(DAY, Convert(date,GETUTCDATE()),Convert(date,enddate)) <1 and patientId= (select top (1) [patientId] FROM [PatientManagementDB].[dbo].[tblSubscriptionTable] where patientId=tbs.patientId and DATEDIFF(DAY, Convert(date,GETUTCDATE()),Convert(date,enddate)) <1 order by id desc)

SELECT [id]
      ,tbs.[PlanID]
      ,tp.[PlanName]
      ,[StartDate]
      ,[EndDate]
     -- ,[patientId] as PatientId
	,PatientId= (select top (1) [patientId] FROM [PatientManagementDB].[dbo].[tblSubscriptionTable] tbs where patientId=tbs.patientId 
   and  DATEDIFF(DAY, Convert(date,GETUTCDATE()),Convert(date,enddate)) <1 order by id desc) 

  FROM [dbo].[tblSubscriptionTable] tbs 
  inner join
  [dbo].[tblPlans] tp on tbs.PlanID=tp.PlanId
   where 
  DATEDIFF(DAY, Convert(date,GETUTCDATE()),Convert(date,enddate)) <1
   and patientId=
  (select top (1) [patientId] FROM [PatientManagementDB].[dbo].[tblSubscriptionTable] where patientId=tbs.patientId and  DATEDIFF(DAY, Convert(date,GETUTCDATE()),Convert(date,enddate)) <1 order by

id desc)id PlanID PlanName StartDate EndDate TransactionId TransactionStatus IsActive Email patientId CreatedOn
1 3 NULL 2022-04-11 08:07:00.000 2022-10-08 08:07:08.307 ch_3KnIH5A3TwJKL8tO1LAOZJfG 1 NULL ravi@gmail.com 06538ECC-8F3B-4080-9A67-2EABD6A281A9 2022-04-11 08:07:08.307
2 3 NULL 2022-04-11 08:09:00.000 2022-10-08 08:09:17.843 ch_3KnIJDA3TwJKL8tO08xTZCfJ 1 NULL ravi@gmail.com 0B9212BD-74AE-4AB0-A05A-5BFAD6C03E11 2022-04-11 08:09:17.843
3 3 NULL 2022-10-09 08:07:08.307 2023-04-07 08:07:08.307 ch_3KnIL1A3TwJKL8tO1etWks9p 1 NULL ravi@gmail.com 06538ECC-8F3B-4080-9A67-2EABD6A281A9 2022-04-11 08:11:09.873
4 3 NULL 2022-04-11 08:14:00.000 2022-10-08 08:14:17.773 ch_3KnIO3A3TwJKL8tO0yKDAUib 1 NULL ravi@gmail.com 0BFAC5C1-1E43-46C3-A5AB-F44A663215EA 2022-04-11 08:14:17.773
5 1 NULL 2022-04-11 08:16:00.000 2022-05-01 08:16:17.917 ch_3KnIPyA3TwJKL8tO0ZqMhYKJ 1 NULL ravi@gmail.com 10637FF4-D93B-4FC3-9A39-E3BF46C61D74 2022-04-11 08:16:17.917
7 4 NULL 2022-10-09 08:14:17.773 2023-10-09 08:14:17.773 ch_3KoSSuA3TwJKL8tO1xRXxka8 1 NULL milan67@yopmail.com 0BFAC5C1-1E43-46C3-A5AB-F44A663215EA 2022-04-14 13:12:05.790
8 2 NULL 2022-04-14 13:29:00.000 2022-07-13 13:29:09.440 ch_3KoSjQA3TwJKL8tO0QeImewK 1 NULL milan12@yopmail.com 127F0556-BB45-40D9-84EE-350FAD471718 2022-04-14 13:29:09.440
9 3 NULL 2022-07-14 13:29:09.440 2023-05-10 13:29:09.440 ch_3KoSkJA3TwJKL8tO1GR5Bmuz 1 NULL milan12@yopmail.com 127F0556-BB45-40D9-84EE-350FAD471718 2022-04-14 13:30:04.353
10 2 NULL 2022-05-02 08:16:17.917 2022-07-31 08:16:17.917 ch_3L3YlDA3TwJKL8tO0sd6lO1D 1 NULL abc@gmail.com 10637FF4-D93B-4FC3-9A39-E3BF46C61D74 2022-05-26 04:57:25.000

Download FREE API for Word, Excel and PDF in ASP.Net

arjunv

0 2

Reply
ANSWER
User: arjunv
Replied: on May 26, 2022 02:58 AM
Modified: on Jun 10, 2022 09:10 AM
Report

Hi pandeygolu4200,

First select all columns from Database Table and use WHERE Clause for Id columns and use again SELECT query for Id column and use GETUTCDATE() function this will returns the current database system Date and Time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format and use ORDER BY command to sort the result set in descending order and pass condition for checking StartDate < EndDate.

Please refer below sample query.

SQL

CREATE TABLE #Test
(
	Id INT,
	Desc_Id INT,
	PlanID INT,
	StartDate DATETIME,
	EndDate DATETIME,
	TransactionStatus VARCHAR(150),
	TransactionId INT,
	IsActive INT,
	Email VARCHAR(50),
	PatientId UNIQUEIDENTIFIER,
	CreatedOn DATETIME
)
INSERT INTO #Test VALUES (1,3,NULL,'2022-04-11 08:07:00.000','2022-10-08 08:07:08.307','ch_3KnIH5A3TwJKL8tO1LAOZJfG',1,NULL,'ravi@gmail.com','06538ECC-8F3B-4080-9A67-2EABD6A281A9','2022-04-11 08:07:08.307')
INSERT INTO #Test VALUES (2,3,NULL,'2022-04-11 08:09:00.000','2022-10-08 08:09:17.843','ch_3KnIJDA3TwJKL8tO08xTZCfJ',1,NULL,'ravi@gmail.com','0B9212BD-74AE-4AB0-A05A-5BFAD6C03E11','2022-04-11 08:09:17.843')
INSERT INTO #Test VALUES (3,3,NULL,'2022-10-09 08:07:08.307','2023-04-07 08:07:08.307','ch_3KnIL1A3TwJKL8tO1etWks9p',1,NULL,'ravi@gmail.com','06538ECC-8F3B-4080-9A67-2EABD6A281A9','2022-04-11 08:11:09.873')
INSERT INTO #Test VALUES (4,3,NULL,'2022-04-11 08:14:00.000','2022-10-08 08:14:17.773','ch_3KnIO3A3TwJKL8tO0yKDAUib',1,NULL,'ravi@gmail.com','0BFAC5C1-1E43-46C3-A5AB-F44A663215EA','2022-04-11 08:14:17.773')
INSERT INTO #Test VALUES (5,1,NULL,'2022-04-11 08:16:00.000','2022-05-01 08:16:17.917','ch_3KnIPyA3TwJKL8tO0ZqMhYKJ',1,NULL,'ravi@gmail.com','10637FF4-D93B-4FC3-9A39-E3BF46C61D74','2022-04-11 08:16:17.917')
INSERT INTO #Test VALUES (7,4,NULL,'2022-10-09 08:14:17.773','2023-10-09 08:14:17.773','ch_3KoSSuA3TwJKL8tO1xRXxka8',1,NULL,'milan67@yopmail.com','0BFAC5C1-1E43-46C3-A5AB-F44A663215EA','2022-04-14 13:12:05.790')
INSERT INTO #Test VALUES (8,2,NULL,'2022-04-14 13:29:00.000','2022-07-13 13:29:09.440','ch_3KoSjQA3TwJKL8tO0QeImewK',1,NULL,'milan12@yopmail.com','127F0556-BB45-40D9-84EE-350FAD471718','2022-04-14 13:29:09.440')
INSERT INTO #Test VALUES (9,3,NULL,'2022-07-14 13:29:09.440','2023-05-10 13:29:09.440','ch_3KoSkJA3TwJKL8tO1GR5Bmuz',1,NULL,'milan12@yopmail.com','127F0556-BB45-40D9-84EE-350FAD471718','2022-04-14 13:30:04.353')
INSERT INTO #Test VALUES (10,2,NULL,'2022-05-02 08:16:17.917','2022-07-31 08:16:17.917','ch_3L3YlDA3TwJKL8tO0sd6lO1D',1,NULL,'abc@gmail.com','10637FF4-D93B-4FC3-9A39-E3BF46C61D74','2022-05-26 04:57:25.000')
 
SELECT *
FROM #Test 
WHERE Desc_Id = (SELECT TOP 1 Desc_Id FROM #Test ORDER BY Id DESC)
AND EndDate > GETUTCDATE()
 
DROP TABLE #Test

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Select all records whose EndDate is greater than Current Date in SQL Server

Company

Explore

Follow us

Contact Us