三个视图 V_GlobalStudy，V_GLOBALSTUDY_NKJK，V_One_GlobalStudy
三个视图 V_GlobalStudy，V_GLOBALSTUDY_NKJK，V_One_GlobalStudy
默认SQL语句
现场可以根据语句对应修改。
视图一、V_GlobalStudy
Create VIEW [dbo].[V_GlobalStudy] AS SELECT a.PATIENTNO, a.PATNAME, a.SEX, a.PATIENTID, b.SERV_REGNO, b.PLA_ORD_NUM, a.HISID, c.ACTION_DATE, b.ACCESSION_DATE, CASE WHEN b.EXIGENCE = '0' THEN 'false' ELSE 'true' END AS ISEXIGENCE, b.ENROLDOCTOR, b.ADM_ID_ISS, b.ADM_ID, b.BedNO, b.PAT_AGE, c.REQ_PROCNO, c.STATUS, c.REQ_PROC_ID, c.MODALITY, d.PARTOFCHECK_STAT AS PARTOFCHECK, c.ALLPARTOFCHECK, d.DIRECTION, d.ALLDIRECTION, c.STUDY_DATE, d.EXPOSALTIMES, c.STUDY_DATE AS CHECKDATE, a.BIRTHDATE, a.IDCARDNO, a.TELEPHONE, a.ADDRESS, d.CHECKDOCTOR, e.REPORTNO, e.REPORTDOCTOR, e.CHIEFDOCTOR, e.PRINTDOCTOR, d.FEE, b.REQ_SERVICE, isnull( (select top 1 REPLACE(r.LATERINFO ,CHAR(13) + CHAR(10),' ') from R_LATERINFO r where r.REQ_PROC_ID=c.REQ_PROC_ID order by r.LATERDATE desc),'') LATINFO,dbo.GetImageNum(c.STUDY_INS_UID) ImageNum, b.REQ_PHY_NAME, c.STUDY_INS_UID, e.CHECKRESULT , CASE SUBSTRING(c.TRACEIDENTIFIER, 1, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_ZC, CASE SUBSTRING(c.TRACEIDENTIFIER, 2, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_XX, CASE SUBSTRING(c.TRACEIDENTIFIER, 3, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_HZ, e.REPORTID, b.OFDEPARTMENT, d.ROOMNO, d.MODALITYNAME, CASE e.masculine WHEN '0' THEN '阴性' WHEN '1' THEN '阳性' END AS MASCULINE, c.REQ_PROC_LOC, c.PRINT_STATUS, c.ISMARK, b.TOTALFEE, c.CRITICAL_STATUS, dbo.GetReportDate(c.REQ_PROCNO ,e.CONTENT_DATE,b.OFDEPARTMENT) CONTENT_DATE, dbo.GetAduitDate(c.REQ_PROCNO ,e.CONTENT_DATE,c.STATUS,c.REQ_PROCNO,e.CHIEFDOCTOR,b.OFDEPARTMENT) AUDITING_DATE, CASE WHEN e.PRINTDATE IS NULL THEN '' WHEN e.PRINTDATE > '1900-01-01 00:00:00' THEN CONVERT(varchar(19), e.PRINTDATE, 121) WHEN e.PRINTDATE = '' THEN '' END AS PRINTDATE, e.PREREPORTDOCTOR, e.PRECHIEFDOCTOR, d.FLATORPOWER, CASE RIGHT(PAT_AGE, 1) WHEN '岁' THEN CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) * 8760 WHEN '月' THEN CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) * 720 WHEN '天' THEN CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) * 24 WHEN '时' THEN CAST(REPLACE(REPLACE(PAT_AGE, '小', ''), RIGHT(REPLACE(PAT_AGE, '小', ''), 1), '') AS INT) ELSE CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) END AS PAT_AGE2, b.FLOORNO, b.LAYERNO,a.MERGER_FK, b.REGIONNO, b.DORMITORYNO, e.DiagnoseResult,'' DIAGNOSIS,e.TYPIST, b.REMARK REMARK,'' ADM_ID_SEQ_SERVICE,'' DIRECTIONCODE,case when e.B2='1' then '是' else '否' end B2, b.ACCESSION_NO, case when b.fetch_date is null then b.ACCESSION_DATE else b.fetch_date end fetch_date,b.ADM_ID_INDEX ,dbo.GetMainReqNO(c.REQ_PROCNO ) Main_REQ_PROCNO ,(select top 1 AUDITINGSTARTTIME from R_WORKTINGTIME w where w.REQ_PROCNO=c.REQ_PROCNO) AUDITINGSTARTTIME ,e.InternDOCTOR01,e.InternDOCTOR02,e.InternDOCTOR03, case when c.IsMultiple='1' then '是' else '否' end Multiple,c.IsMultiple, c.QueueName QueueText,b.PLA_ORD_NUM OPLA_ORD_NUM,a.HISID DHISID,case when c.ReadDiagnosticFilm = '1' then '是' else '否' end ReadDiagnosticFilmText,e.FollowUp_Flag ,case when e.FollowUp_Flag = '1' then '是' else '否' end FollowUp_FlagText,e.QuaImage,e.QuaReport,b.MOrder, p.SORTFLAG FROM dbo.R_PATIENT AS a INNER JOIN dbo.IMG_SERV_REG AS b ON a.PATIENTNO = b.PATIENTNO INNER JOIN dbo.REQ_PROC AS c ON b.SERV_REGNO = c.SERV_REGNO INNER JOIN dbo.REQ_PROC_DETAIL AS d ON c.REQ_PROCNO = d.REQ_PROCNO INNER JOIN dbo.GENERAL_REPORT AS e ON c.REQ_PROCNO = e.REQ_PROCNO join DICT_PUBLICINFO p on b.ADM_ID_ISS =p.CODENAME and p.PARENTCODE='999009' WHERE (c.DELETE_SIGN = '1')
视图二、.V_One_GlobalStudy
CREATE VIEW [dbo].[V_One_GlobalStudy] AS SELECT a.PATIENTNO, a.PATNAME, a.SEX, a.PATIENTID, b.SERV_REGNO, b.PLA_ORD_NUM, a.HISID, c.ACTION_DATE, b.ACCESSION_DATE, CASE WHEN b.EXIGENCE = '0' THEN 'false' ELSE 'true' END AS ISEXIGENCE, b.ENROLDOCTOR, b.ADM_ID_ISS, b.ADM_ID, b.BedNO, b.PAT_AGE, c.REQ_PROCNO, c.STATUS, c.REQ_PROC_ID, c.MODALITY, d.PARTOFCHECK_STAT AS PARTOFCHECK, c.ALLPARTOFCHECK, --d.DIRECTION, d.ALLDIRECTION, c.STUDY_DATE, d.ALLDIRECTION as DIRECTION, d.EXPOSALTIMES, c.STUDY_DATE AS CHECKDATE, a.BIRTHDATE, a.IDCARDNO, a.TELEPHONE, a.ADDRESS, d.CHECKDOCTOR, e.REPORTNO, e.REPORTDOCTOR, e.CHIEFDOCTOR, e.PRINTDOCTOR, d.FEE, b.REQ_SERVICE, case (select count(1) from R_LATERINFO r where r.REQ_PROC_ID=c.REQ_PROC_ID) when 0 then '否' else '是' end LATINFO, b.REQ_PHY_NAME, c.STUDY_INS_UID, e.CHECKRESULT, CASE SUBSTRING(c.TRACEIDENTIFIER, 1, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_ZC, CASE SUBSTRING(c.TRACEIDENTIFIER, 2, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_XX, CASE SUBSTRING(c.TRACEIDENTIFIER, 3, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_HZ, e.REPORTID, b.OFDEPARTMENT, d.ROOMNO, d.MODALITYNAME, CASE e.masculine WHEN '0' THEN '阴性' WHEN '1' THEN '阳性' END AS MASCULINE, c.REQ_PROC_LOC, c.PRINT_STATUS, c.ISMARK, b.TOTALFEE, c.CRITICAL_STATUS, dbo.GetReportDate(e.REQ_PROCNO ,e.CONTENT_DATE,b.OFDEPARTMENT) CONTENT_DATE, dbo.GetAduitDate(e.REQ_PROCNO ,e.CONTENT_DATE,c.STATUS,c.REQ_PROCNO,e.CHIEFDOCTOR,b.OFDEPARTMENT) AUDITING_DATE, CASE WHEN e.PRINTDATE IS NULL THEN '' WHEN e.PRINTDATE > '1900-01-01 00:00:00' THEN CONVERT(varchar(19), e.PRINTDATE, 121) WHEN e.PRINTDATE = '' THEN '' END AS PRINTDATE, e.PREREPORTDOCTOR, e.PRECHIEFDOCTOR, d.FLATORPOWER, CASE RIGHT(PAT_AGE, 1) WHEN '岁' THEN CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) * 8760 WHEN '月' THEN CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) * 720 WHEN '天' THEN CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) * 24 WHEN '时' THEN CAST(REPLACE(REPLACE(PAT_AGE, '小', ''), RIGHT(REPLACE(PAT_AGE, '小', ''), 1), '') AS INT) ELSE CAST(REPLACE(PAT_AGE, RIGHT(PAT_AGE, 1), '') AS INT) END AS PAT_AGE2, b.FLOORNO, b.LAYERNO,a.MERGER_FK, b.REGIONNO, b.DORMITORYNO, e.DiagnoseResult,b.REMARK REMARK,e.TYPIST, '' ADM_ID_SEQ_SERVICE,'' DIRECTIONCODE,case when e.B2='1' then '是' else '否' end B2, b.ACCESSION_NO, case when b.fetch_date is null then b.ACCESSION_DATE else b.fetch_date end fetch_date,b.ADM_ID_INDEX ,dbo.GetMainReqNO(c.REQ_PROCNO ) Main_REQ_PROCNO, (select top 1 AUDITINGSTARTTIME from R_WORKTINGTIME w where w.REQ_PROCNO=c.REQ_PROCNO) AUDITINGSTARTTIME ,e.InternDOCTOR01,e.InternDOCTOR02,e.InternDOCTOR03, case when c.IsMultiple='1' then '是' else '否' end Multiple,c.IsMultiple, dbo.GetQueueText(c.REQ_PROCNO) QueueText,b.PLA_ORD_NUM OPLA_ORD_NUM,a.HISID DHISID,case when c.ReadDiagnosticFilm = '1' then '是' else '否' end ReadDiagnosticFilmText,'' ImageNum,e.FollowUp_Flag ,case when e.FollowUp_Flag = '1' then '是' else '否' end FollowUp_FlagText,e.QuaImage,e.QuaReport,b.MOrder, p.SORTFLAG FROM dbo.R_PATIENT AS a INNER JOIN dbo.IMG_SERV_REG AS b ON a.PATIENTNO = b.PATIENTNO INNER JOIN dbo.REQ_PROC AS c ON b.SERV_REGNO = c.SERV_REGNO INNER JOIN dbo.GENERAL_REPORT AS e ON c.REQ_PROCNO = e.REQ_PROCNO CROSS APPLY(SELECT TOP(1) * from dbo.REQ_PROC_DETAIL AS d where c.REQ_PROCNO = d.REQ_PROCNO order by FEE desc) d join DICT_PUBLICINFO p on b.ADM_ID_ISS =p.CODENAME and p.PARENTCODE='999009' WHERE (c.DELETE_SIGN = '1')
视图三、V_GLOBALSTUDY_NKJK
CREATE VIEW [dbo].[V_GLOBALSTUDY_NKJK] AS SELECT c.STATUS, b.ADM_ID_ISS, a.HISID, b.PLA_ORD_NUM, b.DIAGNOSIS, c.ASSAY, c.ENDONO, a.PATNAME, a.SEX, a.PATIENTID, c.ALLPARTOFCHECK, d.ALLDIRECTION, c.MODALITY, CASE WHEN b.EXIGENCE = '0' THEN 'false' ELSE 'true' END AS ISEXIGENCE, b.ENROLDOCTOR, b.ADM_ID, b.BedNO AS BEDNO, b.PAT_AGE, e.CHIEFDOCTOR, e.PRINTDOCTOR, e.REPORTDOCTOR, d.FEE, c.REQ_PROC_ID, d.DIRECTION, e.CHECKRESULT, a.IDCARDNO, a.BIRTHDATE, a.TELEPHONE, a.ADDRESS, b.REQ_SERVICE, b.REQ_PHY_NAME, c.STUDY_INS_UID, d.PARTOFCHECK_STAT AS PARTOFCHECK, d.CHECKDOCTOR, b.OFDEPARTMENT, c.REQ_PROC_LOC, d.MODALITYNAME, d.ROOMNO, CASE SUBSTRING(c.TRACEIDENTIFIER, 1, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_ZC, CASE SUBSTRING(c.TRACEIDENTIFIER, 2, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_XX, CASE SUBSTRING(c.TRACEIDENTIFIER, 3, 1) WHEN 'T' THEN 'true' WHEN 'F' THEN 'false' END AS TRACEIDENTIFIER_HZ, a.PATIENTNO, b.SERV_REGNO, c.REQ_PROCNO, e.REPORTNO, e.REPORTID, CASE e.masculine WHEN '0' THEN '阴性' WHEN '1' THEN '阳性' END AS MASCULINE, c.CRITICAL_STATUS, e.D2, CONVERT(varchar(19), c.STUDY_DATE, 121) AS CHECKDATE, b.ACCESSION_DATE ACCESSION_DATE, CONVERT(varchar(19), c.ACTION_DATE, 121) AS ACTION_DATE, CONVERT(varchar(19), e.CONTENT_DATE, 121) AS CONTENT_DATE, CONVERT(varchar(19), e.PRINTDATE, 121) AS PRINTDATE,b.NJXH ,p.SORTFLAG FROM dbo.R_PATIENT AS a INNER JOIN dbo.IMG_SERV_REG AS b ON a.PATIENTNO = b.PATIENTNO INNER JOIN dbo.REQ_PROC AS c ON b.SERV_REGNO = c.SERV_REGNO INNER JOIN dbo.REQ_PROC_DETAIL AS d ON c.REQ_PROCNO = d.REQ_PROCNO INNER JOIN dbo.GENERAL_REPORT AS e ON c.REQ_PROCNO = e.REQ_PROCNO join DICT_PUBLICINFO p on b.ADM_ID_ISS =p.CODENAME and p.PARENTCODE='999009' WHERE (c.DELETE_SIGN = '1') AND OFDEPARTMENT = 'NKJK'