内江市第一人民医院信息科工作记录

这个页面用来记录平时遇到的问题和解决方法，在后面再次遇到的时候可以作为参考。

常用SQL

通过住院号查就诊号

SELECT * FROM DHCWMR_SS.ReceiptLog WHERE MrNo = "{住院号}"

查询收入数据

// 分门诊和住院查询指定时间段的不包含耗材的收入数据，并分组统计

SELECT WorkLoad_TarItem_DR->TARI_Code 收费项编码,

       WorkLoad_TarItem_DR->TARI_Desc 收费项名称,

       WorkLoad_TarItem_DR->TARI_ChargeBasis 收费依据,

        CASE WorkLoad_Type WHEN "I" THEN "住院"

              ELSE "门诊" END

              收费类型,

       SUM(WorkLoad_Quantity) 数量,

        WorkLoad_UnitPrice 单价,

        SUM(WorkLoad_TotalPrice) 费用,

        COUNT(*) 次数,

       *

FROM DHC_WorkLoad WHERE WorkLoad_FlagDate>="{strat_date}"

AND WorkLoad_FlagDate<="{end_date}"

AND WorkLoad_TarItem_DR->TARI_ChargeBasis IS NOT NULL

AND WorkLoad_TarItem_DR->TARI_Code IN (SELECT TARI_Code FROM DHC_TarItem WHERE TARI_SubCate NOT IN ("16","17","18","19") AND TARI_ActiveFlag ="Y" )

GROUP BY WorkLoad_TarItem_DR->TARI_ChargeBasis,WorkLoad_Type,WorkLoad_UnitPrice;

查询医疗救助病人

// 查询费用中有医保救助病人的多个医保相关字段

SELECT INSU_AdmInfo.INADM_UserDr, INSU_AdmInfo.INADM_XString5, INSU_AdmInfo.INADM_XString6, INSU_AdmInfo.INADM_InsuId, INSU_AdmInfo.INADM_Center, INSU_AdmInfo.INADM_XString8,

INSU_AdmInfo.INADM_AdmDate, INSU_AdmInfo.INADM_AdmTime, INSU_AdmInfo.INADM_OutDate, INSU_AdmInfo.INADM_OutTime, INSU_AdmInfo.INADM_XString4, INSU_Divide.INPAY_iDate, INSU_Divide.INPAY_bcbxf0, INSU_Divide.INPAY_grzfe0, INSU_Divide.INPAY_InsuPay1, INSU_Divide.INPAY_zhzfe0, INSU_Divide.INPAY_InsuPay2, INSU_Divide.INPAY_InsuPay3, INSU_Divide.INPAY_InsuPay4, INSU_Divide.INPAY_InsuPay5, INSU_Divide.INPAY_InsuPay6, INSU_Divide.INPAY_InsuPay7 FROM INSU_AdmInfo, INSU_Divide WHERE INSU_Divide.INPAY_AdmInfoDr = INSU_AdmInfo.INADM_Rowid AND INSU_Divide.INPAY_sfrq00

BETWEEN "2022-08-01" AND "2022-08-31" AND INPAY_InsuPay4 > 0 AND INPAY_Flag = "I";