I have two table [tblPatient] (main table) and [tblSubscriptionTable], i need to fetch total patient, active patient, inactive patient.
I can easily getting total count for totalpatinet, but for active patient there is entry in tblSubscriptionTable where patientid subscription detail are there , i need to fetch those record which have current subscription >getutcdate. A patientid can have multiple entries in table i need to list last record and join with tblpatienttable for active patient list.
And For inactive Patient whose subscription details are end i.e. enddate>getutcdate() should be list.
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-09-24 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
PatientId Email PasswordHash PasswordSalt FirstName LastName PhoneNo AddressLine CreatedOn CreatedBy UpdatedOn UpdatedBy LastPasswordChangedDate IsActive IsOtpVerified LoginTime PlanId IsPlanActive ProfileName DocumentName Biometric Role RoleId Documenttype Token DeletedBy DeletedOn
06538ECC-8F3B-4080-9A67-2EABD6A281A9 0x0027A3F81221314F96AB06EED5B60E840200000033399C9B3176A98CBBED3602FF057125F694D078A4BBAB354308556B1DEDA99BA32857C0DA8FAA660252E393F1FC843E 0x2432612431302469427767384A2E4C507A4363583066594B6A4A4230657166795A5456644C436E2E356E4F6F32712F6C426939306231623658524153 0x2432612431302469427767384A2E4C507A4363583066594B6A4A423065 0x0027A3F81221314F96AB06EED5B60E84020000008454FCBF56E5B1D21CE5A4AB8B4544956C5A87C105BA6F1F5C8DEA22D815DE77 0x0027A3F81221314F96AB06EED5B60E8402000000500143A0272698B034BEA01303DE76C11BD54D00AB8FAE3632EC0C115CEE35BA 0x0027A3F81221314F96AB06EED5B60E840200000075EEE243CB8D47DD004E9E29AE6B632FED38D2B733CE3150655C5FB6A9BDEFA1D1D423F565F81701100C2C5EEC7E3283 0x0027A3F81221314F96AB06EED5B60E8402000000F5EBB2B0918581A6736861D02322C15447A8A2962746E94082894553B58E5D84EED2DFC8CB63F26F6B79F8E81A523DA1 2022-03-30 11:52:12.880 NULL 2022-05-15 07:45:23.837 8C11C158-8C13-4B47-8BE5-99D645E3B5F8 NULL 1 1 2022-05-12 12:36:07.697 2 1 NULL NULL 0 NULL 2 undefined eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1bmlxdWVfbmFtZSI6IjA2NTM4RUNDLThGM0ItNDA4MC05QTY3LTJFQUJENkEyODFBOSIsInJvbGUiOiJQYXRpZW50IiwiZW1haWwiOiJtaWxhbjMyQHlvcG1haWwuY29tIiwibmJmIjoxNjUyMzU4OTY3LCJleHAiOjE2ODM4OTQ5NjcsImlhdCI6MTY1MjM1ODk2N30.VSCzv_x5AytavXr60LHTZMyBck8mPHmtydNsfU1xB0U 8C11C158-8C13-4B47-8BE5-99D645E3B5F8 2022-05-17 12:13:39.783
08309D22-3A97-4BFC-A9C1-D4A5EEF3C698 0x0027A3F81221314F96AB06EED5B60E8402000000E261C78BC0A47AC6E6D3EB39E655E3E59F90CD6E81A84FF9F84F5ABE15360C88D37E99528316115CF7B4617F2519371C 0x243261243130246B436E765579577833683559503362434A536D786975333456716170415148677365626536426D4A6B4766343365665A4948633371 0x243261243130246B436E765579577833683559503362434A536D786975 0x0027A3F81221314F96AB06EED5B60E84020000000C976BBE9974B819465369178A8DC2F21A39A47C826AA7605C5A5F158FBAD505 0x0027A3F81221314F96AB06EED5B60E840200000034D71C09E285B33A1CE292BE67B9E7285EE2851FD2ECD2B97A4A69A79B318DBE 0x0027A3F81221314F96AB06EED5B60E840200000000A743C1CE8C7859FB7C745281AAFD4ADA2E6F276689496B1C632F9987FAC934667B3FC4897CC50ABB5BEF11188CBBDA 0x0027A3F81221314F96AB06EED5B60E84020000000973475D671D15C33417036ACE962940DB92D44434D8B28819A0854F905A9FD3 2022-05-12 16:32:12.570 NULL NULL NULL NULL 1 0 NULL NULL 0 NULL NULL 0 NULL 2 1 NULL NULL NULL
0AD1AAAD-D2A6-41B0-8F9D-5A9661F5760F 0x0027A3F81221314F96AB06EED5B60E8402000000DCD5337D69BE3FC01362C9D88474006DC78F0F3110AEEF4C380065792276A386E60BB010D851AD07A2CCA7C9CAB443B3 0x2432612431302473383548355A623262436B6A4543653063664F4664756D48353138627A716247484A3044424B6E62662F34555650734E6E594C7A6D 0x2432612431302473383548355A623262436B6A4543653063664F466475 0x0027A3F81221314F96AB06EED5B60E84020000000DA9C55FCA8A34ED5FF204B20B542A254DE2143E9592D1A4283D64DE76E351E71A23180984D14792CAED72827D098E4C 0x0027A3F81221314F96AB06EED5B60E84020000008C5A0ACA91CD49CBA085E9BB961C1859305023CB147146DAB5CBB4E15BA3C9182EB43D8AC94C135F4584BC768AE521D0 0x0027A3F81221314F96AB06EED5B60E8402000000CC30FCAF85891752B0C7E136CCC06022B9BA5F95B60B522DAB7F6B536D0B03D2894B7973659D619A5A1735DF9D632903 0x0027A3F81221314F96AB06EED5B60E84020000000D6920FA069C77DBAD02210D53E5F6452B0A1293917F6C9B72735987BDFBF520 2022-05-04 07:05:59.627 NULL NULL NULL NULL 1 0 NULL NULL 0 NULL NULL 0 NULL 2 2 NULL NULL NULL
0B9212BD-74AE-4AB0-A05A-5BFAD6C03E11 0x0027A3F81221314F96AB06EED5B60E840200000033A1519FA5F0847D2CB0AD86C4B8C7DFC1286CC398803F5B9A1EDB9D0F012382AD44641F199B3BFA3A1D3888749BE016 0x2432612431302463776442364137326D524244592F437A3274504C34654E795A4E69692F46324B3076755247363342494652455A3744376C69556447 0x2432612431302463776442364137326D524244592F437A3274504C3465 0x0027A3F81221314F96AB06EED5B60E840200000042052974F0EC5794D9BAA8BF22A30CA5D3AF038029C5DBC790FDCBC07A5045E5 0x0027A3F81221314F96AB06EED5B60E840200000029A4A52F27E88F2D8B088CF5ADA6D5D4BD93F05503824B4410A0031F543E54F6 0x0027A3F81221314F96AB06EED5B60E8402000000B54C6F24D5E271D127679A6667C765B811B59B0C1BA40BF5CCE06AD1F5647EE6D0B72AF41EA659DEFE7F5FEF8FD55774 0x0027A3F81221314F96AB06EED5B60E84020000008DA74E8492E1F0190A32E169760DED4F4FDF47FE53A7D38E5C70D837645441FA 2022-03-22 12:36:18.480 NULL 2022-05-15 07:54:42.783 8C11C158-8C13-4B47-8BE5-99D645E3B5F8 NULL 1 1 NULL 1 1 Cristiano_Ronaldo_2018.jpg 123.pdf 0 NULL 2 1 NULL NULL NULL
0BFAC5C1-1E43-46C3-A5AB-F44A663215EA 0x0027A3F81221314F96AB06EED5B60E840200000090B67C552DA0E5EE5BED0A107370D849A833F5CF55F608FED656DE6819307A9ED1D598FA6D0603696DD116F3789A3E97 0x2432612431302473754F43574D72706336387150654368357453612E4F796D594B626446556C49304A44737144453435336C4D736B704E45506B4C43 0x2432612431302473754F43574D72706336387150654368357453612E4F 0x0027A3F81221314F96AB06EED5B60E8402000000EF3CFACBF1CE641ACE16F422200FF16E88CB14588ACD24300C05362EBCC28298 0x0027A3F81221314F96AB06EED5B60E8402000000CD0EC4A88FCDBE7434711C8B1D92AC49F6EB705BC910C2895A6E8ED5A83DA9A3 0x0027A3F81221314F96AB06EED5B60E8402000000630A84FA10B9B6DCB47E7D0C1F93F8BCBB7AF5B65B2B63134F96F9CEC16FDE83B362B6C9FFC00D0CE6FFA34CC6ED6CE7 0x0027A3F81221314F96AB06EED5B60E8402000000D20CE38AD6358D3ED809462CC2F10037318BEE94E2C7C3593AE153628B0E5B8DA3C73D0E406A170AD02C283084FE6612D5855925BABE91D3126C9E2E3CBD4A00 2022-04-06 07:24:15.577 NULL 2022-05-27 10:05:39.503 8C11C158-8C13-4B47-8BE5-99D645E3B5F8 NULL 1 1 2022-05-18 10:46:15.457 4 1 hemsworth.jpg Passport.pdf 0 NULL 2 1 eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1bmlxdWVfbmFtZSI6IjBCRkFDNUMxLTFFNDMtNDZDMy1BNUFCLUY0NEE2NjMyMTVFQSIsInJvbGUiOiJQYXRpZW50IiwiZW1haWwiOiJtaWxhbjY3QHlvcG1haWwuY29tIiwibmJmIjoxNjUyODcwNzc1LCJleHAiOjE2ODQ0MDY3NzUsImlhdCI6MTY1Mjg3MDc3NX0.u5WbEiKTJZOd2VLCq6AyiDpSA1HfbBuIu_QsEbv0Qtc NULL NULL
