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

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

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

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.

Adhering to your example, you can get the results you want:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT COL1,
COL2,
BEGINDATE,
ENDDATE
FROM PRUEBA_P
ORDER 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-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

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_P
ORDER 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-07
A 101 2016-03-31 2016-04-20
A 100 2016-05-01 2016-07-07

HTH.

Cheers.

Carlos.

Teradata Employee

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

With period data type using EXPAND ON clause it might provide the necessary output.

Agnit.