USE [PATIENT_DATA] GO /****** Object: StoredProcedure [dbo].[sp_adm_RevokeAdmission] Script Date: 06/23/2014 16:58:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_adm_RevokeAdmission] @IDNum as VarChar(10), @RevokeUser as varchar(10) =null AS if (select AdditionalBed from tbpatient where IDNum=@IDNum) = '0' Begin Update Build_File..tbCoRoom set bedsleft = bedsleft + 1 where roomid in (select roomid from tbpatient where idnum=@idnum); end; else Begin Update Build_File..tbCoRoom set AdditionalBed = AdditionalBed - 1 where roomid in (select roomid from tbpatient where idnum=@idnum); End; Insert into tbPatient_Revoke( HospNum, IdNum, AccountNum, HospPlan, MedicareType, AdmType, AdmDate, DcrDate, ServiceID, ResultID, DispositionID, AttendingDr1, AttendingDr2, AdmittingDr, ReferringDr, AdmittingClerk, ReferredFrom, DischargeClerk, Age, BillingDate, RoomID, BillingClerk, RoomRate, RecordStatus, OPDIdNum, MedicoLegal, Claim1, ArDate, WithWatcher, AdditionalBed, WatcherRate, PharmaSeniorDiscounted, LastUpdatedAR, ZeroDet, AttendingDr5, AttendingDr6, AttendingDr7, AttendingDr8, AttendingDr9, AttendingDr10, TransferredDate, [Room-In], UserID, HostName, RevokeUser) Select HospNum, IdNum, AccountNum, HospPlan, MedicareType, AdmType, AdmDate, DcrDate, ServiceID, ResultID, DispositionID, AttendingDr1, AttendingDr2, AdmittingDr, ReferringDr, AdmittingClerk, ReferredFrom, DischargeClerk, Age, BillingDate, RoomID, BillingClerk, RoomRate, RecordStatus, OPDIdNum, MedicoLegal, Claim1, ArDate, WithWatcher, AdditionalBed, WatcherRate, PharmaSeniorDiscounted, LastUpdatedAR, ZeroDet, AttendingDr5, AttendingDr6, AttendingDr7, AttendingDr8, AttendingDr9, AttendingDr10, TransferredDate, [Room-In], UserID, HostName, @RevokeUser From tbPatient Where IDNum = @IDNum; Delete tbPatient Where IDNum = @IDNum ; Insert into tbPatient2_Revoke( IDNum, VIP, Confidential, HowAdmitted, PatientClass, AccomodationID, AskCashBasis, OkeyLab, OkeyRTS, OkeyXray, OkeyUltra, OkeyCT, OkeyPT, OkeyNU, Remarks, CreditLine, ApprovedByID, OkeyAU, OkeyHS, DietID, DietRemarks, WithDrOrder, OkeyPH, OkeyCS, OkeyEndo, WithOpenCR, OkeyNursery, WithPromisoryNote, AdmRemarks, AdmissionTypeID, PackageID, DialysisID, SpecimenID, TransplantID, OkeyICU, OkeyDiet, OkeyER, OkeyDR, OkeyDI, CashUserID, PatientsMed, OkeyNICU, OptionalMedicareComputation, FirstNotice, SecondNotice, FinalNotice, [Check], Card, LOA, OkeyOL) Select IDNum, VIP, Confidential, HowAdmitted, PatientClass, AccomodationID, AskCashBasis, OkeyLab, OkeyRTS, OkeyXray, OkeyUltra, OkeyCT, OkeyPT, OkeyNU, Remarks, CreditLine, ApprovedByID, OkeyAU, OkeyHS, DietID, DietRemarks, WithDrOrder, OkeyPH, OkeyCS, OkeyEndo, WithOpenCR, OkeyNursery, WithPromisoryNote, AdmRemarks, AdmissionTypeID, PackageID, DialysisID, SpecimenID, TransplantID, OkeyICU, OkeyDiet, OkeyER, OkeyDR, OkeyDI, CashUserID, PatientsMed, OkeyNICU, OptionalMedicareComputation, FirstNotice, SecondNotice, FinalNotice, [Check], Card, LOA, OkeyOL From tbPatient2 Where IDNum = @IDNum; Delete tbPatient2 Where IDNum = @IDNum ; Insert into tbPatientInformant_Revoke( IDNum, InformantName, InformantAddress, InformantRelation, InformantTelNum) Select IDNum, InformantName, InformantAddress, InformantRelation, InformantTelNum From tbPatientInformant Where IDNum = @IDNum; Delete tbPatientInformant Where IDNum = @IDNum ; Insert into tbPatientGuarantor_Revoke( IDNum, GuarantorName, GuarantorAddress, GuarantorEmployer, GuarantorTelNum, GuarantorSex, EmployerAddress) Select IDNum, GuarantorName, GuarantorAddress, GuarantorEmployer, GuarantorTelNum, GuarantorSex, EmployerAddress From tbPatientGuarantor Where IDNum = @IDNum; Delete tbPatientGuarantor Where IDNum = @IDNum ; Insert into tbPatientHistory_Revoke( IDNum, AdmDiagnosis, ChiefComplaint, AdmImpression, Height, Weight, FinalDiagnosis, Procedures, CourseWard, MedicoLegal, Remarks) Select IDNum, AdmDiagnosis, ChiefComplaint, AdmImpression, Height, Weight, FinalDiagnosis, Procedures, CourseWard, MedicoLegal, Remarks From tbPatientHistory Where IDNum = @IDNum; Delete tbPatientHistory Where IDNum = @IDNum ; GO /****** PROJECT OPD SEGREGATE ER 06.27.14 ******/ USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_PhysicianIndex] Script Date: 06/27/2014 13:19:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Alter PROCEDURE [dbo].[sp_AOPD_PhysicianIndex] @StartDate as VarChar(10), @EndDate As VarChar(10), @SearchCode AS VarChar(10) AS SELECT Patient.HospNum , Patient.IDNum , case ISNULL(Master.LastName,'') + ', ' + ISNULL(Master.FirstName,'') + ' ' + ISNULL(Master.MiddleName,'') when ', ' then 'No Name' else ISNULL(Master.LastName,'') + ', ' + ISNULL(Master.FirstName,'') + ' ' + ISNULL(Master.MiddleName,'') end as patientName, ISNULL( master.Age,'0' ) As Age, convert(varchar(25),patient.admdate,101) as admDate, ISNULL( Service.Service, '' ) As Service, ISNULL(HISTORY.diagnosisID1,'') AS DiagnosisID, ISNULL(Diag.Description,'') as Diagnosis, RTRIM( ISNULL(Doctor.FirstName,'') + ' ' + isnull(doctor.Lastname,'') ) As Doctor --osk FROM tbOutPatient Patient LEFT OUTER JOIN tbMaster Master ON Patient.HospNum = Master.HospNum LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Patient.DoctorID1 = Doctor.DoctorID LEFT OUTER JOIN Build_File..tbCoService Service ON Patient.ServiceID1 = Service.ServiceID LEFT OUTER JOIN tbOutPatientHistory HISTORY on patient.idnum = HISTORY.IDnum LEFT OUTER JOIN Build_File..tbICDDiagMain Diag on History.DiagnosisID1 = Diag.Code WHERE Patient.admdate Between @StartDate and (@EndDate + ' 23:59:59') and Patient.DoctorID1 = @SearchCode and ltrim(rtrim(isnull(Patient.opdstatus,''))) <> 'R' and isnull(Patient.ErNum,'')='' GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_ServiceIndex] Script Date: 06/27/2014 13:23:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_AOPD_ServiceIndex] @StartDate as VarChar(10), @EndDate As VarChar(10), @SearchCode As VarChar(10) AS SELECT Patient.HospNum , Patient.IDNum , case ISNULL(Master.LastName,'') + ', ' + ISNULL(Master.FirstName,'') + ' ' + ISNULL(Master.MiddleName,'') when ', ' then 'No Name' else ISNULL(Master.LastName,'') + ', ' + ISNULL(Master.FirstName,'') + ' ' + ISNULL(Master.MiddleName,'') end as patientName, CONVERT(VarChar(10), Patient.AdmDate, 101 ) As AdmDate, RTRIM( ISNULL(Doctor.LastName,'' ) ) + ', ' + RTRIM( ISNULL( Doctor.FirstName,'') ) As Doctor, ISNULL( Master.Age,'' ) As Age, ISNULL(Diag.Description,'') as Diagnosis FROM ( SELECT IDNum FROM Patient_Data..tbOutPatient WHERE ServiceID1 Like @SearchCode + '%') Service Left Outer Join Patient_Data..tbOutPatient Patient ON Service.IDNum = Patient.IDNum LEFT OUTER Join Patient_Data..tbOutPatientHistory OPD ON OPD.IDNum = Patient.IDNum LEFT OUTER JOIN Patient_Data..tbMaster Master ON Patient.HospNum = Master.HospNum LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Patient.DoctorID1 = Doctor.DoctorID LEFT OUTER JOIN Build_File..tbICDDiagMain Diag ON OPD.DiagnosisID1 = Diag.Code WHERE Patient.AdmDate Between @StartDate and (@EndDate + ' 23:59:59') and ltrim(rtrim(isnull(opdstatus,''))) <> 'R' and isnull(Patient.ErNum,'')='' order by Master.LastName GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_DiseaseIndex] Script Date: 06/27/2014 13:27:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_AOPD_DiseaseIndex] @StartDate as VarChar(10), @EndDate As VarChar(10), @SearchCode As VarChar(10) AS SELECT Patient.HospNum , Patient.IDNum , case ISNULL(Master.LastName,'') + ', ' + ISNULL(Master.FirstName,'') + ' ' + ISNULL(Master.MiddleName,'') when ', ' then 'No Name' else ISNULL(Master.LastName,'') + ', ' + ISNULL(Master.FirstName,'') + ' ' + ISNULL(Master.MiddleName,'') end as patientName, CONVERT(VarChar(10), Patient.AdmDate, 101 ) As AdmDate, RTRIM( ISNULL(Doctor.LastName,'' ) ) + ', ' + RTRIM( ISNULL( Doctor.FirstName,'') ) As Doctor, ISNULL( Master.Age,'' ) As Age, ISNULL( Service.Service, '' ) As Service FROM ( SELECT IDNum FROM Patient_Data..tbOutPatientHistory WHERE DiagnosisID1 Like @SearchCode + '%') Diagnosis Left Outer Join Patient_Data..tbOutPatient Patient ON Diagnosis.IDNum = Patient.IDNum LEFT OUTER JOIN Patient_Data..tbMaster Master ON Patient.HospNum = Master.HospNum LEFT OUTER JOIN Build_File..tbCoDoctor Doctor ON Patient.DoctorID1 = Doctor.DoctorID LEFT OUTER JOIN Build_File..tbCoService Service ON Patient.ServiceID1 = Service.ServiceID WHERE Patient.AdmDate Between @StartDate and (@EndDate + ' 23:59:59') and ltrim(rtrim(isnull(Patient.opdstatus,''))) <> 'R' and isnull(Patient.ErNum,'')='' GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_DailyAdmissionLogBook] Script Date: 06/27/2014 13:30:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sp_AOPD_DailyAdmissionLogBook] @ReportDate as varchar(10) as select P.IDNum, convert(varchar(25), P.AdmDate, 100) as AdmDate, dbo.fn_getcompletename(P.HospNum) as PxName, S.Service, C.ChiefComplaint [Causeofconsultation], C2.chiefComplaint[Causeofconsultation2], C3.chiefComplaint[Causeofconsultation3], H.Findings[Findings], M.Age, M.Sex, dbo.Fn_GetCompleteAddress(P.HospNum) as Address, P.MedicareType, H.Findings, dbo.fn_GetDrName(P.DoctorID1) as AttDr1, convert(varchar(25), P.DcrDate, 100) as DcrDate, P.UserID, H.Temperature, H.Weight, H.BloodPressure, H.PulseRate, case when (Select count(IDNum) from Patient_Data..tbOutPatient O where O.Hospnum = P.Hospnum) = '1' then 'New' else 'Old' end NewOLD, convert(varchar, m.birthdate, 101) as Bday, p.Ernum as ernum, convert(varchar,P.consultationended,101) as EndofConsultation From tboutPatient P Left Outer Join Build_File..tbCoService S on P.ServiceID1 = S.ServiceID Left Outer Join Patient_Data..tbOutPatientHistory H on P.IDNum = H.IDNum Left Outer Join Billing..tbBillOPDailyOut O on (P.IDNum = O.IDNum and O.RevenueID = 'FP') Inner Join tbMaster M on M.HospNum = P.HospNum Left Outer Join Patient_Data..tbOutPatientHistory His on P.Idnum = His.idnum left outer join build_file..tbCoChiefComplaint C on His.ChiefComplaintid1 = C.ChiefComplaintID left outer join build_file..tbCoChiefComplaint C2 on His.ChiefComplaintid2 = C2.ChiefComplaintID left outer join build_file..tbCoChiefComplaint C3 on His.ChiefComplaintid3 = C3.ChiefComplaintID left outer join build_file..tbcoCompany Comp on P.AccountNum = Comp.AccountNum left outer join build_file..tbcoBusinessClass BC on Comp.Class = BC.Code Where AdmDate between @ReportDate and @ReportDate + ' 23:59:59.99' and ErNum is null and P.isWalkIn = 0 and P.iswalkin <> 1 and P.Ernum is null and BC.Code not in ('Y', 'X') and isnull(P.ErNum,'')='' GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[OPD_DailyCensusReport] Script Date: 06/27/2014 14:08:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[OPD_DailyCensusReport] @reportDate as varchar(10), @Type as varchar(1) /* set @EndDate = '06/18/2008' set @Type = '3' */ as if @Type = '1' begin Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate, 'PERSONAL' as Account, A.AccountNum from Patient_Data..tbOutpatient A Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum where isnumeric(A.AccountNum) = 1 and a.AdmDate between @reportDate and @reportDate + ' 23:59:59.99' and isnull(A.ErNum,'')='' order by a.AdmDate end else if @Type = '2' begin Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate, C.Company as Account, A.AccountNum from Patient_Data..tbOutpatient A Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum Left Outer Join Build_File..tbcoCompany C on A.AccountNum = C.AccountNum where isnumeric(A.AccountNum) = 0 and a.AdmDate between @reportDate and @reportDate + ' 23:59:59.99' and isnull(A.ErNum,'')='' order by a.AdmDate end else if @type = '3' begin Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate, C.Company as Account, A.AccountNum from Patient_Data..tbOutpatient A Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum Left Outer Join Build_File..tbcoCompany C on A.AccountNum = C.AccountNum where a.AdmDate between @reportDate and @reportDate + ' 23:59:59.99' and isnull(A.ErNum,'')='' order by a.AdmDate end GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[OPDReportPerService] Script Date: 06/27/2014 14:07:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Alter procedure [dbo].[OPDReportPerService] @StartDate as varchar(10), @EndDate as varchar(10), @serviceid as varchar(3) as select a.hospnum, a.idnum, a.admDate as DateAdmitted, b.firstname +' ' + b.middlename + ' ' + b.lastname as Pxname, s.serviceid, s.service, 'Dr. ' + isnull((d.firstname +' ' + d.middlename +' ' + d.lastname),'') as DrName from patient_data..tboutpatient a left outer join patient_data..tbmaster b on a.hospnum = b.hospnum left outer join build_file..tbcoservice S on a.serviceid1 = s.serviceid left outer join build_file..tbcodoctor d on a.doctorid1 = d.doctorid where a.admdate between @startdate and @endDate + ' 23:59:59:99' and a.serviceid1 = @serviceid and isnull(a.ErNum,'')='' GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_Rep_CensusReport] Script Date: 06/27/2014 14:12:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_Rep_CensusReport] @StartDate as varchar(10), @EndDate as varchar(10), @Type as varchar(1) /* set @StartDate = '06/15/2008' set @EndDate = '06/18/2008' set @Type = '3' */ as if @Type = '1' begin Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate, case when isnumeric(A.AccountNum) = 1 then 'PERSONAL' else C.Company end Account, A.AccountNum from Patient_Data..tbOutpatient A Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum Left Outer Join Build_File..tbcoCompany C on A.AccountNum = C.AccountNum where IDNum in (Select IDNum from Medicare..tbMedActual where cast(MedRoomBoard + MedDrug + MedOthers + MedOR as Decimal(12,2)) > 0) and AdmDate between @StartDate and @EndDate + ' 23:59:59.99' and isnull(A.ErNum,'')='' order by AdmDate end else if @Type = '2' begin Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate, C.Company as Account, A.AccountNum from Patient_Data..tbOutpatient A Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum Left Outer Join Build_File..tbcoCompany C on A.AccountNum = C.AccountNum where isnumeric(A.AccountNum) = 0 and AdmDate between @StartDate and @EndDate + ' 23:59:59.99' and isnull(A.ErNum,'')='' order by AdmDate end else if @Type = '3' begin Select B.Hospnum, A.IDNum, LastName, FirstName, MiddleName, AdmDate, 'PERSONAL' as Account, A.AccountNum from Patient_Data..tbOutpatient A Left Outer Join Patient_Data..tbMaster B on A.Hospnum = B.Hospnum where isnumeric(A.AccountNum) = 1 and AdmDate between @StartDate and @EndDate + ' 23:59:59.99' and A.IDNum in (Select IDNum from Billing..tbBillOPDailyOut where DRCR = 'D' group by IDNum) and isnull(A.ErNum,'')='' order by AdmDate end GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_MedRec_AOPDChiefComplaint] Script Date: 06/27/2014 14:15:19 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_MedRec_AOPDChiefComplaint] --declare @StartDate as varchar(10), @EndDate as varchar(10) as --set @StartDate = '07/01/2007' --set @EndDate = '07/31/2007' select count(A.IdNum) as [Number of Patient], isnull(B.ChiefComplaint,'OTHERS') as [Cause of Consultation] from Patient_Data..tbOutPatientHistory A LEFT OUTER JOIN BUILD_FILE..tbcoChiefComplaint B ON A.ChiefComplaintID1 = ChiefComplaintID or A.ChiefComplaintID2 = ChiefComplaintID or A.ChiefComplaintID3 = ChiefComplaintID LEFT OUTER JOIN PATIENT_DATA..tbOutPatient C ON A.IdNum = C.IdNum where convert(varchar(10),C.AdmDate,101) between @StartDate and @EndDate and isnull(C.ErNum,'')='' group by B.ChiefComplaint order by count(A.IdNum) desc GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_MonthlyAdmissionLogBook] Script Date: 06/27/2014 14:25:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sp_AOPD_MonthlyAdmissionLogBook] @StartDate as varchar(10), @EndDate as varchar(10) as select P.IDNum, convert(varchar(25), P.AdmDate, 100) as AdmDate, dbo.fn_getcompletename(P.HospNum) as PxName, S.Service, M.Age, M.Sex, dbo.Fn_GetCompleteAddress(P.HospNum) as Address, P.MedicareType, H.Findings, dbo.fn_GetDrName(P.DoctorID1) as AttDr1, convert(varchar(25), P.DcrDate, 100) as DcrDate, P.UserID, H.Temperature, H.Weight, H.BloodPressure, H.PulseRate, case when (Select count(IDNum) from Patient_Data..tbOutPatient O where O.Hospnum = P.Hospnum) = '1' then 'New' else 'Old' end NewOLD, p.accountnum, p.hospnum From tboutPatient P Left Outer Join Build_File..tbCoService S on P.ServiceID1 = S.ServiceID Left Outer Join Patient_Data..tbOutPatientHistory H on P.IDNum = H.IDNum Left Outer Join Billing..tbBillOPDailyOut O on (P.IDNum = O.IDNum and O.RevenueID = 'FP') Inner Join tbMaster M on M.HospNum = P.HospNum left outer join build_file..tbcoCompany Comp on P.AccountNum = Comp.AccountNum left outer join build_file..tbcoBusinessClass BC on Comp.Class = BC.Code Where AdmDate between @StartDate and @EndDate + ' 23:59:59.99' and ErNum is null and BC.Code not in ('Y', 'X') and isnull(P.ErNum,'')='' GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_Diagnosis] Script Date: 06/27/2014 14:33:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_Diagnosis] @StartDate as varchar(10), @EndDate as varchar(10) AS /* declare @StartDate as varchar(10) declare @EndDate as varchar(10) set @StartDate = '11/01/2010' set @EndDate = '11/30/2010' */ declare @YearStart as varchar(10) declare @YearEnd as varchar(10) declare @LastYearStart as varchar(10) declare @LastYearEnd as varchar(10) declare @LastMonthStart as varchar(10) declare @LastMonthEnd as varchar(10) set @YearStart = '01/01/' + cast(year(@StartDate) as varchar(4)) set @YearEnd = '12/31/' + cast(year(@StartDate) as varchar(4)) set @LastMonthStart = cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastMonthEnd = cast(month(@EndDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearStart = '01/01/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearEnd = '12/31/' + cast(year(@StartDate) - 1 as varchar(4)) select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(H.DiagnosisID1,'') as DiagnosisID, isnull(max(D.Description),'Not Specified') as Diagnosis from PATIENT_DATA..tbOutPatient P left outer join PATIENT_DATA..tbOutPatientHistory H on P.IDNum = H.IdNum left outer join BUILD_FILE..tbIcdDiagMain D on H.DiagnosisID1 = D.CODE where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(H.DiagnosisID1,'')<>'' and isnull(P.ErNum,'')='' group by isnull(H.DiagnosisID1,'') UNION ALL select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(H.DiagnosisID2,'') as DiagnosisID, isnull(max(D.Description),'Not Specified') as Diagnosis from PATIENT_DATA..tbOutPatient P left outer join PATIENT_DATA..tbOutPatientHistory H on P.IDNum = H.IdNum left outer join BUILD_FILE..tbIcdDiagMain D on H.DiagnosisID2 = D.CODE where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(H.DiagnosisID2,'')<>'' and isnull(P.ErNum,'')='' group by isnull(H.DiagnosisID2,'') UNION ALL select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(H.DiagnosisID3,'') as DiagnosisID, isnull(max(D.Description),'Not Specified') as Diagnosis from PATIENT_DATA..tbOutPatient P left outer join PATIENT_DATA..tbOutPatientHistory H on P.IDNum = H.IdNum left outer join BUILD_FILE..tbIcdDiagMain D on H.DiagnosisID3 = D.CODE where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(H.DiagnosisID3,'')<>'' and isnull(P.ErNum,'')='' group by isnull(H.DiagnosisID3,'') UNION ALL select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(H.DiagnosisID4,'') as DiagnosisID, isnull(max(D.Description),'Not Specified') as Diagnosis from PATIENT_DATA..tbOutPatient P left outer join PATIENT_DATA..tbOutPatientHistory H on P.IDNum = H.IdNum left outer join BUILD_FILE..tbIcdDiagMain D on H.DiagnosisID4 = D.CODE where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(H.DiagnosisID4,'')<>'' and isnull(P.ErNum,'')='' group by isnull(H.DiagnosisID4,'') UNION ALL select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(H.DiagnosisID5,'') as DiagnosisID, isnull(max(D.Description),'Not Specified') as Diagnosis from PATIENT_DATA..tbOutPatient P left outer join PATIENT_DATA..tbOutPatientHistory H on P.IDNum = H.IdNum left outer join BUILD_FILE..tbIcdDiagMain D on H.DiagnosisID5 = D.CODE where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(H.DiagnosisID5,'')<>'' and isnull(P.ErNum,'')='' group by isnull(H.DiagnosisID5,'') GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_ChiefComplaint] Script Date: 06/27/2014 14:36:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_ChiefComplaint] @StartDate as varchar(10), @EndDate as varchar(10) AS /* set @StartDate = '12/01/2003' set @EndDate = '12/31/2003' */ declare @YearStart as varchar(10) declare @YearEnd as varchar(10) declare @LastYearStart as varchar(10) declare @LastYearEnd as varchar(10) declare @LastMonthStart as varchar(10) declare @LastMonthEnd as varchar(10) set @YearStart = '01/01/' + cast(year(@StartDate) as varchar(4)) set @YearEnd = '12/31/' + cast(year(@StartDate) as varchar(4)) set @LastMonthStart = cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastMonthEnd = cast(month(@EndDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearStart = '01/01/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearEnd = '12/31/' + cast(year(@StartDate) - 1 as varchar(4)) select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.ChiefComplaintID1,'') as ChiefComplaintID, isnull(max(C.ChiefComplaint),'') as ChiefComplaint from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoChiefComplaint C on P.ChiefComplaintID1 = C.ChiefComplaintID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and isnull(P.OPDStatus,'') not in ('R','E') and isnull(P.userid,'') <> '' and ISNULL(P.ERNum,'')='' group by isnull(P.ChiefComplaintID1,'') union all select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.ChiefComplaintID2,'') as ChiefComplaintID, isnull(max(C.ChiefComplaint),'') as ChiefComplaint from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoChiefComplaint C on P.ChiefComplaintID2 = C.ChiefComplaintID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and isnull(P.OPDStatus,'') not in ('R','E') and isnull(P.userid,'') <> '' and ISNULL(P.ERNum,'')='' group by isnull(P.ChiefComplaintID2,'') union all select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.ChiefComplaintID3,'') as ChiefComplaintID, isnull(max(C.ChiefComplaint),'') as ChiefComplaint from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoChiefComplaint C on P.ChiefComplaintID3 = C.ChiefComplaintID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and isnull(P.OPDStatus,'') not in ('R','E') and isnull(P.userid,'') <> '' and ISNULL(P.ERNum,'')='' group by isnull(P.ChiefComplaintID3,'') GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_Services] Script Date: 06/27/2014 14:38:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_Services] @StartDate as varchar(10), @EndDate as varchar(10) AS /* declare @StartDate as varchar(10) declare @EndDate as varchar(10) set @StartDate = '01/01/2004' set @EndDate = '01/08/2004' */ declare @YearStart as varchar(10) declare @YearEnd as varchar(10) declare @LastYearStart as varchar(10) declare @LastYearEnd as varchar(10) declare @LastMonthStart as varchar(10) declare @LastMonthEnd as varchar(10) set @YearStart = '01/01/' + cast(year(@StartDate) as varchar(4)) set @YearEnd = '12/31/' + cast(year(@StartDate) as varchar(4)) set @LastMonthStart = cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastMonthEnd = cast(month(@EndDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearStart = '01/01/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearEnd = '12/31/' + cast(year(@StartDate) - 1 as varchar(4)) select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.ServiceID1,'') as ServiceID, isnull(max(S.Service),'Not Specified') as Service from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoService S on P.ServiceID1 = S.ServiceID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and ISNULL(P.ERNum,'')='' group by isnull(p.ServiceID1,'') GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_PayCode] Script Date: 06/27/2014 14:40:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_PayCode] @StartDate as varchar(10), @EndDate as varchar(10) AS /* declare @StartDate as varchar(10) declare @EndDate as varchar(10) set @StartDate = '01/01/2004' set @EndDate = '01/08/2004' */ declare @YearStart as varchar(10) declare @YearEnd as varchar(10) declare @LastYearStart as varchar(10) declare @LastYearEnd as varchar(10) declare @LastMonthStart as varchar(10) declare @LastMonthEnd as varchar(10) set @YearStart = '01/01/' + cast(year(@StartDate) as varchar(4)) set @YearEnd = '12/31/' + cast(year(@StartDate) as varchar(4)) set @LastMonthStart = cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastMonthEnd = cast(month(@EndDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearStart = '01/01/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearEnd = '12/31/' + cast(year(@StartDate) - 1 as varchar(4)) select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.PayCode,'') as PayCodeID, isnull(max(C.PayCode),'Not Specified') as PayCode from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoPayCode C on P.PayCode = C.payCodeID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(P.UserID,'') <> '' and ISNULL(P.ERNum,'')='' group by isnull(P.PayCode,'') GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_GenderReport] Script Date: 06/27/2014 14:42:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_GenderReport] @StartDate as varchar(10), @EndDate as varchar(10) AS /* declare @StartDate as varchar(10) declare @EndDate as varchar(10) set @StartDate = '01/01/2004' set @EndDate = '01/08/2004' */ declare @YearStart as varchar(10) declare @YearEnd as varchar(10) declare @LastYearStart as varchar(10) declare @LastYearEnd as varchar(10) declare @LastMonthStart as varchar(10) declare @LastMonthEnd as varchar(10) declare @PediaAgeLimit as integer set @PediaAgeLimit = (Select PediaAgeLimit from PATIENT_DATA..tbHospitalInfo) set @YearStart = '01/01/' + cast(year(@StartDate) as varchar(4)) set @YearEnd = '12/31/' + cast(year(@StartDate) as varchar(4)) set @LastMonthStart = cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastMonthEnd = cast(month(@EndDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearStart = '01/01/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearEnd = '12/31/' + cast(year(@StartDate) - 1 as varchar(4)) select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(M.Sex,'Not Specified') as Sex, case when M.age > @PediaAgelimit then 'A' else 'P' end as AgeCategory from PATIENT_DATA..tbOutPatient P left outer join PATIENT_DATA..tbMaster M ON P.HospNum = M.HospNum where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(P.ErNum,'')='' group by M.Sex, M.Age GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_DoctorReport] Script Date: 06/27/2014 14:43:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_DoctorReport] @StartDate as varchar(10), @EndDate as varchar(10) AS /*declare @StartDate as varchar(10) declare @EndDate as varchar(10)*/ --set @StartDate = '01/01/2004' --set @EndDate = '01/08/2004' declare @YearStart as varchar(10) declare @YearEnd as varchar(10) declare @LastYearStart as varchar(10) declare @LastYearEnd as varchar(10) declare @LastMonthStart as varchar(10) declare @LastMonthEnd as varchar(10) set @YearStart = '01/01/' + cast(year(@StartDate) as varchar(4)) set @YearEnd = '12/31/' + cast(year(@StartDate) as varchar(4)) set @LastMonthStart = cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastMonthEnd = cast(month(@EndDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearStart = '01/01/' + cast(year(@StartDate) - 1 as varchar(4)) set @LastYearEnd = '12/31/' + cast(year(@StartDate) - 1 as varchar(4)) select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.DoctorID1,'') as DoctorID, isnull(max(D.LastName + ', ' + D.Firstname + ' ' + D.MiddleName),'Not Specified') as Doctor from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoDoctor D on P.DoctorID1 = D.DoctorID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and isnull(P.ERNum,'')='' group by isnull(P.DoctorID1,'') union all select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.DoctorID2,'') as DoctorID, isnull(max(D.LastName + ', ' + D.Firstname + ' ' + D.MiddleName),'Not Specified') as Doctor from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoDoctor D on P.DoctorID2 = D.DoctorID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(P.ERNum,'')='' group by isnull(P.DoctorID2,'') union all select Sum(Case When P.AdmDate between @StartDate and @EndDate + ' 23:59:59.99' then 1 Else 0 End) as MonthCount, sum(case when P.AdmDate between @YearStart and @YearEnd + ' 23:59:59.99' then 1 else 0 End) as YearCount, sum(case when P.AdmDate between @LastYearStart and @LastYearEnd + ' 23:59:59.99' then 1 else 0 End) as LastYearCount, sum(case when P.AdmDate between @LastMonthStart and @LastMonthEnd + ' 23:59:59.99' then 1 else 0 End) as LastMonthCount, isnull(P.DoctorID3,'') as DoctorID, isnull(max(D.LastName + ', ' + D.Firstname + ' ' + D.MiddleName),'Not Specified') as Doctor from PATIENT_DATA..tbOutPatient P left outer join BUILD_FILE..tbCoDoctor D on P.DoctorID3 = D.DoctorID where P.AdmDate between @LastYearStart and @YearEnd + ' 23:59:59.99' and OPDStatus not in ('R','E') and isnull(P.ERNum,'')='' group by isnull(P.DoctorID3,'') GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_ReportOfActivities] Script Date: 06/27/2014 14:45:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_AOPD_ReportOfActivities] @StartDate as varchar(25), @EndDate as varchar(25) AS Select isnull(Service,'No Service given') as Service, sum(case Type when 'NewBelow' then 1 else 0 end) as NewBelow, sum(case Type when 'OldBelow' then 1 else 0 end) as OldBelow, sum(case Type when 'NewAbove' then 1 else 0 end) as NewAbove, sum(case Type when 'OldAbove' then 1 else 0 end) as OldAbove, max(isnull(M.Description,'No Diagnosis')) as Diagnosis from (select ServiceID1 as ServiceID, 'NewBelow' as Type, D.DiagnosisID1 as DiagnosisID from tbOutPatient P inner join (Select HospNum from tbMaster where case isnumeric(dbo.fn_ComputeAge(BirthDate,getdate())) when 1 then dbo.fn_ComputeAge(BirthDate,getdate()) when 0 then 0 end < 15) M on P.HospNum = M.HospNum Left Outer Join tbOutPatientHistory D on P.IDNum = D.IDNum where admDate between @StartDate and @EndDate + ' 23:59:59' and ltrim(rtrim(isnull(opdstatus,''))) <> 'R' and rtrim(ltrim(isnull(P.PatientStatus,'N')))<>'O' and isnull(P.ErNum,'')='' union all select ServiceID1 as ServiceID, 'OldBelow' as Type, D.DiagnosisID1 as DiagnosisID from tbOutPatient P inner join (Select HospNum from tbMaster where case isnumeric(dbo.fn_ComputeAge(BirthDate,getdate())) when 1 then dbo.fn_ComputeAge(BirthDate,getdate()) when 0 then 0 end < 15) M on P.HospNum = M.HospNum Left Outer Join tbOutPatientHistory D on P.IDNum = D.IDNum where admDate between @StartDate and @EndDate + ' 23:59:59' and ltrim(rtrim(isnull(opdstatus,''))) <> 'R' and rtrim(ltrim(isnull(P.PatientStatus,'N')))<>'N' and isnull(P.ErNum,'')='' union all select ServiceID1 as ServiceID, 'OldAbove' as Type, D.DiagnosisID1 as DiagnosisID from tbOutPatient P inner join (Select HospNum from tbMaster where case isnumeric(dbo.fn_ComputeAge(BirthDate,getdate())) when 1 then dbo.fn_ComputeAge(BirthDate,getdate()) when 0 then 0 end > 14) M on P.HospNum = M.HospNum Left Outer Join tbOutPatientHistory D on P.IDNum = D.IDNum where admDate between @StartDate and @EndDate + ' 23:59:59' and ltrim(rtrim(isnull(opdstatus,''))) <> 'R' and rtrim(ltrim(isnull(P.PatientStatus,'N')))<>'N' and isnull(P.ErNum,'')='' UNION all select ServiceID1 as ServiceID, 'NewAbove' as Type, D.DiagnosisID1 as DiagnosisID from tbOutPatient P inner join (Select HospNum from tbMaster where case isnumeric(dbo.fn_ComputeAge(BirthDate,getdate())) when 1 then dbo.fn_ComputeAge(BirthDate,getdate()) when 0 then 0 end > 14) M on P.HospNum = M.HospNum Left Outer Join tbOutPatientHistory D on P.IDNum = D.IDNum where admDate between @StartDate and @EndDate + ' 23:59:59' and ltrim(rtrim(isnull(opdstatus,''))) <> 'R' and rtrim(ltrim(isnull(P.PatientStatus,'N')))<>'O' and isnull(P.ErNum,'')='' )as Source left outer join Build_File..tbCoService S on Source.ServiceID=S.ServiceID Left Outer Join Build_File..tbICDDiagMain M on Source.DiagnosisID = M.Code group by Service, DiagnosisID GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_OPSRegionalReport] Script Date: 06/27/2014 14:49:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER Procedure [dbo].[sp_AOPD_OPSRegionalReport] @StartDate varchar(10), @EndDate varchar(10) as select max(D.Region) as Region, D.ZipCode, sum(isnull(Total,0)) as Total, case isnull(max(ShowInOPD),'') when 'P' then max(D.town) when 'M' then max(D.Town) else D.Province end as Province, case max(D.Region) when 'NCR' then 1 when 'ARMM' then 2 when 'CAR' then 3 when 'CARAGA' then 4 WHEN 'I' THEN 5 when 'II' then 6 when 'III' then 7 when 'IV' then 8 when 'V' then 9 when 'VI' then 10 when 'VII' then 11 when 'VIII' then 12 when 'IX' then 13 when 'X' then 14 when 'XI' then 15 when 'XII' then 16 when 'XIII' then 17 END AS SortOrder, isnull(max(ShowInOPD),'') as ShowinOPD from (select b.zipcode as ZipCode, count(b.zipcode) as Total from (Select * from PATIENT_DATA..tbOutPatient where admdate between @StartDate and @EndDate + ' 23:59:59.99' and isnull(tbOutPatient.ERNum,'')='') a left outer join PATIENT_DATA..tbmaster b on a.hospnum =b.hospnum where isnull(b.zipcode,'')<>'' group by b.zipcode) A right outer join Build_File..tbCoAddress D on A.ZipCode = D.ZipCode where isnull(status,'') <> 'I' group by Province, D.ZipCode order by sortorder GO USE [Patient_DATA] GO /****** Object: StoredProcedure [dbo].[sp_AOPD_MonthlyPhysicianIndex] Script Date: 06/27/2014 15:00:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[sp_AOPD_MonthlyPhysicianIndex] @StartDate as varchar(25), @EndDate as varchar(25) AS Select Patient.DoctorID1 as Code, case (isnull(max(Doctor.LastName),'') + ', ' + isnull(max(Doctor.FirstName),'') + Case isnull(max(Doctor.MiddleName),'') when '' then '' else left(max(Doctor.MiddleName),1) + '.' end) when ', ' then '' else isnull(max(Doctor.LastName),'') + ', ' + isnull(max(Doctor.FirstName),'') + Case isnull(max(Doctor.MiddleName),'') when '' then '' else ' ' + left(max(Doctor.MiddleName),1) + '.' end end as DrName, count(*) as Total from tbOutPatient Patient inner join Build_File..tbCoDoctor Doctor on Doctor.DoctorID = Patient.DoctorID1 Where (AdmDate >= @StartDate and AdmDate < dateadd(day,1,@EndDate)) and isnull(Patient.ErNum,'')='' Group by patient.DoctorID1 Order by total desc GO