Analytics
Highlighted
Enthusiast

## group the records based on date

Hi all, could you please help me to group the records based on date. sample data and the excepted result 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

expected result

ID  TYPE  STDT   ENDT

1    A      1Aug    20Aug

2    B      21Aug  9Sep

1    A      10Sep  20Sep

Tags (1)
7 REPLIES
Supporter

## Re: group the records based on date

You'd better RTFM ('recursive' could be a good point to start searching, based on your reiterative question) and stop asking others to do your own work.

Cheers.

Carlos.

Enthusiast

## Re: group the records based on date

I have already done this, i need to optimize the query, so that only i posted in forum.
Junior Contributor

## Re: group the records based on date

What have you done already? Wrote a query and now you need to optimize it?

So why don't you simply show what you already got?

My crystal ball is still broken.

Dieter

Enthusiast

## Re: group the records based on date

Sel ID, TYPE, B, min(STDT), max(ENDT) from

(sel ID, TYPE, STDT, ENDT,

sum(A) over(orderby ID,TYPE,STDT unbounded preceding rows) B from

(sel ID, TYPE, STDT, ENDT,

case when row_number() (partitionby ID orderby ID,STDT)=1 then 1 else 0 end A from TN)DT

)DT

groupby 1,2

Enthusiast

## Re: group the records based on date

Why you want to make such complicated ? What i understood from your desired output, you can get it done easily just by -

Select ID, TYPE, MIN(STDT), MAX(ENDT) From Table group by 1,2.

Enthusiast

## Re: group the records based on date

Hi KS42982, Please check the expected result and then tell me it is simple or not. thanks for your efforts to finding the solution.

Gowtham

Enthusiast

## Re: group the records based on date

Are you saying the solution that I provided wouldn't work for you ?