USE [RADIOLOGY]
GO
/****** Object:  StoredProcedure [dbo].[spRadio_CheckComm_AllSecs]    Script Date: 7/1/2024 2:39:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spRadio_CheckComm_AllSecs] 

AS
	Select
		RevenueId as [RevenueId],
		Count(RevenueId) as [Count]
	From
	
	(Select
		A.RevenueId as [RevenueId]
   	From station..tbNurseCommunicationFile A
		Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
		Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		Inner Join Radiology..tbBillExamListing C on A.ItemID = C.ItemID and A.RevenueID = C.RevenueID
	Where (A.RecordStatus is null or A.RecordStatus = '') 
		and A.RevenueID in (Select RevenueID from tbRadiologyRevenues)--('XR','CT','US','MI','MS','MM','MG','NU','PG','WC','O1','MR')
		and ((D.BillingDate is Null) or (datediff(day, D.BillingDate, getdate()) < 4))


	Union All

	Select
		A.RevenueId as [RevenueId]
  	From station..tbNurseCommunicationFile A
		Inner Join Patient_Data..tbOutPatient D on A.IdNum = D.IdNum 
		Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		Inner Join Billing..tbBillExamListing C on A.ItemID = C.ItemID and A.RevenueID = C.RevenueID
   	Where (A.RecordStatus is null or A.RecordStatus = '') 
		and A.RevenueID in (Select RevenueID from tbRadiologyRevenues)--('XR','CT','US','MI','MS','MM','MG','NU','PG','WC','O1','MR')
		and ((D.BillingDate is Null) or (datediff(day, D.BillingDate, getdate()) < 4))

	) as A
	
	Group By RevenueID