具有多个条件的Teradata SQL CASE语句

Question

我有下面的SQL查询-

select distinct HospitalAcctID,
AdmitDate,
DischargeDate,
PatMRN,
Pat_id,
ICD,
MedCenter,
(case when SeqCount =1 and AdmitDate > '06/01/2013'   and AdmitDate < '06/01/2018' then 1 else null end ) Firstdiag
from
(
select distinct acct.HSP_ACCOUNT_ID as HospitalAcctID,
cast(acct.ADM_DATE_TIME as date format 'mm/dd/yyyy') as AdmitDate,
cast(acct.DISCH_DATE_TIME as date format 'mm/dd/yyyy') as DischargeDate,
pat.pat_mrn_id as PatMRN,
pat.pat_id as Pat_id,
REF_BILL_CODE as ICD,
grp7.NAME AS MedCenter,
row_number() over (partition by PatMRN order by AdmitDate) as SeqCount
from   acct 
inner join  pat on pat.pat_id = acct.pat_id
inner join  hspenc on hspenc.CSN_ID = acct.CSN_ID
inner join  dx  on acct.ACCOUNT_ID = dx.ACCOUNT_ID and line = 1
inner join  edg on dx.DX_ID = edg.DX_ID
inner join loc on loc.LOC_ID = acct.LOC_ID
inner join  grp7 ON loc.RPT_GRP_SEVEN = grp7.RPT_GRP_SEVEN
where
grp7.NAME =  'SMC AREA'
and ADMIT_CONF_STAT_C in ('1','4')
and (edg. REF_BILL_CODE in ('431',
'431')                                      
)                   
and ADT_PAT_CLASS_C in ('1204','12113')
order by  AdmitDate;
)Admit

但我得到了以下语法错误-语法错误，应为"EXCEPT"关键字、"UNION"关键字或介于"AdmitDate"one_answers"，"之间的"MINUS"关键字

在外部select语句中，我试图获得首次诊断的最小(第一)日期。我还想只得到2013年6月至2018年6月期间确诊的患者，这就是为什么我有CASE声明的原因。但是CASE语句给了我错误。

Answer 1

正如@BarbarosÖzhan所写，删除派生表中的最后一行order by AdmitDate;。

但不需要ROW_NUMBER:

select distinct acct.HSP_ACCOUNT_ID as HospitalAcctID,
cast(acct.ADM_DATE_TIME as date format 'mm/dd/yyyy') as AdmitDate,
cast(acct.DISCH_DATE_TIME as date format 'mm/dd/yyyy') as DischargeDate,
pat.pat_mrn_id as PatMRN,
pat.pat_id as Pat_id,
REF_BILL_CODE as ICD,
grp7.NAME AS MedCenter,
case when -- current rows is first row
min(AdmitDate)
over (partition by PatMRN) = AdminDate
-- current row within date range
and AdminDate >= DATE '2013-06-01' and AdmitDate < DATE '2018-06-01' 
then 1
else null
end as Firstdiag
from   acct 
inner join  pat on pat.pat_id = acct.pat_id
inner join  hspenc on hspenc.CSN_ID = acct.CSN_ID
inner join  dx  on acct.ACCOUNT_ID = dx.ACCOUNT_ID and line = 1
inner join  edg on dx.DX_ID = edg.DX_ID
inner join loc on loc.LOC_ID = acct.LOC_ID
inner join  grp7 ON loc.RPT_GRP_SEVEN = grp7.RPT_GRP_SEVEN
where
grp7.NAME =  'SMC AREA'
and ADMIT_CONF_STAT_C in ('1','4')
and (edg. REF_BILL_CODE in ('431',
'431')                                      
)                   
and ADT_PAT_CLASS_C in ('1204','12113')
order by  AdmitDate;

我还切换到标准SQL日期文字DATE '2013-06-01'，而不是'06/01/2013'。前者只有一种可能的格式(DATE 'YYYY-MM-DD')，而后者取决于基列的format，当它更改时可能会失败(当然不是在查询中，因为您在CAST中定义了它)。