Date between two columns

Database
Enthusiast

Date between two columns

Hi Experts,

 

i need  get data between

i have a start_dt and exp_end_dt

 1st plan                                                  2nd plan

-------------------|---------------------------|----------------------------

exp_end_dt    |  <7 days>                |start_dt

    

need to get 1st Plan exp_end_dt  to <7days >  2nd plan start_dt  
withis tha period data only need to get.

 

exp_end_dt,start_dt  are columns.

 

can you suggets the where condition.

 

 

 


Accepted Solutions
Junior Contributor

Re: Date between two columns

Seems you simply want to compare the current row with the previous:

 

select *
from tab
qualify
   max(cnt_end_dt) -- previous end date
   over (partition by cnt_id -- for each customer
         order by cnt_str_dt
         rows between 1 preceding and 1 preceding) >= add_months(cnt_str_dt,-2) -- within two months before current start date

Can be simplified in 16.10+ using LAG:

 

 

qualify
   lag(cnt_end_dt) -- previous end date
   over (partition by cnt_id -- for each customer
         order by cnt_str_dt) >= add_months(cnt_str_dt,-2) -- within two months before current start date

 

 

 

1 ACCEPTED SOLUTION
9 REPLIES
Teradata Employee

Re: Date between two columns

Hi VratnamR,

 

Sorry it's not very clear for me.

Can you provide a sample table (restricted to usefull columns for the test) with some datas (about ten lines) inside and the result you want to obtain ?

Enthusiast

Re: Date between two columns

CUST_TBL
CSUST_ID|CNT_ID
001|1
001|2
002|11
002|12
003|21
003|22


CONTRACT_TBL
cnt_id|cnt_str_dt|cnt_end_dt
1|2017-08-01|?
2|2016-10-01|2017-09-30
11|2017-07-01|?
12|2016-10-01|2017-09-30
21|2017-10-5|?
22|2016-10-01|2017-09-30

i need data who ever have updated thare contract like cnt_end_dt =>2 months

 

 

 

Teradata Employee

Re: Date between two columns

Thanks for providing the test tables, but I still fail to understand this sentence :

 

i need data who ever have updated thare contract like cnt_end_dt =>2 months 


With the data provided, what would be the answer set to your question ?

 

Enthusiast

Re: Date between two columns

Hi 

Each customer have 2 records now i need to get old record end_dt and new records start_dt difference days should be with in 2 months 

,only those records only need to get 

 

Thnak You

Junior Contributor

Re: Date between two columns

Seems you simply want to compare the current row with the previous:

 

select *
from tab
qualify
   max(cnt_end_dt) -- previous end date
   over (partition by cnt_id -- for each customer
         order by cnt_str_dt
         rows between 1 preceding and 1 preceding) >= add_months(cnt_str_dt,-2) -- within two months before current start date

Can be simplified in 16.10+ using LAG:

 

 

qualify
   lag(cnt_end_dt) -- previous end date
   over (partition by cnt_id -- for each customer
         order by cnt_str_dt) >= add_months(cnt_str_dt,-2) -- within two months before current start date

 

 

 

Enthusiast

Re: Date between two columns

Hi All,

 

 

simplified the Dates between 3 columns in a singel table.

date columns A,B,C  (3 date columns)

 

i need to extract B to A (2 months past data)

and B to C (7 days future data)

ex :

A| <2 months> |B| <7days> |

 

Thanks 
Venkat

Junior Contributor

Re: Date between two columns

Again, your question is not clear.

Show some input data, expected result and applied logic.

Enthusiast

Re: Date between two columns

Capture.JPG

 

 

Teradata Employee

Re: Date between two columns

That one is not very hard :

select *
  from tab
 where B - A between 0 and 60
    or C - B between 0 and  7;