Teradata Between Clause with date 'day's' interval

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

Teradata Between Clause with date 'day's' interval

I need to find all patients that have been discharged from the ER and went to their Dr between 1 and 14 days of the discharge date.

 

This is what I have and Teradata does not like it  (DISCH_DT_TM is the ER Discharge date and BEG_EFFECTIVE_DT_TM AS DATE is the date the patient went and saw the DR for follow-up of ER visit). 

 

WHERE

s.STATE_MEANING = 'CHECKED OUT' (NOT CASESPECIFIC)  -- went and completed visit

AND e.DISCH_DT_TM <> '3620-01-10 11:01:00.000000'  -- wacky date entered

AND s.BEG_EFFECTIVE_DT_TM IS NOT NULL -- have nulls in field

AND e.DISCH_DT_TM IS NOT NULL   -- have nulls in field

AND CAST(s.BEG_EFFECTIVE_DT_TM AS DATE) BETWEEN CAST(e.DISCH_DT_TM - INTERVAL '1' DAY AS DATE )

AND CAST(e.DISCH_DT_TM - INTERVAL '14' DAY AS DATE )

AND s.APP_SOURCE_ID = 200

 

Could someone help me and explain what I am doing wrong?

 

Thanks-

Hbell


Accepted Solutions
Enthusiast

Re: Teradata Between Clause with date 'day's' interval

SyntaxEditor Code Snippet



SyntaxEditor Code Snippet
 CAST(e.DISCH_DT_TM  date ) > CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)AND  CAST(e.DISCH_DT_TM  date )> CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)+15

SyntaxEditor Code Snippet

 CAST(e.DISCH_DT_TM  date ) > CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)AND  CAST(e.DISCH_DT_TM  date ) <  CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)+15

 

1 ACCEPTED SOLUTION
4 REPLIES
Enthusiast

Re: Teradata Between Clause with date 'day's' interval

SyntaxEditor Code Snippet



SyntaxEditor Code Snippet
 CAST(e.DISCH_DT_TM  date ) > CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)AND  CAST(e.DISCH_DT_TM  date )> CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)+15

SyntaxEditor Code Snippet

 CAST(e.DISCH_DT_TM  date ) > CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)AND  CAST(e.DISCH_DT_TM  date ) <  CAST(s.BEG_EFFECTIVE_DT_TM AS DATE)+15

 

Fan

Re: Teradata Between Clause with date 'day's' interval

Thank you so much.  My department is learning Teradata on a whim.  And we are all new to it.

 

Hbell

Junior Contributor

Re: Teradata Between Clause with date 'day's' interval

What do you mean by Teradata does not like it, no row returned?

 

You have to switch the calculations in BETWEEN, the 1st parameter must be smaller than the 2nd, otherwise you'll see unsatisfiable condition in Explain

Fan

Re: Teradata Between Clause with date 'day's' interval

Correct!  I did exactly that - that was a brain cramp at the time!  LOL  Thanks for getting back with me.