help me to group the records

Database
Enthusiast

help me to group the records

help me to group the records as the resultset given below.

Table

ID TYPE STDT ENDT
1 A 1Aug 10Aug
1 A 11Aug 20Aug
2 B 21Aug 30Aug
2 B 31Aug 9Sep
1 A 10Sep 20Sep

Below is the expected resultset

ID TYPE STDT ENDT
1 A 1Aug 20Aug
2 B 21Aug 9Sep
1 A 10Sep 20Sep
Tags (1)
7 REPLIES
Teradata Employee

Re: help me to group the records

sel ID,TYPE,MIN(STDT),MAX(ENDT)

FROM TABLE GROUP BY 1,2

Regards,

Chinmay Athavale

Enthusiast

Re: help me to group the records

Hi chinmay, your query is wrong, check my expected result...
Junior Contributor

Re: help me to group the records

Enthusiast

Re: help me to group the records

Hi Dieter,

Thanks for the solution.

Sorry for my ignorance and If you dont mind could you please explain the solution provided by you or please point to the appropriate manual.

WITH cte(grpcol,pd) AS (

SELECT grpcol, PERIOD(from_dt,to_dt)

FROM date_range_test )

SELECT *

FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_OVERLAP_MEET(NEW VARIANT_TYPE(cte.grpcol),

cte.pd)

RETURNS (grpcol INT, pd PERIOD(DATE), cnt INT) HASH BY grpcol LOCAL

ORDER BY grpcol, pd ) AS dt

ORDER BY 1,2;

Thanks.

Junior Contributor

Re: help me to group the records

You'll find the function in the "Functions & Operators" manual, but it's not really "well documented".

Those functions only work for PERIODs and need a WITH to provide the source data, which is then passed to the table function.

The grouping column(s) are passed in the NEW VARIANT_TYPE and must be listed as first column(s) in RETURNS.

The PERIOD is the 2nd parameter for input and ouput and there's an optional count column returning the number of rows combined into one.

HASH BY must be the grouping column(s) and order by must be the grouping column(s) plus the period.

When you miss any part of that syntax you'll get a nice error message :-)

Dieter

Teradata Employee

Re: help me to group the records

Hi Gowtham,
I tried the following at it seems to be working, please check and let me know :

create volatile table brain_twister
(
id integer,
id_type char(1),
stdt date,
endt date
)
primary index (id)
on commit preserve rows;

insert into brain_twister values(1,'A',1130801,1130810);
insert into brain_twister values(1,'A',1130811,1130820);
insert into brain_twister values(2,'B',1130821,1130830);
insert into brain_twister values(2,'B',1130831,1130909);
insert into brain_twister values(1,'A',1130910,1130920);
insert into brain_twister values(1,'A',1130821,1130830);
insert into brain_twister values(1,'A',1130921,1130930);

sel distinct id, id_type, min_stdt,max_endt from
(sel
id,
id_type,
stdt,
endt,
max(endt) over(partition by id,id_type order by stdt,endt reset when stdt -1 > max(endt) over(partition by id,id_type order by stdt, endt rows between unbounded preceding and 1 preceding)) max_endt
,min(stdt) over (partition by id,id_type order by stdt,endt reset when stdt -1 > max(endt) over(partition by id,id_type order by stdt, endt rows between unbounded preceding and 1 preceding)) min_stdt
from brain_twister
) temp
Enthusiast

Re: help me to group the records

tusharraste, i checked your query it is working fine,  thanks for your efforts dieter and tusharraste.