Hi,
I have table where i used to store subscription details, and i am selecting current subscription and upcoming subscription based on getutcdate() and enddate column.
I am getting two rows in first row i want ISACTIVE =1 AND in second row i want ISACTIVE=0.
First row display current subscription and second row display upcoming subscription.
SELECT TOP (1000) [id]
,[PlanID]
,[PlanName]
,[StartDate]
,[EndDate]
,[TransactionId]
,[TransactionStatus]
,[IsActive]
,[Email]
,[patientId]
,[CreatedOn]
FROM [PatientManagementDB].[dbo].[tblSubscriptionTable]
id PlanID PlanName StartDate EndDate TransactionId TransactionStatus IsActive Email patientId CreatedOn
1 1 NULL 2022-05-26 09:30:00.000 2022-06-25 09:30:30.090 ch_3L3d1UA3TwJKL8tO0Zsp6Fxi 1 NULL abcd@gmail.com 10637FF4-D93B-4FC3-9A39-E3BF46C61D74 2022-05-26 09:30:30.090
2 2 NULL 2022-06-26 09:30:30.090 2022-06-23 09:30:30.090 ch_3L3d8wA3TwJKL8tO0SHIW9V5 1 NULL abcd@gmail.com 10637FF4-D93B-4FC3-9A39-E3BF46C61D74 2022-05-26 09:38:11.933
3 2 NULL 2022-05-26 09:40:00.000 2022-08-24 09:40:15.113 ch_3L3dAvA3TwJKL8tO0y8xJ2nP 1 NULL abcd@gmail.com F4E9576E-15D7-4843-AC5B-73A2041ECC78 2022-05-26 09:40:15.113
4 1 NULL 2022-05-26 09:42:00.000 2022-05-25 09:30:30.090 ch_3L3dD6A3TwJKL8tO0F7laKxG 1 NULL abcd@gmail.com F40E78FD-7AD7-440F-999C-E34B8DAC03CE 2022-05-26 09:42:29.510
5 1 NULL 2022-05-26 10:56:00.000 2022-06-25 10:56:47.413 ch_3L3eN0A3TwJKL8tO0BGyLdXq 1 NULL milan67@yopmail.com 0BFAC5C1-1E43-46C3-A5AB-F44A663215EA 2022-05-26 10:56:47.413
6 1 NULL 2022-06-26 10:56:47.413 2022-07-26 10:56:47.413 ch_3L3fLbA3TwJKL8tO1GsMc1Gp 1 NULL milan67@yopmail.com 0BFAC5C1-1E43-46C3-A5AB-F44A663215EA 2022-05-26 11:59:24.617
7 2 NULL 2022-05-26 09:30:30.090 2022-08-24 09:30:30.090 ch_3L5n1MA3TwJKL8tO0yJeHfIY 1 NULL abcd@gmail.com F40E78FD-7AD7-440F-999C-E34B8DAC03CE 2022-06-01 08:35:18.260
SELECT TOP (2) [id]
,tp.[PlanName]
,tp.PlanAmount
,[TransactionId]
,[TransactionStatus]
,tp.PlanId
,1 AS [IsActive]
,[Email]
,[patientId]
,CONVERT(DATE, [StartDate]) StartDate
,CONVERT(DATE, [EndDate]) ExpiryDate
FROM [PatientManagementDB].[dbo].[tblSubscriptionTable] ts inner join [dbo].[tblPlans] tp on ts.PlanID=tp.PlanId
where [patientId] ='0BFAC5C1-1E43-46C3-A5AB-F44A663215EA' and [EndDate] >GETUTCDATE() order by id asc
id PlanName PlanAmount TransactionId TransactionStatus PlanId IsActive Email patientId StartDate ExpiryDate
5 Monthly Plan 20 ch_3L3eN0A3TwJKL8tO0BGyLdXq 1 1 1 milan67@yopmail.com 0BFAC5C1-1E43-46C3-A5AB-F44A663215EA 2022-05-26 2022-06-25
6 Monthly Plan 20 ch_3L3fLbA3TwJKL8tO1GsMc1Gp 1 1 1 milan67@yopmail.com 0BFAC5C1-1E43-46C3-A5AB-F44A663215EA 2022-06-26 2022-07-26
here in second row i want ISACTIVE FLAG=0
