2026-06-15SELECT 
DISTINCT 
  Substring((Select DISTINCT ',' + L.LoginName ----+'('+t2.TCODE+')'  
									From [LIMS_LIVE].[dbo].LOgin L with (Nolock)
                  
  Left Join [LIMS_LIVE].[dbo].sortedOrderTest SOT with (nolock)
			  on SOT.PatientVisitID = PV.PatientVisitID
			  and SOT.OrgID = PV.OrganizationID
              and SOT.AccessionNumber = ORD.AccessionNumber
              and L.loginID = SOT.loginID
	and L.OrganisationID = SOT.OrgID
						      
                    Where 
						    		SOT.OrgID = PV.OrganizationID
                    AND  ST.PatientVisitID = PV.[PatientVisitID]
						    		For XML Path('')),2,8000) As CaseAssignedTo 
                    
                    
                    
                    , count(distinct(

case

when PI.[status] <> 'approve'

then PV.PatientVisitID

else NULL

end

)) [No. Of Reports pending for Authorization]

                    
                    
                    
                    
                    
--       ,(select count(a.Status)---- <> 'approve')as [No.Of Reports pending for Authorization]            
--from [LIMS_LIVE].[dbo].patientinvestigation_View a
--INNER JOIN [LIMS_LIVE].[dbo].[PatientVisit] PV (NOLOCK) ON    PV.[PatientVisitID] = a.PatientVisitID
-- And  PV.OrganizationID = a.OrgID 
--where Status <> 'approve' and PV.OrganizationID = 67)[No.Of Reports pending for Authorization]  


,DATEDIFF(HOUR,   PI.ValidatedAt,PI.ApprovedAt ) AS [Hours Pending]
, PI.Status
 ,CASE WHEN ORD.ReportDateTime  <= getDate() THEN 'DELAYED' ELSE  'TODAY-TAT' END AS Status



--(PV.VisitNumber)
--, PV.VisitDate
--
--    ,P.NAME PatientName 
--  ---	, P.AGE
--	---,P.SEX
--
--	, Location
--	,PISM.Barcode
--	,PISM.CaseNumber
--  	, Tests.TCODE 
--	, Tests.TNAME 
--	, Tests.ATYPE
--      ,Substring((Select DISTINCT ',' + L.LoginName ----+'('+t2.TCODE+')'  
--									From [LIMS_LIVE].[dbo].LOgin L with (Nolock)
--                  
--  Left Join [LIMS_LIVE].[dbo].sortedOrderTest SOT with (nolock)
--			  on SOT.PatientVisitID = PV.PatientVisitID
--			  and SOT.OrgID = PV.OrganizationID
--              and SOT.AccessionNumber = ORD.AccessionNumber
--              and L.loginID = SOT.loginID
--	and L.OrganisationID = SOT.OrgID
--						      
--                    Where 
--						    		SOT.OrgID = PV.OrganizationID
--                    AND  ST.PatientVisitID = PV.[PatientVisitID]
--						    		For XML Path('')),2,8000) As CaseAssignedTo 
--	
--	
--	, pv.ReferingPhysicianName
--		
--	
--
--  ,PI.ApprovedAt
--  , PI.ValidatedAt
--     ,DATEDIFF(HOUR,   PI.ValidatedAt,PI.ApprovedAt ) AS [Hours Pending]
--	, ORD.ReportDateTime
--	
--
--	, ORD.[PkgName]
--	, ORD.ResCaptureLoc
--	
--	, cm.ClientCode
--	, cm.ClientName
--    ,CASE WHEN ORD.ReportDateTime  <= getDate() THEN 'DELAYED' ELSE  'TODAY-TAT' END AS Status
--	, PI.Status
--  , PI.Reason
  

 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 PI.Status <> 'Approve'
--AND PI.Status <> 'Cancel'
--AND PI.Status <> 'Retest'
--AND PI.Status <> 'Rejected'
--AND PI.Status <> 'Recheck'
and PI.Status in ('Co-authorize','Validate')
AND PI.ORGID = 67 
--AND (Tests.DeptName like '%surgical%' or Tests.DeptName like '%TRANSPLANT PATHOLOGY EXPERT OPINION%'
--or tests.tcode in (' P8530',
--'P8531',
--' P8532',
--' P8533',
--'P8534',
--'I8155',
--'K8112',
--'K8113',
--'R0080',
--'H0357',
--'I8180',
--'H8600',
--'H0877',
--'G0349',
--'H0141',
--'L0146',
--'I0385',
--'I0387',
--'I0389',
--'I0391',
--'�I0393',
--'I0395',
--'I0400',
--'I0402',
--'I0404',
--'I0406',
--'I0408',
--'I0410',
--'I0412',
--'I0413',
--'�I8400',
--'I8401',
--'I0384',
--'I0386',
--'I0388',
--'�I0390',
--'�I0392',
--'I0394',
--'I0399',
--'I0401',
--'I0403',
--'I0405',
--'I0407',
--'I0409',
--'I0411',
--'P0173�',
--'I0376  ',
--'C0394',
--'P0184'))
--

group by

-- Substring((Select DISTINCT ',' + L.LoginName ----+'('+t2.TCODE+')'  
--									From [LIMS_LIVE].[dbo].LOgin L with (Nolock)
--                  
--  Left Join [LIMS_LIVE].[dbo].sortedOrderTest SOT with (nolock)
--			  on SOT.PatientVisitID = PV.PatientVisitID
--			  and SOT.OrgID = PV.OrganizationID
--              and SOT.AccessionNumber = ORD.AccessionNumber
--              and L.loginID = SOT.loginID
--	and L.OrganisationID = SOT.OrgID
--						      
--                    Where 
--						    		SOT.OrgID = PV.OrganizationID
--                    AND  ST.PatientVisitID = PV.[PatientVisitID]
--						    		For XML Path('')),2,8000) ----As CaseAssignedTo 
--                    
--                    
----                    
--                    , count(distinct(
--
--case
--
--when PI.[status] = 'approve'
--
--then PV.PatientVisitID
--
--else NULL
--
--end
--
--))---- AutoAuthReports

                    
                    
                    
                    
                    
--       ,(select count(a.Status)---- <> 'approve')as [No.Of Reports pending for Authorization]            
--from [LIMS_LIVE].[dbo].patientinvestigation_View a
--INNER JOIN [LIMS_LIVE].[dbo].[PatientVisit] PV (NOLOCK) ON    PV.[PatientVisitID] = a.PatientVisitID
-- And  PV.OrganizationID = a.OrgID 
--where Status <> 'approve' and PV.OrganizationID = 67)[No.Of Reports pending for Authorization]  

PV.PatientVisitID
,DATEDIFF(HOUR,   PI.ValidatedAt,PI.ApprovedAt ) ---AS [Hours Pending]
, PI.Status
 ,CASE WHEN ORD.ReportDateTime  <= getDate() THEN 'DELAYED' ELSE  'TODAY-TAT' END ---AS Status
,ORD.AccessionNumber
, PV.OrganizationID
,ST.PatientVisitID
MUMBAI\n