Teradata SQL CASE Statement with multiple conditions

Question

I have the below SQL query -

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

But I am getting the below syntax error - Syntax error, expected something like an 'EXCEPT' keyword, 'UNION' Keyword or a 'MINUS' keyword between 'AdmitDate' and ','

In the outer select statement, I am trying to get the min (first ) date when the was first diagnosed. I also want to get only the patients who were diagnosed between 6/2013 to 6/2018 which is why I have the CASE statement. But the CASE statement is giving me error.

just remove the semicolon after order by AdmitDate. I don't know about Teradata's sql but you might even need to remove completely that line with order by... — Barbaros Özhan
– Barbaros Özhan, Commented Dec 22, 2018 at 10:12

dnoeth · Accepted Answer · 2018-12-22 11:12:59Z

As @BarbarosÖzhan already wrote, remove the last line order by AdmitDate; in the Derived Table.

But there's no need for 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;

I also switched to a Standard SQL date literal DATE '2013-06-01' instead of '06/01/2013'. There's only one possible format for the former (DATE 'YYYY-MM-DD') while the latter depends on the FORMAT of the base column and might fail when it changes (of course not in your query, because you defined it in the CAST).

Collectives™ on Stack Overflow

Teradata SQL CASE Statement with multiple conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related