group the records based on date

Analytics
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
Junior Supporter

Re: group the records based on date

Please STOP asking the same thing over and over.

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.
Senior Apprentice

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 ?