查询统计需求分析

北京思创贯宇科技开发有限公司文件编号文件名称查询统计需求分析版本/修订V1.1生效日期2010.10查询统计分为工作量统计，病人统计，科室统计，设备统计，报告结果统计要求输出的统计内容可自行选取。工作量统计医生工作量统计送检医生查询条件:检查时间，检查方法（检查类别），送检科室,病人类别可能的统计内容：病人人数，收费金额，检查项目的个数，检查类别的个数统计结果：计数或者求和Select*frompatient,patientvisit,studyflow,schedule,exam,balancewherePatient.TID=PatientVisit.PatientTIDandPatientVisit.TID=StudyFlow.PatientTIDandStudyFlow.scheduleTID=Schedule.TIDandStudyFlow.ExamTID=Exam.TIDandStudyFlow.BalanceTID=Balance.TIDandstudyflow.examtid=exam.tidandstudyflow.statusin(’7’,’15’,’39’,’47’)收费金额在Balance.TotalMoney或者Balance.schedulemoney送检科室统计病人数量或收费金额送检科室字段来自schedule.ReferringDepartment，或者DEPT_DICT字典按送检医生统计病人数量和收费金额送检医生取自于schedule.ReferringPhysician或者Person字典表按病人类别统计病人数量病人类别取自于patientvisit.patientclass或者patientclass字典表按检查方法统计病人数量检查方法取自于studyflow.procedurexname或者procedurexname字典表按检查类别统计病人数量检查类别取自于exam.EXAM_CLASS或者EXAM_CLASSX字典表按检查部位统计病人数量检查部位取自于studyflow.EXAM_SUB_CLASS或者EXAM_SUB_CLASSX字典表按检查项目统计病人数量检查项目字段取自于studyflow.ExamItem每个统计项目还需要输入查询条件，查询条件一般有以下几种：检查时间：exam.studydate或者studyflow.studydatetime医生姓名：schedule.ReferringPhysician送检科室：schedule.ReferringDepartment检查科室：Exam.RequestingDepartment或者studyflow.RequestingDepartment病人类别：patientclass.name检查类别：EXAM_CLASSX.EXAM_CLASS检查部位：Exam.EXAM_SUB_CLASS收费类别：PatientVisit.CHARGE_TYPE送检科室统计病人数量第一步：取出医生的姓名，方法有2种1、select*frompersonwhere2、selectdistinct(ReferringPhysician)fromschedulewherestudydatetime’’北京思创贯宇科技开发有限公司文件编号文件名称查询统计需求分析版本/修订V1.1生效日期2010.10andstudydatetime’’第二步：统计每个送检医生的送检病人次数SelectCount(*)FROMSTUDYFLOWINNERJOINPATIENTVISITONSTUDYFLOW.PatientTID=PatientVisit.TIDINNERJOINPATIENTONPatientVisit.PatientTID=Patient.TIDLEFTJOINExamONSTUDYFLOW.ExamTID=Exam.TIDLEFTJOINScheduleONSTUDYFLOW.ScheduleTID=Schedule.TIDwhereSchedule.ReferringPhysician=’XXX’andExam.Studydate’XXX’andExam.Studydate’XXX’报告医生查询条件:检查时间，检查方法（检查类别），报告状态，报告医生。可能的统计内容：报告次数，诊断结果，报告阳性率，病人阳性率统计结果：计数，比例或者求和SELECTCOUNT(*)FROMSTUDYFLOWINNERJOINPATIENTVISITONSTUDYFLOW.PatientTID=PatientVisit.TIDINNERJOINPATIENTONPatientVisit.PatientTID=Patient.TIDINNERJOINGENERAL_SERIESONSTUDYFLOW.TID=GENERAL_SERIES.ExamTIDINNERJOINReportSeriesONGENERAL_SERIES.TID=ReportSeries.SeriesTIDLEFTJOINExamONSTUDYFLOW.ExamTID=Exam.TIDLEFTJOINREPORTONREPORT.TID=ReportSeries.FirstReportTIDwhere(ReportSeries.Status='REPORTED'orReportSeries.Status='UNVERIFIED'orReportSeries.Status='VERIFIED:NOTPASSED'orReportSeries.Status='VERIFIED:PASSED'orReportSeries.Status='REVERIFIED:NOTPASSED'orReportSeries.Status='REVERIFIED:PASSED')按医生统计诊断报告数量报告医生姓名取自Report.REPORT_AUTHOR，或者Person字典表按诊断结果统计病人数量诊断结果取自exam.Disease报告阳性率报告结果取自report.reportresult按检查结果统计病人阳性率检查的阳性率取自exam.examresult按病人类别统计诊断报告数量病人类别取自patientvisit.patientclass或者patientclass字典表按各检查方法统计诊断报告数量检查方法取自studyflow.procedurexname或者procedurexname字典表按检查类别统计诊断报告数量检查类别取自Exam.EXAM_CLASS或者EXAM_CLASSX字典表按检查部位统计诊断报告数量检查部位取自Exam.EXAM_SUB_CLASS或者EXAM_SUB_CLASSX字典表每个统计项目还需要输入查询条件，查询条件一般有以下几种：报告状态：ReportSeries.Status检查时间：exam.studydate或者studyflow.studydatetime报告医生姓名：Report.REPORT_AUTHOR送检科室：schedule.ReferringDepartment检查科室：Exam.RequestingDepartment或者studyflow.RequestingDepartment北京思创贯宇科技开发有限公司文件编号文件名称查询统计需求分析版本/修订V1.1生效日期2010.10病人类别：patientclass.name检查类别：EXAM_CLASSX.EXAM_CLASS检查部位：Exam.EXAM_SUB_CLASS收费类别：PatientVisit.CHARGE_TYPE按医生统计报告数量第一步：取出医生的姓名，方法有2种1、select*frompersonwhere2、selectdistinct(report.REPORT_AUTHOR)fromreport第二步：统计每个报告医生的报告量SELECTCOUNT(*)FROMSTUDYFLOWINNERJOINPATIENTVISITONSTUDYFLOW.PatientTID=PatientVisit.TIDINNERJOINPATIENTONPatientVisit.PatientTID=Patient.TIDINNERJOINGENERAL_SERIESONSTUDYFLOW.TID=GENERAL_SERIES.ExamTIDINNERJOINReportSeriesONGENERAL_SERIES.TID=ReportSeries.SeriesTIDLEFTJOINExamONSTUDYFLOW.ExamTID=Exam.TIDLEFTJOINREPORTONREPORT.TID=ReportSeries.FirstReportTIDwhere(ReportSeries.Status='REPORTED'orReportSeries.Status='UNVERIFIED'orReportSeries.Status='VERIFIED:NOTPASSED'orReportSeries.Status='VERIFIED:PASSED'orReportSeries.Status='REVERIFIED:NOTPASSED'orReportSeries.Status='REVERIFIED:PASSED')wherereport.REPORT_AUTHOR=’XXX’andExam.Studydate’XXX’andExam.Studydate’XXX’审核医生查询条件:检查时间，检查方法（检查类别），报告状态，审核医生。可能的统计内容：一审数量，二审数量，报告阳性率，病人阳性率统计结果：计数，比例或者求和SELECTCOUNT(*)FROMSTUDYFLOWINNERJOINPATIENTVISITONSTUDYFLOW.PatientTID=PatientVisit.TIDINNERJOINPATIENTONPatientVisit.PatientTID=Patient.TIDINNERJOINGENERAL_SERIESONSTUDYFLOW.TID=GENERAL_SERIES.ExamTIDINNERJOINReportSeriesONGENERAL_SERIES.TID=ReportSeries.SeriesTIDLEFTJOINExamONSTUDYFLOW.ExamTID=Exam.TIDLEFTJOINREPORTONREPORT.TID=ReportSeries.FirstReportTIDLEFTJOINReportVerifyONReport.TID=ReportVerify.ReportTIDwhere(ReportSeries.Status='REPORTED'orReportSeries.Status='UNVERIFIED'orReportSeries.Status='VERIFIED:NOTPASSED'orReportSeries.Status='VERIFIED:PASSED'orReportSeries.Status='REVERIFIED:NOTPASSED'orReportSeries.Status='REVERIFIED:PASSED')按医生统计审核报告数量审核医生姓名取自ReportVerify.REPORT_VERIFYINGOBSERVERNAME，或者Person字典表一审的报告数量审核状态字段是ReportSeries.Status，状态有'VERIFIED:PASSED'or'VERIFIED:NOTPASSED'二审的报告数量审核状态字段是ReportSeries.Status，状态有'REVERIFIED:PASSED'or'REVERIFIED:NOTPASSED'北京思创贯宇科技开发有限公司文件编号文件名称查询统计需求分析版本/修订V1.1生效日期2010.10报告阳性率报告结果取自repor