甘肃出院病人调查表-卫统4表.sql
SELECT
convert(varchar(12),' ') as USERID,
convert(varchar(60),'甘肃省人民医院') AS USERNAME,
convert(varchar(30),dbo.BA_BRDA.BRXZ) AS YLFKFS,
CONVERT(VARCHAR(50),dbo.BA_BRDA.RYCS) AS ZYCS,
CONVERT(VARCHAR(24),dbo.BA_BRDA.ZYH) AS BAH,
convert(varchar(36),dbo.BA_BRDA.BRXB ) AS XB,
convert(integer,year(getdate()) - year(dbo.BA_BRDA.CSNY)) as LL,
convert(varchar(30),CASE WHEN dbo.BA_BRDA.HYZK = 1 THEN 1
WHEN dbo.BA_BRDA.HYZK = 2 THEN 2
WHEN dbo.BA_BRDA.HYZK = 3 THEN 3
WHEN dbo.BA_BRDA.HYZK = 4 THEN 4
WHEN dbo.BA_BRDA.HYZK = 5 THEN 1
WHEN dbo.BA_BRDA.HYZK = 6 THEN 2
END) AS HYZK,
CONVERT(VARCHAR(30),dbo.BA_BRDA.ZYDM) AS ZYDM,
convert(char(12),dbo.BA_BRSY.RYRQ,112) AS RYRQ,
convert(varchar(24),case when A.KSMC="小儿科" THEN "07"
when A.KSMC="眼科" THEN "10"
when A.KSMC="耳鼻喉头颈外" THEN "11"
when A.KSMC="妇科" OR A.KSMC="产科" THEN "05"
when A.KSMC="急诊科" THEN "20"
when A.KSMC="甲流重症病房" THEN "16"
when A.KSMC="口腔颌面外科" THEN "12"
when A.KSMC="外监室" THEN "62"
when A.KSMC="普外科" OR A.KSMC="骨科" OR A.KSMC="胸外科" OR A.KSMC="神经外科" OR A.KSMC="烧伤整形科" OR A.KSMC="泌尿科" OR A.KSMC="肛肠科" OR A.KSMC="介入科" OR A.KSMC="乳腺科" OR A.KSMC="心血管外科" THEN "04"
ELSE "03"
END) AS RYKBDM,
convert(char(12),dbo.BA_BRSY.CYRQ,112) AS CYRQ,
convert(varchar(24),case when B.KSMC="小儿科" THEN "07"
when B.KSMC="眼科" THEN "10"
when B.KSMC="耳鼻喉头颈外" THEN "11"
when B.KSMC="妇科" OR B.KSMC="产科" THEN "05"
when B.KSMC="急诊科" THEN "20"
when B.KSMC="甲流重症病房" THEN "16"
when B.KSMC="口腔颌面外科" THEN "12"
when B.KSMC="外监室" THEN "62"
when B.KSMC="普外科" OR B.KSMC="骨科" OR B.KSMC="胸外科" OR B.KSMC="神经外科" OR B.KSMC="烧伤整形科" OR B.KSMC="泌尿科" OR B.KSMC="肛肠科" OR B.KSMC="介入科" OR B.KSMC="乳腺科" OR B.KSMC="心血管外科" THEN "04"
ELSE "03"
END ) AS CYKBDM,
convert(varchar(36),CASE WHEN dbo.BA_BRSY.RYQK = 1 THEN 1
WHEN dbo.BA_BRSY.RYQK = 2 THEN 2
WHEN dbo.BA_BRSY.RYQK = 3 THEN 3
END) AS RYSQK,
convert(varchar(30),C.ICD9) AS RYZD,
convert(char(12),dbo.BA_BRSY.QZRQ,112) AS RYHQZRQ,
convert(varchar(30),D.ICD9) AS CYSZYZD,
convert(varchar(36),CASE WHEN dbo.BA_BRSY.ZGQK = 1 THEN 1
WHEN dbo.BA_BRSY.ZGQK = 2 THEN 2
WHEN dbo.BA_BRSY.ZGQK = 3 THEN 3
WHEN dbo.BA_BRSY.ZGQK = 4 THEN 4
WHEN dbo.BA_BRSY.ZGQK = 5 THEN 5
WHEN dbo.BA_BRSY.ZGQK = 6 THEN 5
WHEN dbo.BA_BRSY.ZGQK = 7 THEN 5
end) AS CYSZYZDZLJG,
CONVERT(VARCHAR(30),G.QTZD) AS CYSQTZD,
CONVERT(VARCHAR(36),CASE WHEN G.ZGQK = 1 THEN 1
WHEN G.ZGQK = 2 THEN 2
WHEN G.ZGQK = 3 THEN 3
WHEN G.ZGQK = 4 THEN 4
WHEN G.ZGQK = 5 THEN 5
WHEN G.ZGQK = 6 THEN 5
WHEN G.ZGQK = 7 THEN 5
end) AS CYSQTZDZLJG,
convert(varchar(30),dbo.BA_BRSY.GRBM) AS YYGRMC,
convert(varchar(36),CASE WHEN dbo.BA_BRSY.GRJG = 1 THEN 1
WHEN dbo.BA_BRSY.GRJG = 2 THEN 2
WHEN dbo.BA_BRSY.GRJG = 3 THEN 3
WHEN dbo.BA_BRSY.GRJG = 4 THEN 4
WHEN dbo.BA_BRSY.GRJG = 5 THEN 5
WHEN dbo.BA_BRSY.GRJG = 6 THEN 5
WHEN dbo.BA_BRSY.GRJG = 7 THEN 5
end) AS YYGRMCZLJG,
//-----------------------------------------------------------------------
convert(varchar(30),F.SSBM) AS SSHZDWBYY,
convert(varchar(30),E.SSDM) AS SSBM,
//------------------------------------------------------------------------
convert(NUMERIC(10),dbo.BA_BRFY.ZFY) AS ZYFYZJ,
convert(numeric(7),dbo.BA_BRFY.CWF) AS CW,
convert(numeric(7),dbo.BA_BRFY.HLF) AS ZC,
convert(numeric(7),dbo.BA_BRFY.XYF) AS XY,
convert(numeric(7),(dbo.BA_BRFY.CYF + dbo.BA_https://www.wendangku.net/doc/5a11001059.html,F)) AS ZY,
convert(numeric(7),dbo.BA_BRFY.JYF) AS HY,
convert(numeric(7),dbo.BA_BRFY.ZLF) AS HL,
convert(numeric(7), dbo.BA_BRFY.SSF) AS SX,
convert(numeric(7),dbo.BA_BRFY.JCF) AS MZ,
convert(numeric(7),dbo.BA_BRFY.QTF) AS QTFY,
CONVERT(VARCHAR(24), CASE WHEN dbo.BA_
BRSY.XXDM =1 THEN 1
WHEN dbo.BA_BRSY.XXDM =2 THEN 2
WHEN dbo.BA_BRSY.XXDM =4 THEN 3
WHEN dbo.BA_BRSY.XXDM =6 THEN 4
ELSE 5
END ) AS XX,
CONVERT(VARCHAR(24),dbo.BA_BRZD.HXBS) AS HXB,
CONVERT(VARCHAR(24),dbo.BA_BRZD.XXBS) AS XXB,
CONVERT(VARCHAR(24),dbo.BA_BRZD.SXJS) AS XJ,
CONVERT(VARCHAR(24),dbo.BA_BRZD.SQXS) AS QX,
CONVERT(VARCHAR(24),dbo.BA_BRZD.QTXX) AS QT
FROM dbo.BA_BRDA,
dbo.BA_BRFY,
dbo.BA_BRSY,
dbo.BA_BRZD,
dbo.GY_KSDM AS A,
dbo.GY_KSDM AS B,
dbo.GY_JBBM AS C,
dbo.GY_JBBM AS D,
( SELECT DISTINCT BA_BRSS.ZYH AS ZYH, MAX(GY_SSDM.SSDM) AS SSDM FROM BA_BRSS,GY_SSDM WHERE BA_BRSS.SSDM = GY_SSDM.SSNM GROUP BY ZYH ) AS E,
( SELECT DISTINCT BA_BRSY.ZYH AS ZYH, MAX(BA_BRSY.SSBM) AS SSBM FROM BA_BRSY WHERE ( dbo.BA_BRSY.CYRQ >='2010.1.1' ) and ( dbo.BA_BRSY.CYRQ < '2010.4.1') GROUP BY ZYH) AS F,
( SELECT AA.ZYH AS ZYH,AA.QTZD AS QTZD,MIN(BB.ZGQK) AS ZGQK FROM (SELECT DISTINCT BA_BRQT.ZYH AS ZYH, MAX(GY_JBBM.ICD9) AS QTZD,MAX(BA_BRQT.JBDM) AS JBDM FROM BA_BRQT,GY_JBBM WHERE ( BA_BRQT.JBDM = GY_JBBM.jbxh ) GROUP BY ZYH ) AS AA, BA_BRQT BB WHERE BB.ZYH *= AA.ZYH GROUP BY AA.ZYH,AA.QTZD) AS G
WHERE
( dbo.BA_BRSY.RYKS = A.KSDM ) AND
( C.jbxh = dbo.BA_BRSY.RYZD ) AND
( D.jbxh = dbo.BA_BRSY.ZYZD ) AND
( dbo.BA_BRSY.CYKS = B.KSDM ) AND
( dbo.BA_BRSY.ZYH = dbo.BA_BRFY.ZYH ) and
( dbo.BA_BRSY.ZYH = dbo.BA_BRDA.ZYH ) and
( dbo.BA_BRZD.ZYH = dbo.BA_BRSY.ZYH ) and
dbo.BA_BRSY.ZYH *= E.ZYH AND
dbo.BA_BRSY.ZYH *= F.ZYH AND
dbo.BA_BRSY.ZYH *= G.ZYH AND
( dbo.BA_BRSY.CYRQ >='2011.4.1' ) and
( dbo.BA_BRSY.CYRQ < '2011.7.1' );