如图所示,标出来的框就是报错的字段
字段前加' ', N' ' , CONVERT, 这几种办法试过了,报同样的错
我把代码贴上来请大家帮忙看看,谢谢了
-USE [TATViewDB]
--GO
--CREATE view [dbo].[amRAD_RADIOLOGY_Full]
--AS
------------------申请时间-----------------------------
WITH ApplyDate AS (
----OutPatient
select distinct a.PlacerOrderNo 申请单号,'outpatient' 类型,PatientID 门诊号,PatientName 姓名,
' ' visitnumber,
case PatientSex when '1' then '男' else '女' end 性别,
PatientAge 年龄,
RequestDateTime 申请时间,
--DiagnosticServiceName 分类名称,
BodyPart 部位,
b.BillingItemDescription 医嘱名称,HospitalServiceDescription 所属科室
from OPDOrder a, OPDOrderItems b
where a.PlacerOrderNo=b.PlacerOrderNo
and ChargeStatus=1 and OrderStatus not in ('2','3')
UNION
ALL
----------INPATIENT---------------------
select distinct a.PlacerOrderNo 申请单号,'inpatient' 类型,a.PatientID 门诊号,a.PatientName 姓名,visitnumber,
case PatientSex when '1' then '男' else '女' end 性别,PatientAge 年龄,RequestDateTime 申请时间,
--DiagnosticServiceName 分类名称,
BodyPart 部位,
BillingItemDescription 医嘱名称,HospitalServiceDescription 所属科室
from IPDDocOrder a, IPDDocOrderItems b
where a.PlacerOrderNo=b.PlacerOrderNo
and OrderStatus>'5' )
,
---------------------------预约时间------------------------------------
AppointmentDATE AS
(
SELECT TOP (100) PERCENT RFPlacerOrderNo 申请单号,RFappointmentRequire 预约要求,min(RFCreateTime) 预约时间 FROM [172.16.10.207
].rop.dbo.ReservationForm
where RFIsCancel=0
group by RFPlacerOrderNo,RFappointmentRequire
order by RFPlacerOrderNo,RFappointmentRequire
)
,
--------------------------登记时间-----------------------------------
--SELECT a.PlacerOrderNo,b.RequestDateTime FROM RADServiceRequest a LEFT JOIN
--ApplyDate b ON a.PlacerOrderNo=b.PlacerOrderNo
---------------------------检查时间-----------------------------------------
--除了B超检查时间
CheckDate AS
(SELECT a.sheetID,CONVERT(DATE,substring(ExamDateTime,1,8))检查日期
,substring(ExamDateTime,1,4)+'-'+substring(ExamDateTime,5,2)+'-'
+substring(ExamDateTime,7,2)+' '+substring(ExamDateTime,9,2)
+':'+substring(ExamDateTime,11,2)+':'+substring(ExamDateTime,13,2) 检查时间
,b.HISRequestID 申请单号
FROM [RequestSubSub] a LEFT JOIN [Request] b
ON a.sheetID=b.sheetID)
,
------------------------------审核日期-----------------------------------
REVIEWDATE AS
(SELECT a.PlacerOrderNo 申请单号,b.FinalResultDateTime 审核日期
FROM RADServiceRequest a LEFT JOIN
RADReport b ON a.FillerOrderNo=b.FillerOrderNo
)
-------------------------result------------------------------
SELECT a.*,
b.预约时间, DATEDIFF(MINUTE,a.申请时间,b.预约时间) 申请到预约间隔时间,
c.RequestDateTime 登记时间,datediff(MINUTE,a.申请时间,c.RequestDateTime) 申请到登记间隔时间,
d.检查日期, datediff(MINUTE,b.预约时间,d.检查时间) 预约到检查间隔时间,
datediff(MINUTE,c.RequestDateTime,d.检查时间) 登记到检查间隔时间,
e.审核日期,datediff(MINUTE,d.检查时间,e.审核日期) 检查到出报告时间,b.预约要求
FROM ApplyDate a LEFT JOIN AppointmentDATE b ON a.申请单号 =b.申请单号
LEFT JOIN [RADServiceRequest] c ON a.申请时间 = c.PlacerOrderNo
LEFT JOIN CheckDate d ON a.申请单号 = d.申请单号
LEFT JOIN REVIEWDATE e ON a.申请单号 = e.申请单号
WHERE a.申请时间>'2018-07-01'