USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_CheckCommRequestsIPD]    Script Date: 11/23/2017 13:16:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Lab_CheckCommRequestsIPD]
	@RevenueId varchar(4),
	@StationId varchar(4),
	@UserName varchar(50) = ''
AS


If @RevenueId = 'LB'
     Begin
		if datepart(hour,getdate()) > 18
		   begin
				Select count(A.IdNum) TotalRequests,
					(select count(Station.Stat) 
						from STATION..tbNurseCommunicationFile Station
							left outer join  Build_File..tbCoLabExam BuildFile on Station.ItemId = BuildFile.LabExamID
							Inner Join Patient_Data..tbPatient D on Station.IdNum = D.IdNum 
						Where IsNull(Station.RecordStatus,'') = '' and Station.RevenueId = 'LB' and Station.Stat = 'Y'
							and Station.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
							and not BuildFile.LabGrp in ('BBK')
							and Isnumeric(D.IdNUm)=1
							and isnull(Station.StationID,'')  not in ('OPD','ER')
					) TotalStat
				From STATION..tbNurseCommunicationFile A 
					Left Outer Join Build_File..tbCoLabExam B on A.ItemId = B.LabExamID
					Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
				Where IsNull(A.RecordStatus,'') = '' and A.RevenueId = 'LB' 
					and not ISNULL(B.LabGrp,'') in('BBK')
					and Isnumeric(A.IdNUm)=1
					and isnull(A.StationID,'') NOT IN ('OPD','ER')
					and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
		  end;
	else
		  begin
				Select count(A.IdNum) TotalRequests,
					(select count(Station.Stat) 
						from STATION..tbNurseCommunicationFile Station
							left outer join  Build_File..tbCoLabExam BuildFile on Station.ItemId = BuildFile.LabExamID
							Inner Join Patient_Data..tbPatient D on Station.IdNum = D.IdNum 
						Where IsNull(Station.RecordStatus,'') = '' and Station.RevenueId = 'LB' and Station.Stat = 'Y'
							and Station.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
							and not BuildFile.LabGrp in ('BBK')
							and Isnumeric(D.IdNUm)=1
							and isnull(Station.StationID,'')  not in ('OPD','ER')
					) TotalStat
				From STATION..tbNurseCommunicationFile A 
					Left Outer Join Build_File..tbCoLabExam B on A.ItemId = B.LabExamID
					Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
				Where IsNull(A.RecordStatus,'') = '' and A.RevenueId = 'LB' 
					and not ISNULL(B.LabGrp,'') in('BBK')
					and Isnumeric(A.IdNUm)=1
					and isnull(A.StationID,'') NOT IN ('OPD','ER')
					and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
		  end;
     End;
Else 	
     Begin
		if  datepart(hour,getdate()) > 18 
			begin
				Select count(A.IdNum) TotalRequests,
					(select count(Station.Stat) 
						from STATION..tbNurseCommunicationFile Station
							left outer join  Build_File..tbCoLabExam BuildFile on Station.ItemId = BuildFile.LabExamID
							Inner Join Patient_Data..tbPatient D on Station.IdNum = D.IdNum 
						Where IsNull(Station.RecordStatus,'') = '' and Station.RevenueId = 'LB' and Station.Stat = 'Y'
							and Station.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
							and BuildFile.LabGrp in('BBK')
							and Isnumeric(D.IdNUm)=1
							and isnull(Station.StationID,'')  not in ('OPD','ER')
					) TotalStat
				From STATION..tbNurseCommunicationFile A 
					Left Outer Join Build_File..tbCoLabExam B on A.ItemId = B.LabExamID
				Where IsNull(A.RecordStatus,'') = '' and A.RevenueId = 'LB' 
					and ISNULL(B.LabGrp,'') in('BBK')
					and Isnumeric(A.IdNUm)=1
					and isnull(A.StationID,'') NOT IN ('OPD','ER')
					and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
		   end;
	else
    	begin
			Select count(A.IdNum) TotalRequests,
				(select count(Station.Stat) 
					from STATION..tbNurseCommunicationFile Station
						left outer join  Build_File..tbCoLabExam BuildFile on Station.ItemId = BuildFile.LabExamID
						Inner Join Patient_Data..tbPatient D on Station.IdNum = D.IdNum 
					Where IsNull(Station.RecordStatus,'') = '' and Station.RevenueId = 'LB' and Station.Stat = 'Y'
						and Station.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
						and BuildFile.LabGrp in('BBK')
						and Isnumeric(D.IdNUm)=1
						and isnull(Station.StationID,'')  not in ('OPD','ER')
				) TotalStat
			From STATION..tbNurseCommunicationFile A 
				Left Outer Join Build_File..tbCoLabExam B on A.ItemId = B.LabExamID
				Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
			Where IsNull(A.RecordStatus,'') = '' and A.RevenueId = 'LB' 		
				and isnull(A.StationID,'') NOT IN ('OPD','ER')
				and Isnumeric(A.IdNUm)=1
				and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam 
						 where Status <> 'I')
    	   end;

     End;