2026-06-13SELECT 
DISTINCT (PV.VisitNumber)
,P.PatientNumber PID

    ,'**' AS PatientName
  	, P.AGE
	,P.SEX
	
	, Location
	
	, pv.ReferingPhysicianName
	, PV.VisitDate	
    ,cast (PV.VisitDate as date) Registrationdate
	
	, PI.ApprovedAt 
	, ORD.ReportDateTime
	
	, Tests.TCODE 
	, Tests.TNAME 
	, Tests.ATYPE
	
	, ORD.[PkgName]
	, ORD.ResCaptureLoc
	
	, cm.ClientCode
	, cm.ClientName
	--, N.Value EMAILID
    ,CASE WHEN ORD.ReportDateTime  <= getDate() THEN 'DELAYED' ELSE  'PENDING' END AS TATStatus
	, ORD.Status
  --, PI.Reason
  	--, L.LoginName CaseAssignedTo
 FROM [LIMS_LIVE].[dbo].patientinvestigation_View PI (nolock)
 INNER JOIN [LIMS_LIVE].[dbo].[PatientVisit] PV (NOLOCK) ON    PV.[PatientVisitID] = PI.PatientVisitID
 And  PV.OrganizationID = PI.OrgID 
 INNER JOIN [LIMS_LIVE].[dbo].[Patient] P (NOLOCK) ON 
       P.[PatientID] = PV.PatientID
       AND P.[OrganizationID] = PV.OrganizationID 
 INNER join [LIMS_LIVE].[dbo].VisitClientMapping VCM With (nolock) 
					on VCM.VisitID = PV.PatientVisitId 
					and VCM.IsActive is null 
					and VCM.OrgID = PV.OrganizationID
 INNER JOIN [LIMS_LIVE].[dbo].[OrderedInvestigations_view] ORD (NOLOCK) ON
					 ORD.[VisitID] = PI.PatientVisitID
       AND ORD.[OrgID] = PI.OrgID 
       AND ORD.AccessionNumber = PI.AccessionNumber
 inner join [LIMS_LIVE].[dbo].ClientMaster Cm With (nolock) 
					on CM.ClientID = VCM.ClientID 
					AND  CM.OrgID = PV.OrganizationID        
 --Inner join InvestigationValues_View IV (nolock) on	 IV.PatientVisitId = PI.PatientVisitId 
	--				and IV.OrgID = PI.OrgID
	--				and IV.InvestigationID = PI.InvestigationID
	 inner  join [LIMS_LIVE].[dbo].OrganizationAddress OAD With (nolock) 
    ON PV.OrganizationID = OAD.OrganizationID AND PV.OrgAddressID = OAD.AddressID	
    Inner Join [LIMS_LIVE].[dbo].View_Tests671 tests on
	tests.OrgID = ORD.OrgID
	AND tests.ACRPID = ORD.ID
	AND tests.ATYPE = ORD.TYPE
	/*INNER JOIN [LIMS_LIVE].[dbo].PatientInvSample_view PIS (NOLOCK)
                    ON PIS.OrgID = ORD.OrgID
                    AND PIS.PatientVisitID = ORD.VisitID
                    AND PIS.RecSampleLocID = ORD.ResCaptureLoc
                   Inner Join [LIMS_LIVE].[dbo].[SampleTracker_view]  ST on 
                    ST.OrgID = PIS.OrgID  
                   AND  ST.PatientVisitID = PIS.PatientVisitID
                    AND PIS.SAMPLEID = ST.SAMPLEID
                   and ST.InvSampleStatusID = 3
					Inner join [LIMS_LIVE].[dbo].PatientInvSampleMapping_view PISM (Nolock)
				On ST.OrgID = PISM.OrgID  
                  AND  ST.PatientVisitID = PISM.VisitID
                   AND  ST.SAMPLEID = PISM.SID 
                  AND PIS.SAMPLECODE = PISM.SAMPLEID
			   AND PISM.ID = ORD.ID
			   and PISM.Type = ORD.TYPE
               */
	--Inner JOin LOgin L with (Nolock) on
	--L.loginID = PV.createdby
	--and L.OrganisationID = OAD.OrganizationID
--	left join [Notifications] N (NOLOCK) 
--On N.OrgID = OAD.OrganizationID
--AND  N.IdentityID = ORD.[VisitID]
	/*	  Left Join [LIMS_LIVE].[dbo].sortedOrderTest SOT with (nolock)
			  on SOT.PatientVisitID = PV.PatientVisitID
			  and SOT.OrgID = PV.OrganizationID
              and SOT.AccessionNumber = ORD.AccessionNumber
			  
	Left JOin [LIMS_LIVE].[dbo].LOgin L with (Nolock) on
	L.loginID = SOT.loginID
	and L.OrganisationID = SOT.OrgID
    */
 WHERE  
CAST(VisitDate as Date)  >= CAST(GetDate() -1 as Date)
AND ORD.Status <> 'Approve'
AND ORD.Status <> 'Cancel'
AND ORD.Status <> 'Retest'
AND ORD.Status <> 'Rejected'
AND Tests.TCODE IN (
'B0061_New',
'B0075',
'B0061',
'B0060',
'b0060_new',
'I0315',
'B0058',
'C0323',
'E0056',
'C0121',
'E0043',
'K0019',
'M0147',
'M0103',
'N0041',
'P0058',
'E0053',
'D0028',
'D0028_c',
'S1039',
'S1039_c',
'B1032',
'C0218',
'D0026',
'D0027',
'D0034',
'D0043',
'D0054',
'F0050',
'F0051',
'H0286',
'M0095',
'P1008',
'R0018',
'S0001',
'S0002',
'S0007',
'S0075',
'S0003',
'S0004',
'S0005',
'S0006',
'S0044',
'U0002',
'Y0001',
'C0213',
'P0109',
'T0053',
'M0070',
'A0413',
'A0613',
'B0081',
'B0023',
'B0025',
'S1038',
'S0021',
'H0002',
'I0301',
'C0251',
'C0252',
'C0307',
'C0306',
'C0122',
'C0123',
'F0032',
'F0033',
'I0372',
'I0372_BM',
'J0003',
'M5085',
'M5086',
'J0001',
'J0016',
'J0017',
'L0141',
'L0141_BM',
'L0117',
'L0118',
'N0039',
'N0040',
'P0158',
'B0059',
'C0265',
'M0101',
'M0102',
'P0149',
'P0156',
'T0099',
'H0249',
'H0252',
'B0080',
'N0023_NIPS',
'N5543',
'B0077',
'H0322',
'M0160',
'M0150',
'L0133',
'G0082',
'N5543_NIPS Advanced',
'S0074',---- MHL-205777
'P0058',
'G0079',
'T0938',
'T0080',
'Q8101',
'B0106',
'B0075',
'L0142',
'C0260',
'O0042',
'H0849',
'H0850',
'B1032',
'H0874',
'H0810',
'C0286',
'B0077',
'I0300',
'N8250',
'N8200',
'B1032'


)
AND PI.ORGID = 67 
MUMBAI\n