Database
Enthusiast

## How to calculate minimum & maximum values based on a column only if the records are continuous

Hi,

I have data as below

col1 col2 begindate enddate

A  100  '2016-01-01' '2016-02-18'

A   100 '2016-03-01' '2016-04-07'

A   101 '2016-03-31' '2016-04-20'

A   100 '2016-05-01' '2016-06-03'

A 100 '2016-06-18' '2016-07-07'

i need output like

A 100 '2016-01-01' '2016-04-07'

A 101 '2016-03-31' '2016-04-20'

A 100 '2016-05-01' '2016-07-07'

Basically i need minium begin date and maximum of end date based on col1 & col2 but only when col2 is continuous.

Any help would be highly helpful.

Thanks,

Hanu

11 REPLIES
Junior Supporter

## Re: How to calculate minimum & maximum values based on a column only if the records are continuous

Hi.

You can use a PERIOD datatype from your begindate & enddate and use a NORMALIZE clause.

Cheers.

Carlos.
Enthusiast

## Re: How to calculate minimum & maximum values based on a column only if the records are continuous

Hi Carlos,

I am not familiar with PERIOD & NORMALIZE clause, Could you please elaborate and give sample query.

Thanks,

Hanu

Junior Supporter

## Re: How to calculate minimum & maximum values based on a column only if the records are continuous

Hi.

And what stops you from looking it up in the documentation (SQL Data Manipulation Language, Chapter 1)?

Cheers.

Carlos

Enthusiast

## Re: How to calculate minimum & maximum values based on a column only if the records are continuous

Hi Carlos,

I think his requirement is different. PERIOD will not be able solve this problem. He needs to partition the first two rows (WHERE Col2 = 100) and take the minimum and maximum date from that lot. And then again he need to partition the last two rows (WHERE Col2 = 100) and take minimum and maximum dates from that lot.

For Col2 = 101, since there is only one row, the dates are being picked as minimum and maximum dates.

It would be easier to solve this issue if we not had two rows with Col2 = 100 after the 101 row.

Enthusiast

## Re: How to calculate minimum & maximum values based on a column only if the records are continuous

Don't understand how you identify 'only when col2 is continuous' : A physical file has order of records, but in Table does not have an implicit order of records.

If this needs to be done on a file, it can be done through ETL tools (Eg: Ab initio)

Enthusiast

## Re: How to calculate minimum & maximum values based on a column only if the records are continuous

I was wondering the same thing. How do we identify "continuity" of records in Teradata. It does not make sense.

May be Hanu can explain his requirements and how he came across such a requirement more precisely.

Enthusiast

Junior Supporter

## Re: How to calculate minimum & maximum values based on a column only if the records are continuous

Hi.

I overlooked the dates and didn't understand the logic you wanted.

` BTEQ -- Enter your SQL request or BTEQ command:SELECT COL1,       COL2,       BEGINDATE,       ENDDATE  FROM PRUEBA_PORDER BY 3; *** Query completed. 5 rows found. 4 columns returned. *** Total elapsed time was 1 second.COL1  COL2   BEGINDATE     ENDDATE----  ----  ----------  ----------A      100  2016-01-01  2016-02-18A      100  2016-03-01  2016-04-07A      101  2016-03-31  2016-04-20A      100  2016-05-01  2016-06-03A      100  2016-06-18  2016-07-07 BTEQ -- Enter your SQL request or BTEQ command:SELECT DISTINCT       COL1,       COL2,       MIN(BEGINDATE) OVER ( PARTITION BY COL1 ORDER BY BEGINDATE                             RESET WHEN COL2 <> MIN(COL2) OVER ( PARTITION BY COL1                                                                 ORDER BY BEGINDATE                                                                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING )                           ) _BEGINDATE,       MAX(ENDDATE) OVER ( PARTITION BY COL1 ORDER BY ENDDATE                           RESET WHEN COL2 <>MIN(COL2) OVER ( PARTITION BY COL1                                                              ORDER BY ENDDATE                                                              ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING )                         ) _ENDDATE  FROM PRUEBA_PORDER BY 3; *** Query completed. 3 rows found. 4 columns returned. *** Total elapsed time was 1 second.COL1  COL2  _BEGINDATE    _ENDDATE----  ----  ----------  ----------A      100  2016-01-01  2016-04-07A      101  2016-03-31  2016-04-20A      100  2016-05-01  2016-07-07`

HTH.

Cheers.

Carlos.