All Forums Analytics
Gowtham 16 posts Joined 12/12
12 Aug 2013
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:
CarlosAL 512 posts Joined 04/08
12 Aug 2013

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.

Gowtham 16 posts Joined 12/12
13 Aug 2013

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

dnoeth 4628 posts Joined 11/04
13 Aug 2013

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

Dieter

Gowtham 16 posts Joined 12/12
14 Aug 2013

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

KS42982 137 posts Joined 12/12
15 Aug 2013

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.

Gowtham 16 posts Joined 12/12
18 Aug 2013

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

KS42982 137 posts Joined 12/12
21 Aug 2013

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

You must sign in to leave a comment.