USE [build_file]
GO
/****** Object:  Table [dbo].[tbcoBarangay]    Script Date: 03/27/2012 17:34:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbcoBarangay](
	[BarangayID] [varchar](6) NULL,
	[Barangay] [varchar](100) NULL,
	[Status] [bit] NOT NULL DEFAULT ((1))
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF




 


USE [patient_data]
GO
/****** Object:  StoredProcedure [dbo].[sp_adm_RoomStatus]    Script Date: 03/27/2012 17:33:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_adm_RoomStatus] 
	@Search as varchar(20) = '',	
	@StationID As VarChar(4) = ''
AS

  Select Room, max([Patient Name]) [Patient Name], max(NoticeDate) NoticeDate,
	max([Billed Date]) [Billed Date], max([BillUpTo Date]) [BillUpTo Date],
	max([Discharge Date]) [Discharge Date], max([Paid Date]) [Paid Date],
	max([Room Remarks]) [Room Remarks]
  from (
	select ltrim(rtrim(R.Roomid)) as Room, 
		case when NoticeDate is not null then
			N.patientname 
		     when P.RoomId is not null then
			M.FirstName + ' ' +  isnull(left(M.MiddleName, 1) + '. ','') + M.LastName
		     else
			''
			end as [Patient Name], 
		 isnull(convert(varchar,NoticeDate,100),'') as NoticeDate, 
		 isnull(convert(varchar,BilledDate,100),'') as [Billed Date],
		 isnull(convert(varchar,BillUpTo,100),'') as  [BillUpTo Date],
		 isnull(convert(varchar,DcrDate,100),'') as [Discharge Date],
		 isnull(convert(varchar,PaidDate,100),'') as [Paid Date],
		 isnull(r.Remarks,'') as [Room Remarks], 
		 IsNull(R.StationID,'') As StationID
	from Build_File..tbcoRoom R
		left outer join Patient_Data..tbpatient P on R.roomid = P.roomid
		left outer join Patient_Data..tbMaster M on P.Hospnum = M.HospNum
		left outer join Station..tbNurseDischargeNotice N on P.Idnum = N.Idnum
	where convert(varchar,noticedate, 101) = convert(varchar(10), getdate(), 101) or p.dcrdate is null

	union all

	select ltrim(rtrim(R.Roomid)) as Room, 
		case when NoticeDate is not null then
			'(Watcher) ' + N.patientname 
		     when P.RoomId is not null then
			'(Watcher) ' + Patient_Data.dbo.fn_GetCompleteName(P.Hospnum)
		     else
			''
			end as [Patient Name], 
		 isnull(convert(varchar,NoticeDate,100),'') as NoticeDate, 
		 isnull(convert(varchar,BilledDate,100),'') as [Billed Date],
		 isnull(convert(varchar,BillUpTo,100),'') as  [BillUpTo Date],
		 isnull(convert(varchar,DcrDate,100),'') as [Discharge Date],
		 isnull(convert(varchar,PaidDate,100),'') as [Paid Date],
		 isnull(r.Remarks,'') as [Room Remarks], 
		 IsNull(R.StationID,'') As StationID
	from Build_File..tbcoRoom R
		left outer join Patient_Data..tbAdmWatcherMaster P on R.roomid = P.roomid
		left outer join Station..tbNurseDischargeNotice N on P.Idnum = N.Idnum
	where convert(varchar,noticedate, 101) = convert(varchar(10), getdate(), 101) or p.dcrdate is null
	) A where A.Room like @Search + '%'  AND
	(A.StationID = @StationID or @StationID = '')
       group by Room
       order by Room